Associating row data with importrange data in a new workbook on Google Sheets

Question

For our distribution company, we have a sheet that has all of the invoices listed (in reverse chronological order to keep most recent invoices at the top), along with relevant data that our invoicing/accounting person would add. Order date, company name, location, invoice subtotal, taxes, total, outstanding balance, delivery status, etc.

I then have another sheet that pulls this data for our fulfillment and dispatch team. It filters to only the relevant columns (stuff like invoice subtotal/taxes, order date, etc are removed). I do not have it filter by row, so that way it includes ALL of the invoices from the original sheet.

I want to include a "delivery date" column and "assigned driver" column in this spreadsheet, which I have done, but like most people trying to do something similar on here, when a new invoice is added, the manually-entered data on this second sheet doesn't dynamically shift with the imported data, thus causing the rows to misalign.

The formula for the second sheet is =query(IMPORTRANGE("sheet_id","'Order Tracker'!A:T"),"select Col1, Col3, Col5, Col6, Col9, Col10, Col11, Col12, Col19 where Col10 = 'New' OR Col10 = 'Packed' OR Col10 = 'Pending'",1) I then have columns 10/11 as manual entry columns for driver assigning and delivery date. Unfortunately, as I mentioned, the rows don't stick together so as the dynamic order of the imported columns changes, the static order of the manual columns causes a mismatch.

Is there a way to make this work? Let's say I have the following invoices, with delivery dates and driver manually entered on this second sheet:

INV-005 | 10/26 | Frank
INV-004 | 10/27 | Brandon
INV-003 | 10/27 | Frank
INV-002 | 10/26 | Frank
INV-001 | 10/28 | Brandon

And then I add a new invoice, INV-006 to the top of the original invoicing spreadsheet. Now the fulfillment spreadsheet will show:

INV-006 | 10/26 | Frank
INV-005 | 10/27 | Brandon
INV-004 | 10/27 | Frank
INV-003 | 10/26 | Frank
INV-002 | 10/28 | Brandon
INV-001

Instead, I want it to show:

INV-006
INV-005 | 10/26 | Frank
INV-004 | 10/27 | Brandon
INV-003 | 10/27 | Frank
INV-002 | 10/26 | Frank
INV-001 | 10/28 | Brandon

Answer 1

The script below will do what you need.

When you make a change in your order sheet, the delivery sheet will be updated correctly. It will update:

1. When you open the delivery sheet
2. If you press a REFRESH button on the delivery sheet.

(So not quite as automatically as when using the "query(IMPORTRANGE..)" formula).

Here is the code that you will need to install.

function onOpen() {
   updateTracker();
}


function updateTracker(){
  // This function is executed when the sheet is opened
  // and also intended to be linked to a REFRESH button to be installed in the sheet
  // The function populates ColA to ColK with data from another sheet
  // Existing data in ColL and ColM has to be preserved, and realigned 
  // with the same invoice numbers in ColA of the new data.

  // Step 1 - read ColA, ColL and ColM of the old data, before repopulating ColA and ColsK
  //---------------------------------------------------------------------------------------
  //var openSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Open Order Tracker");
  var openSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = openSheet.getLastRow(); // locate last row of data
  var invNoArray = openSheet.getRange(2,1,lastRow-1).getValues();   // 1D array of invoices numbers
  var invNoList = {}; 
  for(var row=0; row < invNoArray.length; row++){  // make an "associative array" of invoice numbers
     invNoList[invNoArray[row]] = row;
  }
  // read the delivery dates and driver from this sheet ('Open Order Tracker')
  var driverArray = openSheet.getRange(2,12,lastRow-1,3).getValues();  // 2D array of Delivery Dates and Drivers
  // clear out the old content (not formats or vaidation)
  var currentRange = openSheet.getRange(2,1,lastRow,14); 
  currentRange.clearContent();    

  // Step 2 - Read and the data for ColA to ColK from the source sheet
  //------------------------------------------------------------------
  // Simulating this formula
  //=query(IMPORTRANGE("1rm31Zza8fMS2pASIuFvQ0WBBqWb-174lD5VrtAixDjg","'Order Tracker'!A:T"),"select Col1, Col2, Col3, Col5, Col9, Col10, Col11, Col12, Col13, Col14, 
  // Col19 where Col10 = 'New' OR Col10 = 'Packed' OR Col10 = 'Pending' OR(Col10 = 'Delivered' AND Col14 > 0.01)",1)

  var sourceSheet = SpreadsheetApp.openById('1LU-dSlGqyiKj6xjo5AVvNNdf1pBR26NTuaXZBdLK2Og').getSheetByName("Order Tracker");
  var dataRange = sourceSheet.getDataRange();
  var dataValues = dataRange.getValues().filter(function (x) {return x[9]=='New' || x[9] =='Packed' || x[9] == 'Pending' || (x[9] == 'Delivered' && x[13] >=0.01);});

  // Remove columns we dont need.
  var reqValues = [];
  var reqCols=[0,1,2,4,8,9,10,11,12,13,18];    // corresponding to Col1, Col2 etc
  for(var row=0; row<dataValues.length; row++){
    var thisRow = [];
    for (var col=0; col<reqCols.length; col++){
      thisRow.push(dataValues[row][reqCols[col]]);
    }
    // Add placeholders cols for ColL and ColM
    thisRow.push("None");
    thisRow.push("None");
    thisRow.push("None");   // to be removed later
    reqValues.push(thisRow);
  }

  // Step 3 - Populate ColL and ColM - re-aligning the Invoice Numbers
  //------------------------------------------------------------------
  for (var row=0; row < reqValues.length; row++){
    if (invNoList.hasOwnProperty(reqValues[row][0])){
       var invNoIndex= invNoList[reqValues[row][0]];      // locate correct data based on invoice number
       reqValues[row][11] = driverArray[invNoIndex][0];   // fill in Delivery Date
       reqValues[row][12] = driverArray[invNoIndex][1];   // fill in the Driver
       // below line to be removed later
       reqValues[row][13] = driverArray[invNoIndex][2];   // fill in the CrossCheck data
    }  
  }

  //Step 4 -  Copy the reqValues
  //-----------------------------------------------
  var finalRange = openSheet.getRange(2,1,reqValues.length,14);   // openSheet and lastRow should be still valid
  finalRange.setValues(reqValues);

  //Done
}

I have tested this in copies of your test sheets and all seems to work OK. This link is a version of your fulfilment sheet, and has the script and REFRESH button install in Col M: https://docs.google.com/spreadsheets/d/15ecr9CmXn2YyhMpGTg8VCVf8tTi5GaGrjgmQus9FxWA/edit?usp=sharing

NOTE to any Google script experts: I had to make a script version of the original "query(IMPORTRANGE..)" formula. This is in step 2. If anyone sees a better way to do this I would be interested to hear. The reason I did this was due to Google script restrictions (as I understand):

1. There is no event following execution of "query(IMPORTRANGE..)"
2. If I install the query in script, there is no way to execute it in the script.