Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Created August 17, 2022 02:06
Show Gist options
  • Save tanaikech/5939e3de5e24ce389beade94158bdb7d to your computer and use it in GitHub Desktop.
Save tanaikech/5939e3de5e24ce389beade94158bdb7d to your computer and use it in GitHub Desktop.
Moving Cell Detection on Google Spreadsheet using Google Apps Script

Moving Cell Detection on Google Spreadsheet using Google Apps Script

This is a sample script for detecting whether the specific cells on Google Spreadsheet are manually moved using Google Apps Script.

In this case, the named range, OnChange trigger, and PropertiesService are used.

Usage:

1. Create a named range.

As a sample, please create a named range to the cells "A2:B2" as "sampleNamedRange1". Ref

2. Prepare sample script.

Please copy and paste the following script to the script editor of Spreadsheet. And, please install OnChange trigger to the function installedOnChange.

function installedOnChange(e) {
  var originalRange = "A2:B2";
  var nameOfNamedRange = "sampleNamedRange1";

  if (!["INSERT_ROW", "REMOVE_ROW"].includes(e.changeType)) return;
  var p = PropertiesService.getScriptProperties();
  var pv = p.getProperty("range");
  if (!pv) pv = originalRange;
  var range = e.source.getRangeByName(nameOfNamedRange);
  var a1Notation = range.getA1Notation();
  if (a1Notation != pv) {
    p.setProperty("range", a1Notation);
    Browser.msgBox(
      `Range was changed from "${pv}" to "${a1Notation}". Original range is "${originalRange}".`
    );
  }
  // var values = range.getValues(); // The values are not changed because of the named range.
}

3. Testing.

In this case, please do I added a row under A1:B1. By this, the script is automatically run by the OnChange trigger. And, you can see the dialog. You can see the demonstration as shown in the top image.

Note:

  • This is a simple sample script. So, please modify this for your actual situation.

References:

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment