在Google表格上的新工作簿中将行数据与importrange数据相关联 [英] Associating row data with importrange data in a new workbook on Google Sheets

问题描述

对于我们的分销公司，我们有一张工作表，其中列出了所有发票(按倒序排列，以将最新发票保持在顶部)，以及我们的发票/会计人员要添加的相关数据.订单日期，公司名称，位置，发票小计，税金，总计，未偿余额，交货状态等.

For our distribution company, we have a sheet that has all of the invoices listed (in reverse chronological order to keep most recent invoices at the top), along with relevant data that our invoicing/accounting person would add. Order date, company name, location, invoice subtotal, taxes, total, outstanding balance, delivery status, etc.

然后我有另一张纸为我们的履行和调度团队提取此数据.它仅过滤到相关列(已删除诸如发票小计/税款，订单日期等之类的东西).我没有按行过滤它，因此它包括了原始工作表中的所有发票.

I then have another sheet that pulls this data for our fulfillment and dispatch team. It filters to only the relevant columns (stuff like invoice subtotal/taxes, order date, etc are removed). I do not have it filter by row, so that way it includes ALL of the invoices from the original sheet.

我想在此电子表格中添加交货日期"列和分配的驱动程序"列，但我已经这样做了，但是就像大多数人试图在此处做类似的事情一样，当添加新发票时，手动-在第二张工作表上输入的数据不会随着导入的数据动态移动，从而导致行未对齐.

I want to include a "delivery date" column and "assigned driver" column in this spreadsheet, which I have done, but like most people trying to do something similar on here, when a new invoice is added, the manually-entered data on this second sheet doesn't dynamically shift with the imported data, thus causing the rows to misalign.

第二张纸的公式为=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列用作驱动程序分配和交货日期的手动输入列.不幸的是，正如我所提到的，行不会粘在一起，所以随着导入列的动态顺序改变，手动列的静态顺序会导致不匹配.

The formula for the second sheet is =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) I then have columns 10/11 as manual entry columns for driver assigning and delivery date. Unfortunately, as I mentioned, the rows don't stick together so as the dynamic order of the imported columns changes, the static order of the manual columns causes a mismatch.

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

Is there a way to make this work? Let's say I have the following invoices, with delivery dates and driver manually entered on this second sheet:

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.现在，实现电子表格将显示:

And then I add a new invoice, INV-006 to the top of the original invoicing spreadsheet. Now the fulfillment spreadsheet will show:

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

推荐答案

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

The script below will do what you need.

当您更改订单时，交货单将正确更新. 它将更新:

When you make a change in your order sheet, the delivery sheet will be updated correctly. It will update:

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

1. When you open the delivery sheet
2. If you press a REFRESH button on the delivery sheet.

(因此不如使用"query(IMPORTRANGE..)"公式时自动).

(So not quite as automatically as when using the "query(IMPORTRANGE..)" formula).

这是您需要安装的代码.

Here is the code that you will need to install.

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
}

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

I have tested this in copies of your test sheets and all seems to work OK. This link is a version of your fulfilment sheet, and has the script and REFRESH button install in Col M: https://docs.google.com/spreadsheets/d/15ecr9CmXn2YyhMpGTg8VCVf8tTi5GaGrjgmQus9FxWA/edit?usp=sharing

向任何Google脚本专家致意:我必须制作原始"query(IMPORTRANGE..)"公式的脚本版本.这是第2步.如果有人认为有更好的方法可以做到这一点，我很想听听.我这样做的原因是由于Google脚本限制(据我了解):

NOTE to any Google script experts: I had to make a script version of the original "query(IMPORTRANGE..)" formula. This is in step 2. If anyone sees a better way to do this I would be interested to hear. The reason I did this was due to Google script restrictions (as I understand):

1. 执行"query(IMPORTRANGE..)"后没有任何事件
2. 如果我将查询安装在脚本中，则无法在脚本中执行查询.

1. There is no event following execution of "query(IMPORTRANGE..)"
2. If I install the query in script, there is no way to execute it in the script.

这篇关于在Google表格上的新工作簿中将行数据与importrange数据相关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！