google-sheetsgoogle-apps-scripttriggersformatting

onEdit() e.source returning first sheet in spreadsheet, not edited sheet


For context, I have a google sheet that we use for inventory and pricing. I have made another spreadsheet that imports the ranges that we need from the original to show wholesale inventory to customers without any pricing. I am currently using ImportRange() to copy the content, but I would like to maintain the formatting of the previous sheet.

I am using this onEdit() script to update the formatting (I am using a trigger in the original):

function setFormatOnEdit(e) {
  if (!e)
    throw new Error("This function is automatically called, do not run this manually")
  const lock = LockService.getScriptLock()
  if (lock.tryLock(350000)) {
    try {
      const {
        master,
        sources
      } = variables_();
      const { range, source } = e;
      const { editRange } = sources.find(({ sheetName }) => sheetName == source.getSheetName());
      const srcRange = source.getRange(editRange);
      const editedSheetName = range.getSheet().getSheetName();
      if (editedSheetName != srcRange.getSheet().getSheetName())
        return;
      //Reformatting Code Here
    } catch ({stack}) {
      console.error(stack)
    } finally {
      lock.releaseLock();
      console.log("Done");
    }
  } else { console.error("Timeout") }
}

And variables_() is:

function variables_() {
  const masterSpreadsheetId = "###"

  const sourceSheetName1 = "Mixed Oak"
  ...
  const sourceSheetName13 = "Misc/Random Inventory"

  const sourceSheet1Range = "Mixed Oak!A1:I"
  ...
  const sourceSheet13Range = "Misc/Random Inventory!A1:I"

  return {
    master: { masterSpreadsheetId },
    sources: [
      { sheetName: sourceSheetName1, editRange: sourceSheet1Range },
      ...
      { sheetName: sourceSheetName13, editRange: sourceSheet13Range }
    ]
  };
}

Currently I am having the issue of e.source returning the first sheet in the source spreadsheet (which is a summary of pricing sheet; I do not want to copy this sheet over) after every edit, regardless of which sheet is edited.

This poses a problem because I am trying to use the sheet name of the edited original to reformat the copy sheet on each edit.

I could just use conditional formatting to reformat the copy, but there are less options possible and I would need to do a search through all rows for certain formatting parameters, which would take forever to update and is just clunky.

Let me know if there is a way to fix this issue, or if there is a better way to do what I am trying to do with copying over.


Solution

  • The source property of the on-edit event trigger returns the active SpreasheetApp.Spreadsheet, not a SpreadsheetApp.Sheet.

    On the other hand, the following part of the script in the question doesn't make sense to me:

    const { editRange } = sources.find(({ sheetName }) => sheetName == source.getSheetName());
    const srcRange = source.getRange(editRange);
    const editedSheetName = range.getSheet().getSheetName();
    if (editedSheetName != srcRange.getSheet().getSheetName())
            return;
    

    If you are looking to get the edited range use e.range, e.source.getActiveSheet().getActiveRange() or SpreadsheetApp.getActiveSheet().getActiveRange(). The first option is preferred for brevity, and intuitively, it should be faster. Also, the object returned is extended with the properties rowStart, columnStart, rowEnd and columnEnd, which are handy in many use cases.

    To get the name of the edited sheet you might use e.range.getSheet().getName() or e.source.getActiveSheet().getName(), SpreadsheetApp.getActiveSheet().getName(). Any of the first two options are preferred.

    Note: Regarding explaining spreadsheet concepts and how Google spreadsheet works, I think that the Google Sheets API documentation is more complete and clear than the documentation of the Google Apps Script Spreadsheet Service and even the Google Sheets section on https://support.google.com/docs. I mentioned this because the Google Sheets API documentation mentions how is determined the sheet or range to be returned on several cases.