CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-macros
Geert Schuring
Geert Schuring

Reputation: 2163

How to make Google Sheets auto update my macros after adding row?

Problem: After adding a row in my Google Sheet my existing macros stop working because the cells they refer to are now shifted one row.

Question: Is there a way to make Google Sheets auto update my macros just like it updates all the functions in the sheet?

Upvotes: 0

Views: 224

Answers (1)

ziganotschka
ziganotschka

Reputation: 26806

Use PropertiesService and onChange trigger

  • PropertiesService allows you to store a variable (like the row of interest) between script runs
  • The installable trigger onChange fires automatically when a change (such as row insertion) has taken place
  • You can set-up the latter to modify the row of interest if another row has been inserted above

Sample:

var sheetToWatch = "Sheet1";

function setUp(){  
  PropertiesService.getScriptProperties().setProperty("row", 4);
}

function onChange(e) {
  var row = parseInt(PropertiesService.getScriptProperties().getProperty("row"));
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  Logger.log(e.changeType);
  Logger.log(sheet.getName());
  Logger.log(sheetToWatch);
  Logger.log(sheet.getActiveRange().getRow());
  if (e.changeType == "INSERT_ROW"  &&  sheet.getName() == sheetToWatch && sheet.getActiveRange().getRow()<=row){
    row++;
  }
}


function withRow(){
  var row = parseInt(PropertiesService.getScriptProperties().getProperty("row"));
  // do something with my row
}

Instructions:

  • Run fucntion setUp() once to set the initial row
  • If rows above this row will be inserted, onChange() will change the var row automatically (do not forget to bind an installable trigger to onChange() )
  • Run your own function (called here withRow()) and retrieve the actual row from ScripProperties

Upvotes: 1

Related Questions