Google-Sheet - query multiple unknown sheets?

Question

Context: I need to develop a Google Sheet to manage yearly cost and revenues from the Charity where I volunteer. This file will be updated from volunteers, so I need to develop a structure which they can use with easiness.

My idea:

• have a front sheet with a summary from activities and charts
• let volunteers duplicate a template event cost and revenue and add their c&r

Result would have as much sheets as many Events and Charity or Fundraising events/campaign.

But, since the number of sheets is unknown till the end of the year, how can I have a summary sheet?

E.G. How can I sum all costs and all rev? Can I query to search in **all existing sheets the cell that follows an exact string (e.g. Total Rev)?**

Answer 1

You can write an Apps Script - a Google Script service based on Javascript

It is relatively simple to learn and use and it will allow you to loop dynamically through all the sheets without knowing in advance how many there will be.

The following sample script iterates through all sheets minus the master sheet of the spreadsheet to which it is bound and sums the values of the cells "A10" (if this is the cell where you have your costs) together. Finally it sets the value of the sum into the cell "A10" fo the master sheet.

function myFunction() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheets=ss.getSheets();
  var sum=0;
  for (var i=0; i<sheets.length; i++){
    if(sheets[i].getName()!="Master"){
      var value=sheets[i].getRange("A10").getValue();
      sum=sum+value;
    }
  }
  ss.getSheetByName("Master").getRange("A10").setValue(sum);
}

If you take some time to get familiar with Apps Script, you will be able to easily adapt the sample above to your needs.