Pseudo OnEdit Trigger for Google Spreadsheet without Simple and Installable Triggers using Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

This is a sample script for achieving the pseudo OnEdit trigger for Google Spreadsheet without the simple and the installable triggers using Google Apps Script.

Today, I saw a question at Stackoverflow. The goal of this question is as follows.

  • There is a Google Spreadsheet created by a service account.
  • Goal is to use OnEdit trigger on this Spreadsheet.

I thought a workaround for achieving this goal.

Issue and workaround:

In the current stage, unfortunately, when the owner of Google Spreadsheet is the service account. The Google Apps Script cannot be run. By this, even when onEdit function is put to the script editor, this script cannot be run. It seems that this is the current specification of the Google side. Unfortunately, in the current stage, this goal cannot be directly achieved.

But, I thought that a workaround might be able to be proposed. Fortunately, the built-in functions of the Spreadsheet can be also used in the Spreadsheet created by the service account. I thought that this might be able to be used as a workaround.

And, I remembered the following my posts.

I thought that when these posts are used, a workaround might be able to be proposed. In this post, I would like to propose a workaround for achieving this goal. So, please think of this as the pseudo OnEdit trigger.

The flow of this workaround is as follows.

  1. Prepare 2 Spreadsheets.
  2. Prepare Web Apps.
    • This Web Apps is deployed at the Google Apps Script project created by the Google account that the script can be run. Please be careful about this.
  3. When the pseudo OnEdit trigger is used in “Sheet1”, put IMPORTXML to a cell. IMPORTXML requests to the Web Apps.
    • This Spreadsheet is the Spreadsheet created by the service account.
  4. When the cells in “Sheet1” are edited, the formula is refreshed.

By this flow, when the cells are edited, the Google Apps Script of Web Apps can be run by refreshing the formula. This is the pseudo OnEdit trigger as this workaround.

Usage:

1. Create 2 Spreadsheets

  • Spreadsheet “A”: Create this using the service account.

    • Please share this Spreadsheet with your Google account as the writer.
    • Please set the sheet name to “Sheet1”.

  • Spreadsheet “B”: Create this using your Google account.

    • This Spreadsheet is used as the storage and Web Apps.
    • Please set the sheet name to “Sheet1”.

2. Sample script:

Please copy and paste the following script to the script editor of Spreadsheet “B”.

const targetSpreadsheetId = "###"; // Please set the target Spreadsheet created by the service account.
const sheetName = "Sheet1"; // In this sample, both sheet names of target and storage are the same.

// This function is used as the pseudo OnEdit trigger.
function doGet(e) {
  const response = (v) =>
    ContentService.createTextOutput(
      `<result>Edited at ${new Date().toISOString()}. ${v}</result>`
    ).setMimeType(ContentService.MimeType.XML);
  if (e.parameter.key != "sample") return response("Wrong key.");
  const { range } = e.parameter;
  const lock = LockService.getDocumentLock();
  let res;
  if (lock.tryLock(350000)) {
    try {
      const targetSheet =
        SpreadsheetApp.openById(targetSpreadsheetId).getSheetByName(sheetName);
      const targetValues = targetSheet.getRange(range).getDisplayValues();
      const storageSheet =
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
      const storageValues = storageSheet.getRange(range).getDisplayValues();
      const check = targetValues.reduce((ar, r, i) => {
        r.forEach((c, j) => {
          if (c != storageValues[i][j]) {
            ar.push({
              newValue: c,
              oldValue: storageValues[i][j],
              row: i + 1,
              col: j + 1,
            });
          }
        });
        return ar;
      }, []);
      if (check.length > 0) {
        targetSheet
          .getRangeList(
            check.map(({ row, col }) =>
              targetSheet.getRange(row, col).getA1Notation()
            )
          )
          .setBackground("red");
        res = check;
        storageSheet.clear();
        storageSheet
          .getRange(1, 1, targetValues.length, targetValues[0].length)
          .setValues(targetValues);
      } else {
        res = "No change.";
      }
    } catch (err) {
      return response(JSON.stringify(err));
    } finally {
      lock.releaseLock();
      return response(JSON.stringify(res));
    }
  } else {
    return response("timeout");
  }
}

// At first, please run this script. By this, the current target sheet is saved.
// This workaround uses this saved sheet.
function copyCurrentSheet() {
  const targetValues = SpreadsheetApp.openById(targetSpreadsheetId)
    .getSheetByName(sheetName)
    .getDataRange()
    .getDisplayValues();
  const storageSheet =
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  storageSheet.clear();
  storageSheet
    .getRange(1, 1, targetValues.length, targetValues[0].length)
    .setValues(targetValues);
}

3. Deploy Web Apps.

The detailed information can be seen at the official document.

  1. On the script editor, at the top right of the script editor, please click “click Deploy” -> “New deployment”.
  2. Please click “Select type” -> “Web App”.
  3. Please input the information about the Web App in the fields under “Deployment configuration”.
  4. Please select “Me” for “Execute as”.
    • This is the importance of this workaround.
  5. Please select “Anyone” for “Who has access”.
    • In your situation, I thought that this setting might be suitable.
  6. Please click “Deploy” button.
  7. Copy the URL of the Web App. It’s like https://script.google.com/macros/s/###/exec.

3. Testing.

You can see the demonstration at the top of image.

  1. In this sample script, please put the following formula to a cell “D1”. In this case, please use your Web Apps URL. If this cannot be done, please check the above flow again.

    =IMPORTXML("https://script.google.com/macros/s/###/exec?key=sample&values="&TEXTJOIN(",",TRUE,Sheet1!A1:C5)&"&range=A1:C5","/result")
    • This sample OnEdit trigger uses the cells “A1:C5” of “Sheet1”.
    • This formula is a sample formula. So, please modify the range and formula for your actual situation.

  2. Please edit the cells “A1:C5” on “Sheet1”. By this, the formula of cell “D1” is refreshed, and by this refresh, Google Apps Script of Web Apps is run. This is the pseudo OnEdit trigger as this workaround.

Note:

  • When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
  • You can see the detail of this in the report of “Redeploying Web Apps without Changing URL of Web Apps for new IDE”.
  • In this workaround, I think that you can retrieve the edited cells by checking the difference between before and after the edited sheet. But, this is a simple script for explaining this workaround. So, if you use this workaround, please modify the script for your actual situation.

References:

 Share!