Google-sheets – retrieve the names of sheets in a public document

google sheetsimportrange

I am currently using the function IMPORTRANGE in a Google spreadsheet to pull information from a public document using the URL.

I have to supply a range_string in the format "Sheet Name!A1:D21" to select cells A1:D21 of sheet "Sheet Name" in the shared document.

Is there any way for me to get the list of sheets that are currently in the other document? Sheets are added by others, and currently I have to manually enter that information into the document where IMPORTRANGE is being used.

Alternatively, is it possible to have a range_string that uses an index of some sort, such as "[0]!A1:D21" to get cells A1:D21 in the first sheet of the other document?

Best Answer

Google Sheets doesn't have built-in functions that return a list of the sheet names and it's not possible to use something like [0]!A1:D21 in formulas to refer to a range in the first sheet of the spreadsheet.

The alternative to automatically get a list of the sheet names is to use Google Apps Script. You could create a custom function to get the list of the sheet names or even you could use Apps Script to import the values of the external spreadsheet.

References

https://developers.google.com/apps-script/guides/sheets

Related Solutions

Google Sheets: Lookup and Copy Data from Different Sheets

Your plans seem sensible to me and I think reasonably straightforward to implement. A formula such as:

=importrange("1NsEAt1biV822J4RnqOmb4MW6GCjrkUzfZjbssnZmkSQ)","Sheet1!A1:F8")

in each of the employee sheets in the Director's cut should import Sheet1!A1:F8 from each individual employee's document (once access has been granted). The long string of characters in the middle of the above formula is the 'key' and is unique to each document.

When all the relevant details are in the Director's copy it should then also be quite easy to process them further for analysis purposes.

Google-sheets – How to reference a range of data in another workbook (not importing)

Spreadsheet functions cannot access the content of other spreadsheets, other than by invoking importrange.

But you can have importrange inside other functions. For example,

=join(",", importrange("...", "Sheet1!A1:A5"))

returns the content of cells A1:A5 in the other spreadsheet, comma-separated within one cell. Another command often nested is query, which allows you to do a lot of processing of another spreadsheet's data without getting intermediate results in. For example:

=query(importrange("...",  "Sheet1!A1:B9"), "select Col1, sum(Col2) group by Col1 order by Col1")

returns the totals of the second column split by the values of the first column, and sorts by the first column.

Best Answer

Related Solutions

Google Sheets: Lookup and Copy Data from Different Sheets

Google-sheets – How to reference a range of data in another workbook (not importing)

Related Topic