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:
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