I am trying to build a Summary Sheet, which imports data from Project Sheets as they are created and updated. The Summary Sheet is stored in a team drive, and the Project Sheets are stored in a separate folder in that team drive. Each new project will have its own sheet, which is created when the project is accepted according to a template.

The folder for Project Sheets will end up with many sheets, for example:


The Summary Sheet then needs to IMPORTRANGE data from these sheets as they are created and updated.

Project 01112016, Date1, Date2, Sum1, Average1
Project 02112016, Date1, Date2, empty, empty
Project 15112016, Date1, empty, empty, empty
Project 12122016, empty, empty, empty, empty

I had tried building this in Excel, but the indirect function doesn't pull data from closed workbooks. In Google Sheets I have a different problem, the addresses of future sheets are unknown, because each new sheet address is unpredictable (as far as I know). There may be a thousand project sheets created each year, so automating the Summary Sheet is important.

Any ideas how to predict new sheet addresses, or to identify sheets in the folder of a team drive, in order to link Project Sheets and Summary Sheet?


1 Answer 1


Use the getFiles() from the Class Folder of Google Apps Script Drive Service.

Related questions on SO (include scripts)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.