Error with synchronizing independent sheets using IMPORTRANGE

Question

I have two sheets; one is a master sheet that is edited by a number of people, the other is a copy of the master using IMPORTRANGE where my team makes notes in a column next to the IMPORTRANGE data. This suffers from the age old problem that when someone deletes or inserts a row on the master data, the data on the second sheet becomes misaligned.

I have a master sheet called Test Main Data and a test sheet called Test Data Script.

In reference to Synchronize independent spreadsheet rows, filled by IMPORTRANGE() asked by @sam, I have used the code in the answer provided by @Mogsdad to link my own sheets together. However, I am not really versed in JavaScript (or any language for that matter, I only ever took introduction to programming) so I'm not really sure how to get this to work for my sheets.

var sheetId2 = '1FeRbdGFoPL4MdJix9AvW50i4oEMqAuVxCewJLykxI-g';
/**
 * 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('Notes');

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

The problem I have been experiencing is that, when the script is run, data will be written in column C and cause the IMPORTRANGE function to crash. Ideally, I would like to put my notes in column A in Test Data Script, before the import range data and for that column to synchronise with header "Name" in Test Main Data.

ziganotschka ziganotschka · Accepted Answer · 2019-07-15T11:19:31

If you open the Spreadsheet Mogsdad provided in his answer to Synchronize independent spreadsheet rows, filled by IMPORTRANGE() , you will see that he pasted in his cell "A1" an IMPORTRANGE function which imports the updated data from the sheet "Items" to the sheet "Orders":

This is necessary to update the sheet with new entries.

You can use it the same way, adapting the SpreadsheetId, sheet name and range, just that in your case the formula should be copied in cell C1 - given that you want to reserve columns A and B for your notes.

You can embed the formula also within your Apps Script code:

sheet2.getRange("C1").setValue("PASTE_YOUR_FORMULA_HERE");

Error with synchronizing independent sheets using IMPORTRANGE

1 Answers