I am trying to figure out why my function upon iterating through the loop doesn't increment to the next value in the range of column values. I will share for reference (below). The function is intended to pass the sheet and search key to find the values in two cols in a staging and prod sheet and return the rowNum that matches the search key in both sheets. Then delete the corresponding row. Please see the logs and execution transcript (following code)
function findInColumn(sheet, data)
{
if(sheet.getName() == "Data" || sheet.getName() == "Info")
{
var column = sheet.getRange("D" + ":" + "D"); // like A:A
var values = column.getValues();
var row = 0;
while ( values[row] && values[row][0] != data ) {
row++;
}
if (values[row][0] == data)
return row+1;
else
return -1;
}
else if(sheet.getName() == "AdData" || sheet.getName() == "AdInfo")
{
var columnID = sheet.getRange("C" + ":" + "C"),
columnEmail = sheet.getRange("I"+ ":" + "I"); // like A:A
var valuesID = [columnID.getValues()],
valuesEmail = [columnEmail.getValues()];
for(var row = 0;row<valuesID.length;row++)
{
if(valuesID[row][0] == data)
{
return Logger.log(row+1);
}
else if(valuesEmail[row][0] == data)
{
return Logger.log(row+1);
}
else
{
Logger.log("ID " + valuesID[row][0] + "\nEmail " + valuesEmail[row][0] + "\n");
continue;
}
}
Logger.log("No matching ID AND Email were found");
}
else
{
return Logger.log("No sheet was found with that name.");
}
}
Execution Transcripts:
[18-11-07 11:17:00:183 PST] SpreadsheetApp.getActiveRange() [0 seconds]
[18-11-07 11:17:00:183 PST] Range.getRow() [0 seconds]
[18-11-07 11:17:00:183 PST] Range.getLastRow() [0 seconds]
[18-11-07 11:17:00:183 PST] Range.getColumn() [0 seconds]
[18-11-07 11:17:00:183 PST] Range.getLastColumn() [0 seconds]
[18-11-07 11:17:00:183 PST] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[18-11-07 11:17:00:190 PST] Starting execution
[18-11-07 11:17:00:194 PST] Logger.log([Success! Named Values Site Admin Opt: Remove Site Admin, []]) [0 seconds]
[18-11-07 11:17:00:195 PST] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[18-11-07 11:17:00:304 PST] Spreadsheet.getSheetByName([Admin Data]) [0.109 seconds]
[18-11-07 11:17:00:305 PST] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[18-11-07 11:17:00:305 PST] Spreadsheet.getSheetByName([Admin Info]) [0 seconds]
[18-11-07 11:17:00:306 PST] Logger.log([123 [email protected] , []]) [0 seconds]
[18-11-07 11:17:00:306 PST] Sheet.getName() [0 seconds]
[18-11-07 11:17:00:306 PST] Sheet.getName() [0 seconds]
[18-11-07 11:17:00:306 PST] Sheet.getName() [0 seconds]
[18-11-07 11:17:00:306 PST] Sheet.getRange([C:C]) [0 seconds]
[18-11-07 11:17:00:307 PST] Sheet.getRange([I:I]) [0 seconds]
[18-11-07 11:17:00:562 PST] Range.getValues() [0.255 seconds]
[18-11-07 11:17:00:740 PST] Range.getValues() [0.176 seconds]
[18-11-07 11:17:00:741 PST] Logger.log([ID Site ID Email Admin Email , []]) [0 seconds]
[18-11-07 11:17:00:741 PST] Logger.log([No matching SiteID AND Admin Email were found, []]) [0 seconds]
[18-11-07 11:17:00:742 PST] Sheet.getName() [0 seconds]
[18-11-07 11:17:00:742 PST] Sheet.getName() [0 seconds]
[18-11-07 11:17:00:742 PST] Sheet.getName() [0 seconds]
[18-11-07 11:17:00:742 PST] Sheet.getRange([C:C]) [0 seconds]
[18-11-07 11:17:00:743 PST] Sheet.getRange([I:I]) [0 seconds]
[18-11-07 11:17:01:001 PST] Range.getValues() [0.257 seconds]
[18-11-07 11:17:01:002 PST] Range.getValues() [0 seconds]
[18-11-07 11:17:01:004 PST] Logger.log([ID Site ID Email Admin Email , []]) [0 seconds]
[18-11-07 11:17:01:004 PST] Logger.log([No matching SiteID AND Admin Email were found, []]) [0 seconds]
[18-11-07 11:17:01:004 PST] Sheet.getName() [0 seconds]
[18-11-07 11:17:01:004 PST] Sheet.getName() [0 seconds]
[18-11-07 11:17:01:004 PST] Sheet.getName() [0 seconds]
[18-11-07 11:17:01:005 PST] Sheet.getName() [0 seconds]
[18-11-07 11:17:01:005 PST] Sheet.getRange([C:C]) [0 seconds]
[18-11-07 11:17:01:006 PST] Sheet.getRange([I:I]) [0 seconds]
[18-11-07 11:17:01:255 PST] Range.getValues() [0.248 seconds]
[18-11-07 11:17:01:556 PST] Range.getValues() [0.3 seconds]
[18-11-07 11:17:01:557 PST] Logger.log([ID Site ID Email Admin Email , []]) [0 seconds]
[18-11-07 11:17:01:558 PST] Logger.log([No matching SiteID AND Admin Email were found, []]) [0 seconds]
[18-11-07 11:17:01:558 PST] Sheet.getName() [0 seconds]
[18-11-07 11:17:01:558 PST] Sheet.getName() [0 seconds]
[18-11-07 11:17:01:558 PST] Sheet.getName() [0 seconds]
[18-11-07 11:17:01:558 PST] Sheet.getName() [0 seconds]
[18-11-07 11:17:01:558 PST] Sheet.getRange([C:C]) [0 seconds]
[18-11-07 11:17:01:559 PST] Sheet.getRange([I:I]) [0 seconds]
[18-11-07 11:17:01:817 PST] Range.getValues() [0.257 seconds]
[18-11-07 11:17:02:035 PST] Range.getValues() [0.217 seconds]
[18-11-07 11:17:02:036 PST] Logger.log([ID Site ID Email Admin Email , []]) [0 seconds]
[18-11-07 11:17:02:037 PST] Logger.log([No matching SiteID AND Admin Email were found, []]) [0 seconds]
[18-11-07 11:17:02:037 PST] Sheet.getName() [0 seconds]
[18-11-07 11:17:02:037 PST] Sheet.getName() [0 seconds]
[18-11-07 11:17:02:037 PST] Sheet.getName() [0 seconds]
[18-11-07 11:17:02:038 PST] Sheet.getRange([C:C]) [0 seconds]
[18-11-07 11:17:02:038 PST] Sheet.getRange([I:I]) [0 seconds]
[18-11-07 11:17:02:316 PST] Range.getValues() [0.278 seconds]
[18-11-07 11:17:02:318 PST] Range.getValues() [0 seconds]
[18-11-07 11:17:02:320 PST] Logger.log([ID Site ID Email Admin Email , []]) [0 seconds]
[18-11-07 11:17:02:320 PST] Logger.log([No matching SiteID AND Admin Email were found, []]) [0 seconds]
[18-11-07 11:17:02:324 PST] Execution failed: Cannot find method deleteRow((class)). (line 113, file "macros") [2.127 seconds total runtime]
