Moving Cell Detection on Google Spreadsheet using Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

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:

 Share!