I have a query in cell I25 of my first sheet in Googlesheets:

=query({SheetsRange("A13:F17")}, "select sum(Col6) where Col1 contains '"&D7&"' ")

The "SheetsRange" is an Appscript function (thanks to the author!) that gets all of the sheet names for me:

/**
 * Returns concatened string range of all the sheets exept current sheet.
 *
 * @param {"A1:B5"} range - Input range as string that gets concatened to the sheetnames.
 * @return {string} all the sheets with range together
 * @customfunction
 */
function SheetsRange(range) {
    const ss = SpreadsheetApp.getActiveSpreadsheet()
    const currentSheet = ss.getActiveSheet().getName()
    const sheets = ss.getSheets()
 
    const output = []
 
    sheets.forEach(sheet => {
        const sheetname = sheet.getName()
        console.log(sheetname)
        if (sheetname != currentSheet) {
            const values = sheet.getRange(range).getValues()
            values.forEach(row => {
                output.push(row)
            })
        }
    })
 
    return output
 
}

Whenever I update a cell in a sheet, the sum in I25 should reflect these changes. However, it only does this when I hit save on the Appscript.

I think I need an onEdit(e) function in order to catch any changes made on the spreadsheet and call the SheetsRange function? however I've not been able to adapt examples I've found to work for me.

For your amusement, my current attempt of using onEdit is:

function onEdit(e) {
  if (!e) {
    throw new Error('Please do not run the script in the script editor window. It runs automatically when you edit the spreadsheet.');
  }
  if (e.range.getA1Notation() !== 'F12:F') {
    return;
  }

  SheetsRange();
}

Thank you for any help!

CodePudding user response：

In your situation, how about the following modification?

Modified script:

function onEdit(e) {
  if (!e) {
    throw new Error('Please do not run the script in the script editor window. It runs automatically when you edit the spreadsheet.');
  }

  // I modified below script.
  const { range, source } = e;
  if (range.columnStart != 6 || range.rowStart == 1) {
    return;
  }
  var formula = "SheetsRange";
  var tempFormula = "sample";
  source.createTextFinder(formula).matchCase(false).matchFormulaText(true).replaceAllWith(tempFormula);
  source.createTextFinder(tempFormula).matchFormulaText(true).replaceAllWith(formula);
}

• In this modification, when the cells of column "F" is edited, your showing formula of =query({SheetsRange("A13:F17")}, "select sum(Col6) where Col1 contains '"&D7&"' ") is recalculated.

References:

• createTextFinder(findText) of Class Spreadsheet
• Class TextFinder

CodePudding user response：

You don't really need onEdit() function to do this... you just need to change the concept of the script.

Costume functions do updates the output results while input got changes. The reason why yours do not update is because your input is a plain text that doesn't change.

If you change the formula into this:

=LAMBDA(SHEETNAMES,RANGE,
 LAMBDA(RLEN,CLEN,SROW,SCOL,
  LAMBDA(NAMECOUNT,
   LAMBDA(ARRAY,
    QUERY(ARRAY,"SELECT SUM(Col6) WHERE Col1 CONTAINS'"&D7&"'")
   )(
    MAKEARRAY(RLEN*NAMECOUNT,CLEN,LAMBDA(R,C,
     INDIRECT("'"&INDEX(SHEETNAMES,ROUNDUP(R/RLEN))&"'!R"&IF(MOD(R,RLEN)=0,RLEN SROW,MOD(R,RLEN) SROW)&"C"&C SCOL,FALSE)
    ))
   )
  )(COUNTA(SHEETNAMES))
 )(
  REDUCE(0,REGEXEXTRACT(RANGE,"(\d )\D (\d )"),LAMBDA(A,B,B-A)) 1,
  REDUCE(0,BYCOL(REGEXEXTRACT(RANGE,"(\D )\d :(\D )"),LAMBDA(COL,COLUMN(INDIRECT(COL&"1")))),LAMBDA(A,B,B-A)) 1,
  REGEXEXTRACT(RANGE,"\d ")-1,
  COLUMN(INDIRECT(REGEXEXTRACT(RANGE,"\D ")&"1"))-1
 )
)(getSheetNames(),"A5:B10")

And change the script into this:

function getSheetNames() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const thisSheet = ss.getActiveSheet().getSheetName();

  return ss.getSheets().map(sheet => sheet.getSheetName()).filter(name => name!==thisSheet);
}

Instead of using Apps-script to get the values of each sheet, this example only return a list of sheet names with it.

And we uses INDIRECT() in R1C1 format to create the import data array, which since it is a build-in spreadsheet formula, it is always dynamic.

The results should be updated whenever the reference sheets got updated even without onEdit() trigger.