同步独立的电子表格行，由 IMPORTRANGE() 填充 [英] Synchronize independent spreadsheet rows, filled by IMPORTRANGE()

问题描述

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

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

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

它的工作方式是派对 1 在 A1-F10 行写入他们的数据，然后派对 2 根据派对 1 写入的内容在电子表格 2 中写入他们的附加数据.

例如，如果电子表格 1 A1:F10 是商品的名称、价格、预计交货时间、数量等，则电子表格 2 G1:M10 可能是有关订单日期、交付(是/否)等的一堆数据.

我目前遇到的问题是，当设置电子表格时，它们可以很好地读取，即电子表格 1 中的 1-10 与电子表格 2 中的 1-10 对齐，但过了一会儿，一些新行被添加到电子表格 1 之间的旧行中第 2-5 行.这会抛出电子表格 2 中的顺序(现在电子表格 1 中的第 4 行与电子表格 2 中的第 4 行不对齐，数据变得不正常).是否存在这样的情况，以便即使有人在现有行中间添加额外的行，两个电子表格也会更新?

解决方案

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

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

这需要在电子表格 2 中进行小的更改，其中名称列现在将出现在 G 列中，紧跟 A-F 列中的导入范围.

 A B C D E F G H I J|姓名 |价格 |预计交货时间 |数量 |等|一个项目 |姓名 |订购日期 |交付 |等等等等|<- - - - - - - - - - - - 进口 - - - - - - - - - - - >*钥匙* <- - - - - - 第 2 页 - - - - - >

演示

这是实际操作的样子！这个例子在同一个电子表格中使用了两张工作表，只是为了方便.在演示中，在工作表 1 的中间添加了一个新的项目"行，由于 =IMPORTRANGE() 函数，它会自动出现在工作表 2 上.同步功能在 1 分钟定时触发器上运行，您会看到它在大约 20 秒内移动.

您可以在

代码

/*** 使用键列的名称调用syncTables().*/函数 doSyncTables() {同步表(名称")；}/** 将订单"电子表格与从项目"电子表格中导入的行同步.** 来自:http://stackoverflow.com/a/33172975/1677912** @param {String} keyName 列标题用作键列，出现* 在订单"数据的开头，跟随*项目"数据.*/功能同步表(键名){var sheet2 = SpreadsheetApp.openById( sheetId2 ).getSheetByName('Orders');//获取数据var lastCol = sheet2.getLastColumn();var lastRow = sheet2.getLastRow();//包括所有行，甚至是空白行，因为 =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 = [];//按键跟踪订单//扫描 orderData 中的键for (var row=1; row

I need to synchronize the contents of 2 spreadsheets that reference each other, keeping their rows in sync if a new row is added in one of the sheets.

I've got 2 spreadsheets in Google Sheets (although if there is a cross spreadsheet solution, both Excel and GS that would be great):

• Spreadsheet1 has data in A:F and party1 (a set of users) writes their data in it.
• Spreadsheet2 is and import range of A:F from spreadsheet1 and then has further details written in G:M, the data is written in by party2.

The way it works is party1 writes in their data in rows A1-F10 then party2 writes their additional data in spreadsheet2 based on what party1 has written in.

For example if Spreadsheet1 A1:F10 was a name, price, est delivery time, qty etc. of an item, Spreadsheet2 G1:M10 might be a bunch of data on order date, delivered (yes / no) etc.

The issue I'm currently having is that when the spreadsheets are setup they read across fine i.e. 1-10 in spreadsheet1 lines up with 1-10 in spreadsheet2, but after a while some new rows get added into spreadsheet1 between the old rows 2-5. This throws out the order in spreadsheet2 (now row 4 in spreadsheet1 doesn't line up with the row 4 in spreadsheet2 and the data becomes out of line). Is there away around this so that even if someone adds additional rows in the middle of existing rows both spreadsheets will update?

解决方案

This is a classic problem in database design; how to associate information in two tables. The usual solution is to use key data; one or more columns that exist in both tables and provide a unique identifier, or key, to associate rows.

We can adapt that idea to your situation, with a script that will adjust the location of rows in Spreadsheet 2 to synchronize with Spreadsheet 1. To do that, we need to identify a key - say the Name column - which must exist in both spreadsheets.

This entails a small change in spreadsheet 2, where a Name column will now appear in column G, following the imported range in columns 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  - - - - - >

Demo

Here's how that would look in action! This example is using two sheets in the same spreadsheet, just for convenience. In the demo, a new "Item" row is added in the middle of sheet 1, which automatically appears on sheet 2 thanks to the =IMPORTRANGE() function. The synchronizing function is running on a 1-minute timed Trigger, and you'll see it move things around about 20 seconds in.

You can grab a copy of the spreadsheet + embedded script here.

Code

/**
 * 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);
}

这篇关于同步独立的电子表格行，由 IMPORTRANGE() 填充的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！