Bring value of A1 from all active sheet in to Master report

user_17927157 0
Viewed 15

Each sheet represents the client profile with name in F1 and email address in F3. We add a new sheet for any new client. I need to have a Master Report which will show values of F1 of all sheets (names of clients) in column A and values of F3 of all sheets(email addresses) in column B. Report needs to be updated once a new client is added. Thank you.

google-sheets-formula
1 Answers

You can use this custom formula:

function getF1F3Data() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets()
                             .filter(sheet => sheet.getSheetName() != 'main');
  return sheets.map(sheet => [sheet.getRange("F1").getValue(), sheet.getRange("F3").getValue()]);
}

output

Matches all sample data put into Sheet2 and Sheet3.

user_161186860
Related