javascript - 同步独立的电子表格行，由 IMPORTRANGE() 填充

Question

我需要同步 2 个相互引用的电子表格的内容，如果在其中一个工作表中添加新行，则保持它们的行同步。

我在 Google 表格中有 2 个电子表格(尽管如果有交叉电子表格解决方案，Excel 和 GS 都很棒):

• Spreadsheet1 在 A:F 中有数据，party1(一组用户)将他们的数据写入其中。
• Spreadsheet2 是从 spreadsheet1 导入 A:F 的范围，然后在 G:M 中写入更多详细信息，数据由 party2 写入。

它的工作方式是 party1 将他们的数据写入 A1-F10 行，然后 party2 根据 party1 写入的内容将他们的附加数据写入电子表格 2。

例如，如果 Spreadsheet1 A1:F10 是商品的名称、价格、预计交货时间、数量等，Spreadsheet2 G1:M10 可能是订单日期、交货(是/否)等一堆数据

我目前遇到的问题是，当设置电子表格时，它们可以很好地读取，即电子表格 1 中的 1-10 与电子表格 2 中的 1-10 对齐，但过了一段时间后，一些新行被添加到电子表格 1 中的旧行之间第 2-5 行。这会打乱 spreadsheet2 中的顺序(现在 spreadsheet1 中的第 4 行与 spreadsheet2 中的第 4 行不对齐，数据变得不一致)。有没有解决这个问题的办法，即使有人在现有行的中间添加了额外的行，两个电子表格也会更新？

Answer 1

这是数据库设计中的经典问题；如何关联两个表中的信息。通常的解决方案是使用关键数据；一个或多个列存在于两个表中，并提供唯一标识符或键来关联行。

我们可以根据您的情况调整该想法，使用一个脚本来调整电子表格 2 中行的位置以与电子表格 1 同步。为此，我们需要确定一个键(例如名称列)，该键必须存在在两个电子表格中。

这需要对电子表格 2 进行小的更改，名称列现在将显示在 G 列中，紧随 A-F 列中的导入范围。

    A      B             C            D       E         F        G         H           I           J
| Name | Price | est delivery time | qty | etc. of | an item | Name  | order date | delivered | blah blah |
 < - - - - - - - - - - - -  Imported  - - - - - - - - - - - >  *KEY*  < - - - - - -  sheet 2  - - - - - >

演示

下面是它的实际效果!为方便起见，此示例在同一个电子表格中使用了两个工作表。在演示中，在工作表 1 的中间添加了一个新的“项目”行，由于 =IMPORTRANGE() 函数，该行自动出现在工作表 2 上。同步功能在 1 分钟的定时触发器上运行，您会看到它在大约 20 秒内移动了一些东西。

您可以获取一份电子表格 + 嵌入式脚本 here .

代码

/**
 * Call syncTables() with the name of a key column.
 */
function doSyncTables() {
  syncTables( "Name" );
}

/*
 * Sync "Orders" spreadsheet with imported rows from "Items" spreadsheet.
 *
 * From: http://stackoverflow.com/a/33172975/1677912
 *
 * @param {String}  keyName    Column header used as key colum, appears
 *                             at start of "Orders" data, following
 *                             "Items" data.
 */
function syncTables( keyName ) {
  var sheet2 = SpreadsheetApp.openById( sheetId2 ).getSheetByName('Orders');

  // Get data
  var lastCol = sheet2.getLastColumn();
  var lastRow = sheet2.getLastRow();      // Includes all rows, even blank, because of =importRange()
  var headers = sheet2.getRange(1, 1, 1, lastCol).getValues()[0];
  var keyCol = headers.lastIndexOf( keyName ) + 1;
  var itemKeys = sheet2.getSheetValues(1, 1, lastRow, 1).map(function(row) {return row[0]});
  var itemData = sheet2.getSheetValues(1, 1, lastRow, keyCol-1);
  var orderData = sheet2.getSheetValues(1, keyCol, lastRow, lastCol-keyCol+1);

  var ordersByKey = [];  // To keep track of orders by key

  // Scan keys in orderData
  for (var row=1; row<orderData.length; row++) {
    // break loop if we've run out of data.
    var orderKey = orderData[row][0];
    if (orderKey === '') break;

    ordersByKey[ orderKey ] = orderData.slice(row, row+1)[0];

    var orderKey = orderData[row][0];
  }

  var newOrderData = [];  // To store reordered rows

  // Reconcile with Items, fill out array of matching orders
  for (row = 1; row<itemData.length; row++) {
    // break loop if we've run out of data.
    var itemKey = itemData[row][0];
    if (itemKey === '') break;

    // With each item row, match existing order data, or add new
    if (ordersByKey.hasOwnProperty(itemKey)) {
      // There is a matching order row for this item
      newOrderData.push(ordersByKey[itemKey]);
    }
    else {
      // This is a new item, create a new order row with same key
      var newRow = [itemKey];
      // Pad out all columns for the new row
      for (var col=1; col<orderData[0].length; col++) newRow.push('');
      newOrderData.push(newRow);
    }
  }

  // Update spreadsheet with reorganized order data
  sheet2.getRange(2, keyCol, newOrderData.length, newOrderData[0].length).setValues(newOrderData);
}