Skip to content

Instantly share code, notes, and snippets.

@ArisBee
Last active December 17, 2017 22:48
Show Gist options
  • Save ArisBee/989643faa687e56633cd974a0d5dd775 to your computer and use it in GitHub Desktop.
Save ArisBee/989643faa687e56633cd974a0d5dd775 to your computer and use it in GitHub Desktop.
Google App Script function looking for a list of values from a sheet column in another sheet column and deleting the corresponding lines.
function checker () {
// Delete all the row associated to a list of values contained in an other sheet
var sss = SpreadsheetApp.openById('X'); // Replace X with spreadsheet ID
var ts = sss.getSheetByName('sheet1'); // Replace sheet1 with destination Sheet tab name
var ss = sss.getSheetByName('sheet2'); // Replace sheet2 with source Sheet tab name
var startRowTS = 1; // First row of data to process in Sheet1
var startRowSS = 1; // First row of data to process in Sheet2
var numRowsTS = ts.getLastRow() ; // Number of rows to process destination table
var numRowsSS = ss.getLastRow(); // Number of rows to process source table
var range1 = ss.getRange(1, 1, numRowsSS, 10); // Assign the range you want to copy
var range2 = ts.getRange(1, 1, numRowsTS, 10); // Assign the range you want to copy
var data1 = range1.getValues();
var data2 = range2.getValues();
var cell;
var rowsDeleted = 0; // variable to compensate offset caused by row deletion
for (var i = startRowSS; i <= numRowsSS; ++i) {
// Bellow replace y by the column number (+1) where you get value, z where you look for it
var pos = rowOf(data1[i][y], z, data2);
if (pos != 0)
{
Logger.log( "Row "+ pos+ " is deleted!")
ts.deleteRow( pos - rowsDeleted);
rowsDeleted++;
// Alternative to just color the cell in red instead of row
// Logger.log( "The cell containing "+data1[i][y]+" on row "+pos+" is red!")
// cell = range2.getCell(pos, z);
// cell.setBackground('red');
}
}
}
function rowOf(containingValue, columnToLookInIndex, data) {
// This function looks for a value in a column and return its position at first appearance
var outRow = 0;
for (var i = 0; i < data.length; i++)
{
if (data[i][columnToLookInIndex] == containingValue)
{
outRow = i+1;
break;
}
}
return outRow;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment