Trouble triggering a macro when a specific cell has a specific value

0
votes

I'm trying to trigger a google sheet macro / apps script to reformat a sheet when a specific value is entered into a specific cell. I have been working on it to get it to work but without success.

Here is my code;

function onEdit(e) {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getRange('C2');
  var cellContent = cell.getValue();

  if(cellContent === 'Past campaigns - actual cashflows to date only') {
    spreadsheet.getRange('7:12').activate();
    spreadsheet.getActiveSheet().hideRows(spreadsheet.getActiveRange().getRow(), spreadsheet.getActiveRange().getNumRows());
    spreadsheet.getRange('13:13').activate();
  }
};

I'm new to macros and apps scripts. I have tried to implement all the suggestions from the following links without success;

2
google-apps-scriptgoogle-sheetstriggers
onEdit(e) runs when a user changes a value in a spreadsheet. Note when USER changes a value. - Cooper
Hi @Cooper - thanks for getting involved........................... I'm still missing something obvious here though so I hope you can help further..................... you emphasize 'USER'. So, are you explaining that because I have created the apps script I'm not a 'USER' and therefore when I make changes it will not work - but if someone other than me makes changes it will work? Thanks - NeilC
edit triggers only fires when an actual user edits a cell on the standard google spreadsheet user interface. - Cooper
Thanks @Cooper - well I tried to trigger the macro with a user on the standard google spreadsheet user interface - it still doesn't trigger - so I think the problem with the script is elsewhere... I might try and attack this challenge from a different angle... thanks for trying to help - NeilC
I just checked on my site by putting e.source.toast('Entry'); on the top line and making an edit and it triggered and displayed the toast. So you have problems in the script. - Cooper

2 Answers

0
votes

As @Cooper has said you cannot make a trigger based on a cell value. Only on user changes.

You could try to make the onEdit trigger to execute only inside an if statement. But that's already what you have done.

0
votes

Try this:

function onEdit(e) {
  e.source.toast('Entry');
  const sh=e.range.getSheet();  
  if(sh.getName()!='Sheet Name')return;//you need to change this sheetname
  const X=sh.getRange('C2').getValue();
  if(X=='Past campaigns - actual cashflows to date only') {
    e.source.toast('flag1');
    sh.hideRows(e.range.rowStart,e.range.rowEnd-e.range.rowStart+1);
  }
}

It's not necessary to use activate in most scripts and certainly in this one since it has to finish in 30 seconds. They use activate in macros because they are following you actions as you move around the screen. But in a script, generally to don't want to interact with the screen very much because it slows down the script and it doesn't really accomplish anything.

I'm currently using in a script that run when a Spreadsheet opens up because it has a lot rows in it and I want it to go down to the last row. So activate is useful because I don't have to scroll down to the last row every time I open the spreadsheet.

try it this way:

function onEdit(e) {
  //e.source.toast('Entry');
  var sh=e.range.getSheet();  
  if(sh.getName()!='Sheet Name')return;//you need to change this sheetnamee
  var X=sh.getRange('C2').getValue();
  if(X=='Past campaigns - actual cashflows to date only') {
    //e.source.toast('flag1');
    sh.hideRows(e.range.rowStart,e.range.rowEnd-e.range.rowStart+1);
  }
}

I hope you're not trying to run this from the script editor because that's not going to work.