How you download a particular sheet as .xlsx from google spreadsheet?

There are many numbers pf sheet in the spreadsheet.
How do i download a particular sheet from google spreadsheet?

See here:

it uses gid but i want do download by sheet name

You would need a google apps script for that.

However, if you know the google sheets individual sheet names and gids in advance, you could select the sheet name from a list, and set the download url with the corresponding gid.

i only know the sheet name in advance not the gid

How do you know the sheet name ?

It is not that difficult to know both...

i use app script to add custom name sheet

Then use apps script to get the gid.

function getGids() {
var ss = SpreadsheetApp.getActive();
var shts = ss.getSheets();
var gids = [];
for (var i=0;i<shts.length;i++) {
gids.push([shts[i].getSheetName() + "," + shts[i].getSheetId()]);
}
console.log(gids);
}

gids = [ [ 'Sheet1,0' ], [ 'Sheet2,194411959' ] ]

should i get web with url https://script.google.com/macros/s/YOUR_SCRIPT_ID/exec?action=getGids
?

You would need for the code I posted to be a part of your existing web app script. without seeing it i cannot advise on what you need to put in there.

i will PM the code. pls have a look

In your existing code just add the above code in else if with an action and set the return value as text output.

New web app created:

function doGet(e) {
var ss = SpreadsheetApp.openById(e.parameter.ID);
var shts = ss.getSheets();
var gids = [];
for (var i=0;i<shts.length;i++) {
gids.push([shts[i].getSheetName() + "," + shts[i].getSheetId()]);
}
return ContentService.createTextOutput(JSON.stringify(gids));
}

url (example)

https://script.google.com/macros/s/AKfycbwE2G88u1XEho6ZfEuo_edited_V6C43xdm9gyKG2-3-5A552Aj7WMjD/exec?ID=1ny-UplmI_edited_D70AVudgV4A

returns (for example):

[["Sheet1,0"],["Sheet2,194411959"]]
1 Like

morever have you tried this url? i hope this url too will download the selected sheet as xlx format into your app

https://docs.google.com/spreadsheets/d/<spreadsheetId>/export?format=xlsx&gid=<sheetId>

there is a catch.
my previous app script is not working.
only this new app script working.
it only provides json format.
so do i need to create new deployment?

OP wants to use sheet name, so we have to get them and the gids first before constructing the url...

You can only have one bound web app script per spreadsheet.

Create the new script as a standalone web app, and revert your bound web app version to your previous script.

?

[["Sheet1,0"],["Sheet2,194411959"]]

This is a stringified json array. Use the Web component's JSONTextDecode block to convert this to an AI2 list.

what should i write here?

your google sheet ID

else instead of this line in the above code

var ss = SpreadsheetApp.openById(e.parameter.ID);

try, var ss = SpreadsheetApp.getActiveSpreadsheet();

so you no need to pass ID in the url and pass only the url you will get pair of sheet name and its gid number

1 Like

i combine both scripts now app working good. Thanks to all :smiling_face_with_three_hearts: