Delete Row in Google Sheet depending on other Google Sheet cell valuie

Question

Is it possible to write a google script that will delete a row in a Google Sheet, based on cell values for a given range, in another google sheet?

I've done some research on how to do this all in the same google sheet, but my limited experience with writing google sheet scripts has prevented me from understanding if what I described is possible, and how.

This is a script I have so far. This will delete rows in a range of my active spreadsheet if a cell in Column F contains the word "id:123456". What I'd like to be able to do, is to modify this code so that if the word "id:123456" is found, it will look in another column of another Google Sheet, and delete rows that contain "id:123456".

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Sheet1'); // change to your own
  var values = s.getDataRange().getValues();

  for (var row in values)
    if (values[row][6] == 'id:123456')

      s.deleteRow(parseInt(row)+1);
};

Do you want to delete everything in the second sheet that's present in the first sheet, or do you want to use the first sheet as a flag to determine whether a specific element from should deleted from the second sheet? — Matthew Graves
@MatthewGraves Yeah essentially a flag. I want to use the values in the first sheet to determine if anything in the second sheet needs to be deleted. I have other code that does things to the first sheet afterwards — qaguy94

Matthew Graves Matthew Graves · Accepted Answer · 2016-07-08T16:05:37

I haven't tested this code, so you might need to do some modifications to run successfully. But the basic idea is simple: have two sheets open at once, and based on the if from the first sheet make changes in the second sheet.

var ss1 = SpreadsheetApp.getActiveSpreadsheet();
var ss2 = SpreadsheetApp.getByID(0); //Change to the other sheet's ID, or somehow open it.
var s1 = ss1.getSheetByName('Sheet1'); // change to your own
var s2 = ss2.getSheetByName('Sheet1'); // change to your own
var values = s1.getDataRange().getValues();

for (var row in values)
  if (values[row][6] == 'id:123456')
    var other_values = s2.getDataRange().getValues();
      for (var other_row in other_values)
        if (other_values[row][6] == 'id:123456')
          s2.deleteRow(parseInt(other_row)+1);

Main thing to be worried about and test: I copied your delete code, but since we're deleting rows, unless we start from the bottom we might start missing. (Suppose rows 4 and 8 out of 10 contain something to be deleted; you might end up deleting 4, which turns 8 to 7, but then we still delete row 8--meaning that we've missed a row we should have deleted and deleted a row we shouldn't have.)

Delete Row in Google Sheet depending on other Google Sheet cell valuie

1 Answers