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

问题描述

我需要同步2张相互参照的电子表格的内容，并在其中一张表格中添加新行时保持行的同步。

I已经在Google表格中获得了2个电子表格（尽管如果有交叉电子表格解决方案，Excel和GS都会很棒）： Spreadsheet1在A：F中有数据，而party1（一组用户）在其中写入数据。

Spreadsheet2是电子表格1中的A：F的输入范围，然后用G：M写入更多详细信息，数据由party2写入。
$ b

它的工作方式是将party1的数据写入行A1-F10，然后party2将其附加数据写入电子表格2，例如，如果Spreadsheet1 A1：F10是一个项目的名称，价格，交货期，数量等，Spreadsheet2 G1：M10可能是一个一堆数据在订单日期，交付（是/否）等。

我目前遇到的问题是，当电子表格设置他们阅读罚款即1电子表格1中的-10与电子表格2中的1-10一致，但过了一段时间后，一些新行将被添加到旧电子表格2-5之间的电子表格1中。这将在电子表格2中抛出订单（现在，电子表格1中的第4行不与电子表格2中的第4行对齐，并且数据不成行）。是否存在这样的问题，即使有人在现有行的中间添加了额外的行，这两个电子表格都会更新？

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

我们可以根据您的情况使用该脚本将调整Spreadsheet 2中的行的位置以与电子表格1同步。为此，我们需要确定一个关键字 - 比如名称列 - 必须存在于两个电子表格中。

这需要在电子表格2中进行一些小的更改，其中名称列现在将出现在列G中，在AF列中导入的范围之后。

  ABCDEFGHIJ 
 |名称|价格|预计交货时间| qty | | |项目|名称|订单日期|交付|等等等等
< -   -   -   -   -   -   -   -   -   -   -   - 导入 -   -   -   -   -   -   -   -   -   -   - > * KEY *< -   -   -   -   -   - 表2  -   -   -   -   - > 
  

Demo

在行动！为了方便起见，此示例在同一电子表格中使用两张表。在演示中，在表单1的中间添加了一个新的Item行，这自动显示在表单2上，这要归功于 = IMPORTRANGE（）函数。同步功能在1分钟的定时触发器上运行，你会看到它在20秒左右的时间内移动。

您可以获取电子表格的副本+嵌入式脚本

代码

/ **
*使用键列的名称调用syncTables（）。
* /
函数doSyncTables（）{
syncTables（Name）;
}

/ *
*将订单电子表格与项目电子表格中导入的行同步。
*
*来自：http://stackoverflow.com/a/33172975/1677912
*
* @param {String} keyName用作键列的列标题，显示
*开始订单数据，在
*Items数据之后。
* /
函数syncTables（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中的键
（var row = 1; row< orderData.length; row ++）{
// break如果我们的数据用完了，则循环。
var orderKey = orderData [row] [0];
if（orderKey ===''）break;

ordersByKey [orderKey] = orderData.slice（row，row + 1）[0];

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

var newOrderData = []; //存储重新排序的行

//与Items同步，填写匹配订单数组
（row = 1; row< itemData.length; row ++）{
/如果我们的数据用完了/ break循环。
var itemKey = itemData [row] [0];
if（itemKey ===''）break;

//对于每个商品行，匹配现有的订单数据，或者添加新的
if（ordersByKey.hasOwnProperty（itemKey））{
//有匹配的订单行此项目
newOrderData.push（ordersByKey [itemKey]）;
}
else {
//这是一个新项目，用相同的键
创建一个新的订单行var newRow = [itemKey];
//填充新行的所有列
（var col = 1; col< orderData [0] .length; col ++）newRow.push（''）;
newOrderData.push（newRow）;
}
}

//用更新的订单数据更新电子表格
sheet2.getRange（2，keyCol，newOrderData.length，newOrderData [0] .length）。 setValues方法（newOrderData）;
}


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屋！