google-sheets - 在 Google 表格的新工作簿中将行数据与 importrange 数据相关联

Question

对于我们的分销公司，我们有一张表，其中列出了所有发票（按时间倒序，以将最近的发票保留在顶部），以及我们的发票/会计人员将添加的相关数据。订单日期、公司名称、地点、发票小计、税金、总计、未结余额、交货状态等。

然后，我有另一张表为我们的履行和调度团队提取这些数据。它仅过滤到相关列（删除发票小计/税、订单日期等内容）。我没有按行过滤，因此它包括原始表格中的所有发票。

我想在这个电子表格中包含一个“交货日期”列和“分配的司机”列，我已经这样做了，但是就像大多数人试图在这里做类似的事情一样，当添加新发票时，手动输入的数据第二张工作表不会随导入的数据动态移动，从而导致行错位。

第二张表的公式是=query(IMPORTRANGE("sheet_id","'Order Tracker'!A:T"),"select Col1, Col3, Col5, Col6, Col9, Col10, Col11, Col12, Col19 where Col10 = 'New' OR Col10 = 'Packed' OR Col10 = 'Pending'",1)我将列 10/11 作为驱动程序分配和交货日期的手动输入列。不幸的是，正如我所提到的，行不会粘在一起，因此随着导入列的动态顺序发生变化，手动列的静态顺序会导致不匹配。

有没有办法使这项工作？假设我有以下发票，在第二张表上手动输入了交货日期和驱动程序：

INV-005 | 10/26 | Frank
INV-004 | 10/27 | Brandon
INV-003 | 10/27 | Frank
INV-002 | 10/26 | Frank
INV-001 | 10/28 | Brandon

然后我将新发票 INV-006 添加到原始发票电子表格的顶部。现在履行电子表格将显示：

INV-006 | 10/26 | Frank
INV-005 | 10/27 | Brandon
INV-004 | 10/27 | Frank
INV-003 | 10/26 | Frank
INV-002 | 10/28 | Brandon
INV-001

相反，我希望它显示：

INV-006
INV-005 | 10/26 | Frank
INV-004 | 10/27 | Brandon
INV-003 | 10/27 | Frank
INV-002 | 10/26 | Frank
INV-001 | 10/28 | Brandon

标签： google-sheetsgoogle-sheets-queryimportrange

Answer 1

下面的脚本将满足您的需求。

当您在订单表中进行更改时，交货表将正确更新。它将更新：

1. 打开交货单时
2. 如果您按下REFRESH交货单上的按钮。

（所以不像使用“ query(IMPORTRANGE..)”公式那样自动）。

这是您需要安装的代码。

function onOpen() {
   updateTracker();
}


function updateTracker(){
  // This function is executed when the sheet is opened
  // and also intended to be linked to a REFRESH button to be installed in the sheet
  // The function populates ColA to ColK with data from another sheet
  // Existing data in ColL and ColM has to be preserved, and realigned 
  // with the same invoice numbers in ColA of the new data.

  // Step 1 - read ColA, ColL and ColM of the old data, before repopulating ColA and ColsK
  //---------------------------------------------------------------------------------------
  //var openSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Open Order Tracker");
  var openSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = openSheet.getLastRow(); // locate last row of data
  var invNoArray = openSheet.getRange(2,1,lastRow-1).getValues();   // 1D array of invoices numbers
  var invNoList = {}; 
  for(var row=0; row < invNoArray.length; row++){  // make an "associative array" of invoice numbers
     invNoList[invNoArray[row]] = row;
  }
  // read the delivery dates and driver from this sheet ('Open Order Tracker')
  var driverArray = openSheet.getRange(2,12,lastRow-1,3).getValues();  // 2D array of Delivery Dates and Drivers
  // clear out the old content (not formats or vaidation)
  var currentRange = openSheet.getRange(2,1,lastRow,14); 
  currentRange.clearContent();    

  // Step 2 - Read and the data for ColA to ColK from the source sheet
  //------------------------------------------------------------------
  // Simulating this formula
  //=query(IMPORTRANGE("1rm31Zza8fMS2pASIuFvQ0WBBqWb-174lD5VrtAixDjg","'Order Tracker'!A:T"),"select Col1, Col2, Col3, Col5, Col9, Col10, Col11, Col12, Col13, Col14, 
  // Col19 where Col10 = 'New' OR Col10 = 'Packed' OR Col10 = 'Pending' OR(Col10 = 'Delivered' AND Col14 > 0.01)",1)

  var sourceSheet = SpreadsheetApp.openById('1LU-dSlGqyiKj6xjo5AVvNNdf1pBR26NTuaXZBdLK2Og').getSheetByName("Order Tracker");
  var dataRange = sourceSheet.getDataRange();
  var dataValues = dataRange.getValues().filter(function (x) {return x[9]=='New' || x[9] =='Packed' || x[9] == 'Pending' || (x[9] == 'Delivered' && x[13] >=0.01);});

  // Remove columns we dont need.
  var reqValues = [];
  var reqCols=[0,1,2,4,8,9,10,11,12,13,18];    // corresponding to Col1, Col2 etc
  for(var row=0; row<dataValues.length; row++){
    var thisRow = [];
    for (var col=0; col<reqCols.length; col++){
      thisRow.push(dataValues[row][reqCols[col]]);
    }
    // Add placeholders cols for ColL and ColM
    thisRow.push("None");
    thisRow.push("None");
    thisRow.push("None");   // to be removed later
    reqValues.push(thisRow);
  }

  // Step 3 - Populate ColL and ColM - re-aligning the Invoice Numbers
  //------------------------------------------------------------------
  for (var row=0; row < reqValues.length; row++){
    if (invNoList.hasOwnProperty(reqValues[row][0])){
       var invNoIndex= invNoList[reqValues[row][0]];      // locate correct data based on invoice number
       reqValues[row][11] = driverArray[invNoIndex][0];   // fill in Delivery Date
       reqValues[row][12] = driverArray[invNoIndex][1];   // fill in the Driver
       // below line to be removed later
       reqValues[row][13] = driverArray[invNoIndex][2];   // fill in the CrossCheck data
    }  
  }

  //Step 4 -  Copy the reqValues
  //-----------------------------------------------
  var finalRange = openSheet.getRange(2,1,reqValues.length,14);   // openSheet and lastRow should be still valid
  finalRange.setValues(reqValues);

  //Done
}

我已经在您的测试表副本中对此进行了测试，一切似乎都正常。此链接是您的履行单的一个版本，并在 Col M 中安装了脚本和刷新按钮：https ://docs.google.com/spreadsheets/d/15ecr9CmXn2YyhMpGTg8VCVf8tTi5GaGrjgmQus9FxWA/edit?usp=sharing

任何 Google 脚本专家的注意事项：我必须制作原始“ query(IMPORTRANGE..)”公式的脚本版本。这是第 2 步。如果有人看到更好的方法来做到这一点，我很想听听。我这样做的原因是由于谷歌脚本限制（据我了解）：

1. query(IMPORTRANGE..)执行“ ”后没有事件
2. 如果我在脚本中安装查询，则无法在脚本中执行它。