CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-apps
Vin
Vin

Reputation: 2165

Google Apps Script to delete rows from two different spreadsheets

I've done a lengthy search and couldn't find what I'm looking for. Maybe someone out there can kindly help?

I have two Google Spreadsheets, Spread1 and Spread2. I will only be referring to sheet1 of both spreadsheets.

I need a script that deletes rows containing "Hello" in column B of Spread1 but only if "Hello" appears in column B of Spread2.

If rows are deleted (if the same word appears in both columns), then I would like the script to go back to Spread2 and also delete the rows with "Hello" in column B.

So after the script has run, "Hello" will not appear in column B in either of the spreadsheets.

If anyone knows how to achieve this I will be very grateful indeed - your valuable help will be much appreciated.

Thanks for looking!

EDIT:

I have this script which deletes rows in Spread2 that contain "hello" in column B: 

function deleteRow() {
  var ss = SpreadsheetApp.openById("SPREADSHEET KEY"); 
  SpreadsheetApp.setActiveSpreadsheet(ss);
  var s = ss.getSheetByName('Sheet1'); // change to your own
  var values = s.getDataRange().getValues();
  for( var row = values.length -1; row >= 0; --row )
     if (values[row][1] == 'Hello')
      s.deleteRow(parseInt(row)+1);
};

but I only want it to do so when "Hello" appears in column B of Spread1. Any ideas? Thanks

Upvotes: 0

Views: 2892

Answers (2)

Mogsdad
Mogsdad

Reputation: 45750

To simplify this solution, I'm using the 2D Array Library, for ArrayLib.find(). You could instead write a function that loops through rows in sheet2 to find 'Hello'.

function deleteRow() {
  // This assumes script is independent of either sheet
  var s1 = SpreadsheetApp.openById("SPREADSHEET KEY1").getSheetByName('Sheet1'); 
  var s2 = SpreadsheetApp.openById("SPREADSHEET KEY2").getSheetByName('Sheet1'); 

  var values1 = s1.getDataRange().getValues();
  var values2 = s2.getDataRange().getValues();

  // Check if "Hello" appears in column B of Spread2.
  if (ArrayLib.find(values2, 1, 'Hello') != -1) {
    // Yes it does, so delete rows containing "Hello" in column B of Spread1
    for( var row = values.length -1; row >= 0; --row )
       if (values1[row][1] == 'Hello')
        s1.deleteRow(parseInt(row)+1);
    }
  }
}

You can further optimize this by removing the call to deleteRow(), modifying Spread1 in array form, then calling clearContent() and setValues() to write the modified array back to the sheet. This way, you replace n service calls with 2, and save lots of run time.

Upvotes: 1

DavidF
DavidF

Reputation: 1345

You can refer to both spreadsheets in the same script and process the two arrays in step

function deleteRow() {
      var ss = SpreadsheetApp.openById("SPREADSHEET KEY"); 
      SpreadsheetApp.setActiveSpreadsheet(ss);
      var s = ss.getSheetByName('Sheet1'); // change to your own
      var values = s.getDataRange().getValues();
      var ss1 = SpreadsheetApp.openById("SPREADSHEET KEY1"); 
      var s1 = ss1.getSheetByName('Sheet1'); 
      var values1 = s1.getDataRange().getValues();
            // assumes sheets have same number of rows and you wish to delete corresponding rows in each
      for( var row = values.length -1; row >= 0; --row )
         if (values[row][1] == 'Hello' || values1[row][1] == 'Hello')
          s.deleteRow(parseInt(row)+1);  // parseInt is not required
          s1.deleteRow(parseInt(row)+1);
    };

Upvotes: 1

Related Questions