时间戳

首页 > 解决方案 > 谷歌脚本在运行后删除列公式

google-apps-script - 谷歌脚本在运行后删除列公式

问题描述

语境

我有一个脚本，我现在找不到它的来源，但我已经根据我的需要对其进行了调整。在谷歌表中，它搜索多个条件（如果一列具有多个值之一：“未收到付款”、“payout_approved”等+状态列中为“PENDING” +特定字符串在另一列中-都在同一行），如果他们相遇，它将行状态更改为“.PAID”（带有下拉列表的列）。我添加了一个触发器，它可以工作。

其他几列在表格标题后的第一行上有公式，似乎在脚本运行后它会删除公式。之后仍然可以看到所有预期值，但是公式停止工作（如果我添加更多行，我可以看到公式停止工作，因为它们是数组公式/vlookup，根据我添加的内容以及如果我去单元格从其他工作表中获取值使用公式只有值在那里，而不是公式）。这是在那个特定时间工作的唯一脚本，我可以检查单元格的更改历史记录，看看它是否与脚本运行的时间相匹配）。

我想要什么

我希望您的帮助：

我有的

function PendingPayment_Status() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetname = "MASTER";
var sheet = ss.getSheetByName(sheetname);

var LR = sheet.getLastRow();
var Columns = 24;
var range = sheet.getDataRange();
var data = range.getValues();

var refStatus = 'PENDING';
var refRequest = 'Payment not received. Please provide payment confirmation.';
var refCPstatus = 'payout_approved';
var refCPstatus2 = 'sent_to_finance';
var refCPstatus3 = 'special';
var refCPstatus4 ='fee_collected';

for (var i=0;i<LR-1;i++){
var cpstate = data[i+1][23];
var state = data[i+1][11];
var request = data[i+1][8];

// update the status to .PAID
if ((cpstate == refCPstatus && state == refStatus && request == refRequest) || (cpstate == refCPstatus2 && state == refStatus && request == refRequest) || (cpstate == refCPstatus3 && state == refStatus && request == refRequest) || (cpstate == refCPstatus4 && state == refStatus && request == refRequest)){
  // request, sheet status and status match the reference data
  data[i+1][11] = ".PAID";
  //Logger.log("DEBUG: Updated sheet status for row#"+(+i+1))
}  
}
 range.setValues(data);
 }

先感谢您。

测试电子表格- 之后添加

标签： google-apps-scriptgoogle-sheets

解决方案

另一种可能：

您可以检索有关范围的信息并使用以下组合创建新范围：

range.getHeight()
range.getWidth()
sheet.getRange()

sheet.getRange()可以使用“A1”表示法，但也可以通过以下方式调用：

sheet.getRange(1,1,5,10)

这得到 range "A1:E10"。IE.getRange(top left row, top left col, num of rows, num of cols)

请参阅下面如何在您的脚本中实现这一点。

我还冒昧地以更简洁的方式编写了您的代码的某些部分，尽管这只是我个人的喜好！:) 例如，我使用该array.includes()方法来缩短您的条件语句。

function PendingPayment_Status() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  
  var LR = sheet.getLastRow();
  var Columns = 5; // changed number of columns to fit my example
  var range = sheet.getDataRange(); // Always gets A1 as top left cell
  var rgHeight = range.getHeight()
  // you can also range.getWidth() if you want

  // Here you specify *2* as the top left row
  range = sheet.getRange(2,1,rgHeight - 1, Columns) // top left row, top left column, num of rows, num of columns
  
  var data = range.getValues();
 
  var refStatus = "PENDING"; // have to use double quotes here
  var refRequest = "Payment not received. Please provide payment confirmation.";
  
  var okCpStatuses = ["payout_approved", "sent_to_finance", "special", "fee_collected'"]
  
  for (var i=0; i<range.getHeight(); i++) {
    var cpstate = data[i][3]; // I changed index to fit my example
    var state = data[i][1]; // I changed index to fit my example
    var request = data[i][2]; // I changed index to fit my example
    
    // update the status to .PAID
    if (
         (state == refStatus && request == refRequest) &&
         ( okCpStatuses.includes(cpstate) )
    ) {
        // request, sheet status and status match the reference data
        data[i][1] = ".PAID"; // I changed the index to fit my example
    }
  }
 range.setValues(data);
  
}

我使用本地工作表对其进行了测试，这就是更改列索引号的原因，但是由于我没有您的工作表，因此无法使用您的工作表进行测试。

也许下次您可以提供自己的示例表，这会更快:)

参考：https ://developers.google.com/apps-script/reference/spreadsheet/range

推荐阅读