Synchronize independent spreadsheet rows, filled by IMPORTRANGE()

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