I used this post to have all Google Sheets IDs in a folder Getting all files' file-id from a folder in Google Drive

I would like to make a formula or script to sum values in table across all these sheets IDs (note, the table is present in all sheets with same cell range).

Instead of copying and pasting the sheets Ids manually every time, I would like to make formula with variables that represents sheets ID so that every time I just past the sheets ID and the variables are assigned for each sheets.

Some thing in my mind like this:


b= 1fUb8H44PpeZpu40mfzK1_vuNEoLAyhiHjNcSOGTfLJU


And so on... while the formula is fixed

  • Please show what you tried. Commented Jan 9, 2021 at 22:15
  • 1
    Are you familiar with/have you experimented with importrange?
    – Tedinoz
    Commented Jan 9, 2021 at 23:22
  • 1
    Does this answer your question? How do I link a cell in Google Spreadsheets to a cell in another document?
    – Tedinoz
    Commented Jan 9, 2021 at 23:26
  • docs.google.com/spreadsheets/d/… Commented Jan 10, 2021 at 3:14
  • '''=SUM(IMPORTRANGE("1CckyrVFklIdQZa6PCM6mByvV9UK14zEwnoaK0PueTHg", "'statistics'!d6"), IMPORTRANGE("1bsKy3bx9x2sYbM4BBvUjaYDdtveL1XV1l8wp8-CUD1c", "'statistics'!d6")) ''' But i need to copy paste this formula in every cell to count and change the cell range as well.. Also I need to copy the sheet ID every time I need to which is a long process>> my project is having a specific sheet ID for every day in a month.. so monthly I'm counting /sum all 30 sheets Ids in this way.. so i have to repeat for every month.. definitely there is a way but I'm still looking for Commented Jan 10, 2021 at 3:14

1 Answer 1


You can do the following set-up

In cell C2 place ="'statistics'!d6" and so on

Then in cell D2 the below formula and drag it down


enter image description here

  • Thats awsome... Thanks.. you save my life> I could add lot of ranges and sheets ID Commented Jan 10, 2021 at 11:52

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.