Google Sheets – Trigger onEdit When Specific Cells Selected

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I can't seem to figure out how to get my script to trigger only on the edit of specific cells and on the current sheet. I've tried just about everything and it just refuses to run the script at all if I change anything.

I've created new variables to try and select the specific cells, then trigger with an if statement all to no avail. The script below is the only current version I have working which triggers should I edit anything anywhere.

Please someone help me out, I've no idea what I've done wrong.

Edit: Updated the code I've been trying… Still not doing anything.

function onEdit(e) {
var sheet = SpreadsheetApp.getActiveSheet();
var r  = sheet.getRange("D11");

if (r == 'Hide'){

  r.hideRows(11);

}}

Best Answer

Add getValue() to var r = sheet.getRange("D11");. The resulting code line is:

var r  = sheet.getRange("D11").getValue();

The above because getRange returns Range object which not return directly it's value/values.

Also replace r.hideRows(11); by sheet.hideRows(11);

For further details checkout https://developers.google.com/apps-script/guides/sheets

Related Solutions

Google-sheets – onOpen doesn’t execute

Simple Triggers, such as onOpen, are subject to certain restrictions:

They cannot access services that require authorization.

They can modify the file they are bound to, but cannot access other files because that would require authorization.

They may or may not be able to determine the identity of the current user, depending on a complex set of security restrictions.

You were trying to get the email of EffectiveUser (which may or may not be possible) and also to access another spreadsheet (which is certainly impossible).

(If you wonder why this restriction is in place, imagine what would happen if someone shared with you a document with onOpen attached, which would access your other files.)

The solution is to use an installable trigger.

Google-sheets – Change a working function from onOpen() to onEdit()

Yes, if you want a script to be triggered by updates to spreadsheet A, then it should be bound to that spreadsheet. It can still access the other spreadsheets by their URL or Id. For example, this function copies the cells F2:H6 of the present sheet to the cells A1:C5 of another one.

function copyStuff() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName('MasterSheet1')
  var ss2 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/...');
  var targetSheet = ss2.getSheetByName('Sheet1');
  targetSheet.getRange("A1:C5").setValues(sourceSheet.getRange("F2:H6").getValues());
}

Best Answer

Related Solutions

Google-sheets – onOpen doesn’t execute

Google-sheets – Change a working function from onOpen() to onEdit()

Related Topic