时间戳

首页 > 解决方案 > 当 2 列应用程序脚本中的值匹配时删除行

google-apps-script - 当 2 列应用程序脚本中的值匹配时删除行

问题描述

我试图弄清楚为什么我的函数在遍历循环时不会增加到列值范围内的下一个值。我将分享以供参考（下）。该函数旨在传递工作表和搜索键以在暂存和生产工作表中的两个列中查找值，并返回与两个工作表中的搜索键匹配的 rowNum。然后删除相应的行。请查看日志和执行记录（以下代码）

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.");
  }
}

执行成绩单：

[18-11-07 11:17:00:183 PST] SpreadsheetApp.getActiveRange() [0 秒]

[18-11-07 11:17:00:183 PST] Range.getRow() [0 秒]

[18-11-07 11:17:00:183 PST] Range.getLastRow() [0 秒]

[18-11-07 11:17:00:183 PST] Range.getColumn() [0 秒]

[18-11-07 11:17:00:183 PST] Range.getLastColumn() [0 秒]

[18-11-07 11:17:00:183 PST] SpreadsheetApp.getActiveSpreadsheet() [0 秒]

[18-11-07 11:17:00:190 PST] 开始执行

[18-11-07 11:17:00:194 PST] Logger.log（[成功！命名值站点管理员选项：删除站点管理员，[]]）[0 秒]

[18-11-07 11:17:00:195 PST] SpreadsheetApp.getActiveSpreadsheet() [0 秒]

[18-11-07 11:17:00:304 PST] Spreadsheet.getSheetByName([Admin Data]) [0.109 秒]

[18-11-07 11:17:00:305 PST] SpreadsheetApp.getActiveSpreadsheet() [0 秒]

[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 test@test.com , []]) [0 秒]

[18-11-07 11:17:00:306 PST] Sheet.getName() [0 秒]

[18-11-07 11:17:00:306 PST] Sheet.getName() [0 秒]

[18-11-07 11:17:00:306 PST] Sheet.getName() [0 秒]

[18-11-07 11:17:00:306 PST] Sheet.getRange([C:C]) [0 秒]

[18-11-07 11:17:00:307 PST] Sheet.getRange([I:I]) [0 秒]

[18-11-07 11:17:00:562 PST] Range.getValues() [0.255 秒]

[18-11-07 11:17:00:740 PST] Range.getValues() [0.176 秒]

[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（[未找到匹配的 SiteID 和管理员电子邮件，[]]）[0 秒]

[18-11-07 11:17:00:742 PST] Sheet.getName() [0 秒]

[18-11-07 11:17:00:742 PST] Sheet.getName() [0 秒]

[18-11-07 11:17:00:742 PST] Sheet.getName() [0 秒]

[18-11-07 11:17:00:742 PST] Sheet.getRange([C:C]) [0 秒]

[18-11-07 11:17:00:743 PST] Sheet.getRange([I:I]) [0 秒]

[18-11-07 11:17:01:001 PST] Range.getValues() [0.257 秒]

[18-11-07 11:17:01:002 PST] Range.getValues() [0 秒]

[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（[未找到匹配的 SiteID 和管理员电子邮件，[]]）[0 秒]

[18-11-07 11:17:01:004 PST] Sheet.getName() [0 秒]

[18-11-07 11:17:01:004 PST] Sheet.getName() [0 秒]

[18-11-07 11:17:01:004 PST] Sheet.getName() [0 秒]

[18-11-07 11:17:01:005 PST] Sheet.getName() [0 秒]

[18-11-07 11:17:01:005 PST] Sheet.getRange([C:C]) [0 秒]

[18-11-07 11:17:01:006 PST] Sheet.getRange([I:I]) [0 秒]

[18-11-07 11:17:01:255 PST] Range.getValues() [0.248 秒]

[18-11-07 11:17:01:556 PST] Range.getValues() [0.3 秒]

[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（[未找到匹配的 SiteID 和管理员电子邮件，[]]）[0 秒]

[18-11-07 11:17:01:558 PST] Sheet.getName() [0 秒]

[18-11-07 11:17:01:558 PST] Sheet.getName() [0 秒]

[18-11-07 11:17:01:558 PST] Sheet.getName() [0 秒]

[18-11-07 11:17:01:558 PST] Sheet.getName() [0 秒]

[18-11-07 11:17:01:558 PST] Sheet.getRange([C:C]) [0 秒]

[18-11-07 11:17:01:559 PST] Sheet.getRange([I:I]) [0 秒]

[18-11-07 11:17:01:817 PST] Range.getValues() [0.257 秒]

[18-11-07 11:17:02:035 PST] Range.getValues() [0.217 秒]

[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（[未找到匹配的 SiteID 和管理员电子邮件，[]]）[0 秒]

[18-11-07 11:17:02:037 PST] Sheet.getName() [0 秒]

[18-11-07 11:17:02:037 PST] Sheet.getName() [0 秒]

[18-11-07 11:17:02:037 PST] Sheet.getName() [0 秒]

[18-11-07 11:17:02:038 PST] Sheet.getRange([C:C]) [0 秒]

[18-11-07 11:17:02:038 PST] Sheet.getRange([I:I]) [0 秒]

[18-11-07 11:17:02:316 PST] Range.getValues() [0.278 秒]

[18-11-07 11:17:02:318 PST] Range.getValues() [0 秒]

[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（[未找到匹配的 SiteID 和管理员电子邮件，[]]）[0 秒]

[18-11-07 11:17:02:324 PST] 执行失败：找不到方法 deleteRow((class))。（第 113 行，文件“宏”）[2.127 秒总运行时间]

标签： google-apps-scriptgoogle-sheets-api

解决方案

代替

var valuesID = [columnID.getValues()],
valuesEmail = [columnEmail.getValues()];

经过

var valuesID = columnID.getValues(),
valuesEmail = columnEmail.getValues();

在这里，您试图增加一个 3D 数组，就像它是一个 2D 数组一样。getValues已经返回一个二维数组。

参考

获取值（）

推荐阅读