时间戳

首页 > 解决方案 > 谷歌表格：从多列中删除数据

google-apps-script - 谷歌表格：从多列中删除数据

问题描述

我一直在尝试通过从不同列中删除特定值来清理文件。我从一列中执行此操作没有问题，但是如果我使用不同的列添加相同的脚本并运行这两个脚本，则不会正确删除值。

我对此很陌生，所以我的脚本可能是错误的。有一个更好的方法吗？还是我犯了一个错误？

    function clean() {

  var startTime = new Date().getTime();
  var deleteSelectedRows = removeThenSetNewVals();
  var runTime = (new Date().getTime() - startTime) / 1000;
  Logger.log("Runtime is: " + runTime + " seconds");
};

function removeThenSetNewVals(){
  
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
var range = sheet.getDataRange();
var pattern = new RegExp("value1|value2");
var columnToSearch = 5;
var newRangeVals = range.getValues().filter(r => r[0] && !pattern.exec(r[columnToSearch]))  
range.clearContent();
var numRows = newRangeVals.length;
var newRange = sheet.getRange(1,1, numRows, newRangeVals[0].length).setValues(newRangeVals);
var maxRows = sheet.getMaxRows();
sheet.deleteRows(numRows + 1, maxRows - numRows);
};

function clean2() {

  var startTime = new Date().getTime();
  var deleteSelectedRows = removeThenSetNewVals2();
  var runTime = (new Date().getTime() - startTime) / 1000;
  Logger.log("Runtime is: " + runTime + " seconds");
};

function removeThenSetNewVals2(){
  
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
var range = sheet.getDataRange();
var pattern = new RegExp("Value1|Value3");
var columnToSearch = 2;
var newRangeVals = range.getValues().filter(r => r[0] && !pattern.exec(r[columnToSearch]))  
range.clearContent();
var numRows = newRangeVals.length;
var newRange = sheet.getRange(1,1, numRows, newRangeVals[0].length).setValues(newRangeVals);
var maxRows = sheet.getMaxRows();
sheet.deleteRows(numRows + 1, maxRows - numRows);
};

标签： google-apps-scriptgoogle-sheets

解决方案

您可以用一种.filter()方法完成所有检查

function removeThenSetNewVals(){  
  let sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1'),
      range = sheet.getDataRange(),
      maxRows = sheet.getMaxRows(),
      srchCol_1 = 2,
      srchPatt_1 = new RegExp("Value1|Value2"),
      srchCol_2 = 5,
      srchPatt_2 = new RegExp("Value1|Value3"),      
      newRangeVals = range.getValues().filter(r => r[0] && !srchPatt_1.exec(r[srchCol_1]) && !srchPatt_2.exec(r[srchCol_2])),
      numRows = newRangeVals.length;  
  range.clearContent();
  sheet.getRange(1,1, numRows, newRangeVals[0].length).setValues(newRangeVals);
  sheet.deleteRows(numRows + 1, maxRows - numRows);
}

替换pattern.exec(string)为pattern.test(string)?

推荐阅读