Retrieving a cell from multiple sheets using a SheetName() array to dynamically link to each sheet

Question

I’m trying to create a dynamic, automatically updating Google spreadsheet which may or may not be beyond spec.

GOAL:

I am tracking tutoring hours logged per month in a Google sheet. Each month has its own sheet within the Google sheet workbook. To make a new month, I just duplicate the last month and delete the contents. I have a summary sheet to track hours logged per month, payments made, etc. I would like to automate as much of this worksheet as possible.

APPROACHES TAKEN:

I have used the following code to get an array which will automatically update when I add another sheet next month. So far so good as show in column B:

function sheetnames() {
  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i=0 ; i<sheets.length ; i++) out.push( [ sheets[i].getName() ] )
  return out 
}

/*https://www.extendoffice.com/documents/excel/5222-google-sheets-get-list-of-sheets.html#a2*/

The next step is to get a specific cell on each sheet. I can manually type in each sheet name and then the linking works fine. E.g. ‘Aug2021’!$E$35 dashboard page so far

WHAT DIDN’T WORK:

But I’m trying to automate this process. This means that the above just won't cut it.

Here's what I tried.

=B10!$E$35

='B10’!$E$35

='(TO_TEXT(B10))’!$E$35

THE ASK:

I'm obviously not a programmer, but by God have I got a taste for it now. Am I having an issue because I'm dealing with an array? How do I call a sheet name from an array such that it behaves...as sheet names normally do? How do I compensate? Do I need a new piece of code where I can collect the information in a specific cell or range of cells by sheet? Would this code use the "get" command?

Answer 1

use:

=INDIRECT(B10!$E$35)

but note that INDIRECT does not work with arrays

---

here is an alternative example:

sum E35 across all sheets regardless if the sheet exists or not or if the sheet gets created in the near future

cell A1:

=ARRAYFORMULA("=SUM({"&TEXTJOIN("; ", 1, 
 "IFERROR("&UNIQUE(TEXT(ROW(INDIRECT(1*"31/08/2021"&":"&1*(TODAY()+90))), 
 "mmmyyyy\!E35"))&", """")")&"})")

script:

function onEdit() { 
var sheet = SpreadsheetApp.getActive().getSheetByName('Master Sheet');  
var src = sheet.getRange("A1");   // The cell which holds the formula
var str = src.getValue(); 
var cell = sheet.getRange("C5");  // The cell where I want the results to be
cell.setFormula(str);             // Setting the formula.
}