Google sheets range of sheets? [closed]

Question

I have multiple sheets in my Google Spreadsheet. Let's suppose that they are named "1", "2", ... "n". I would like to sum the contents of the B4 cells in all of these sheets. Is it possible to specify a range of sheets, or do I need to meticulously include a reference to each sheet?

Answer 1

You have a Spreadsheet containing n sheets (named "1", "2", "3", "4", "5" and so on) which have identical formatting. You want an efficient way to sum the values of a given cell (B4) from every sheet.

An option is to use a custom function such as sum-sheets-cell.js on Github.

Since your sheets are named in numerical sequence, I have modified the function to automatically insert the sheet names and cell reference.

---

---

/**
 * Variables.
 *
 * You should not have to alter anything in the function,
 * only these variables.
 */
// The name of the sheet the dynamic date exists on.
var dateSheet = 'Sheet8';
// The cell of the sheet defined above in the "dateSheet" variable,
// that the script should update the date in.
var dateCell = 'A1';
// Name of the "Refresh" menu tab and link.
var refreshName = 'Refresh';


/**
 * Add a "Refresh" menu tab and link.
 *
 * We need this to have a way to trigger a callback that will update
 * a specific cell.
 * You can read more about it in the DocBlock for refreshLastUpdate().
 *
 * @See http://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-spreadsheet
 */
function onOpen() {
  // Define the menu item.
  // The refreshName variable is defined at the top of the document.
  var entries = [{
    name : refreshName,
    functionName : "refreshLastUpdate"
  }];

  // Add the menu item.
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  sheet.addMenu(refreshName, entries);
};

/**
 * Update a cell with the current date+time.
 *
 * We do this to work around the lack of references to real fields.
 * Custom functions doesn't update when a field it fetches values from
 * is updated, so we needed another way to recalculate custom functions.
 *
 * @See http://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-spreadsheet
 */
function refreshLastUpdate() {
  // Update the date cell with the current time and date.
  // The "dateSheet" and "dateCell" variables are defined outside of this function, at the top of the file.
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dateSheet).getRange(dateCell).setValue(new Date().toTimeString());
}

/**
 * Get the sum of a cell from multiple sheets.
 *
 * @param {string} Sheets - Reference a cell or write a comma-separated string with the sheets to sum up.
 * @param {string} Range - The range we want to fetch values from. Ex: "A1" or "A1:A6".
 * @param {string} Datetime - A dynamic date string.
 *
 * @return {number} Sum - The sum of the field across multiple sheets. 
 * @customfunction
 */
function SUMSHEETS(sheets, range, datetime) {
  // Dummy data - We need these when we test functionality inside the editor.
   //var sheets = '1,2,3,4,5,6', range = 'B4';

  // Get the active Spreadsheet and sheets.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = ss.getSheets();
  
  // START: code added to automatically get sheet names and cell reference
    var summarysheetname = "summary2";
    var summary = ss.getSheetByName(summarysheetname);
    var startsheet = summary.getRange("B4").getValue();
    var lastsheet = summary.getRange("B5").getValue();
    var range = summary.getRange("B6").getValue();
    var sheetarray = []
    for (var i=startsheet;i<lastsheet+1;i++){
      sheetarray.push(i);
    }
    var sheets = sheetarray.join(","); 
    //Logger.log(sheets)
  // END: code added to automatically get sheet names and cell reference


  // Parse sheets-string to an array of sheet-names.
  var sheets = sheets.split(',');

  // Prepare all sheet-names to a new array.
  // We do this to get the actual name of all the sheets in the Spreadsheet
  // and format them to lowercase so we're case-insensitive.
  var sheetNames = [];
  for (i = 0; i < allSheets.length; i++) {
    sheetNames.push(allSheets[i].getName().toLowerCase());
  }

  // Loop through each of the user-provided sheet-names and find a match
  // with the active Spreadsheet sheets.
  // We do this to fetch the sheet-object with all values etc inside.
  // When we have found a match, we add the values of the user-provided range
  // and sum the values of the range. - This sum will then be added to the
  // total sum that we return at the end of the function.
  var totalSum = 0;
  for (i = 0; i < sheets.length; i++) {
    // Get the index of the sheet-object.
    var sheetIndex = sheetNames.indexOf(sheets[i].toLowerCase());

    // If the sheet exists (the index is bigger than 0. False will result in -1).
    if (sheetIndex > 0) {
      // Get the current sheet.
      var sheet = allSheets[sheetIndex];
      // Get the values of the provided range.
      var values = sheet.getRange(range).getValues();
      // Get the sum of the range.
      var sum = eval(values.join('+'));

      // Add the sum of the range to the total sum.
      totalSum = totalSum + sum;
    }
  }

  // Return the total sum of the range across the sheets.
  return totalSum;
}

Answer 2

Another way to do this is to use the INDIRECT statement. You will still need to list the sheet names, but if they are sequential or if you have a list with the names, you can copy and paste them into a sheet. It's not one cell, but it can do the job.

indirect("'"&A3&"'!"&B3)

https://docs.google.com/spreadsheets/d/1jYI6_n_2l0dDQ1GEPjxSnIf_vEiXHvEe8YhLUN7Jf0M/edit?usp=sharing

sheet name	cell	reference	indirect	sum
Sheet1	A1	'Sheet1'!A1	10	150
Sheet2	A1	'Sheet2'!A1	20
Sheet3	A1	'Sheet3'!A1	30
Sheet4	A1	'Sheet4'!A1	40
Sheet5	A1	'Sheet5'!A1	50