0

I'm working on a spreadsheet right now that pulls data from a Salesforce report. My question is if it's possible to delete a row within a specific spreadsheet if a cell within the column is updated.

So for instance, if A2 is modified in any way, delete A2:D2 as a reaction. The reason for this is because the A cells that the data pulls data from may occasionally change and be removed from the source report. A2 references specific data.

If the reference data is absent, rather than A2 trying to reference other data, I would like for A2 and the associated row to be deleted instead.

Tedinoz
  • 5,911
  • 3
  • 25
  • 35
Mijinion
  • 1
  • 1
    Welcome to StackOverflow! Can you add any code that you have tried? or any tools that you used to achieve and felt there are any shortcomings? that would help others to give specific answers to your question. Thanks! – S Raghav Aug 29 '19 at 19:24
  • Welcome. This is a common topic as your research would have shown. The answer to your question is "Yes, it can be done". Feel free to Google more widely, but these are good starters: [Delete part of row if checkbox is checked](https://stackoverflow.com/q/57716601/1330560), or [Delete Row in Google Sheet depending on other Google Sheet cell value](https://stackoverflow.com/q/38270788/1330560), or a variation on a theme [Delete a row in Google Spreadsheets if value of cell in said row is 0 or blank](https://stackoverflow.com/q/11058019/1330560). – Tedinoz Aug 30 '19 at 01:15
  • Further afield, I noticed the usually reliable Yagisanatode has [Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet](https://yagisanatode.com/2019/06/12/google-apps-script-delete-rows-based-on-a-columns-cell-value-in-google-sheet/). – Tedinoz Aug 30 '19 at 01:17

1 Answers1

0

  • It is possible to delete a row automatically with an onEdit trigger, when a column is updated.

  • However, keep in mind that onEdit is only fires when the column has been edited by a human, not if the data is pulled programmatically from elsewhere, or updated with a formula or script.See trigger restrictions.

If the latter reflects your situation, you need to use a workaround:

  • You can install a time-driven trigger which compares in intervals of your choice either the value has changed. enter image description here

  • To compare the values, you can store the last value as a script property.

Sample:

function myFunction()
{
  var ss = SpreadsheetApp.getActive().getActiveSheet();
  var cell=ss.getRange('A2').getValue();
  var value = PropertiesService.getScriptProperties().getProperty('lastCellValue');
  if(value){
    if(cell!=value){
      ss.getRange('A2:D2').clear();
    }
    else{
      Logger.log('cell content did not change');
    }
  }
  else{
    Logger.log('This is the first call, so changes are not tracked yet');
  }
  PropertiesService.getScriptProperties().setProperty('lastCellValue', cell);
}
ziganotschka
  • 25,866
  • 2
  • 16
  • 33