How To Automate Combining Orders Data From 3 Worksheets Into A Single Google Worksheet & Sync Over New Records

Question

Combine Data from Worksheets Into A Master Orders Sheet

Hi all, We use a Google Sheet to track our online orders, storing the orders from each marketplace in their own sheet within the same workbook. Currently, we have 3 worksheets: ebay, etsy and amazon. There are external scripts that run throughout the day inserting records into each respective worksheet as the orders come in.

We manually transfer the the order records into a single master worksheet called "GI Orders". From there, we have an internal workflow that occurs in which We setup "jobs" for each order, record the job number(s) for a given order in the spreadsheet, and mark it as done, once processed.

These manual inputs are ONY done on the master GI Orders worksheet, per record, and are recorded in columns A:C, everything from Col D is just data.

Copying records easy because all the sheets have identical columns. The difficulty is in figuring out which orders were already copied over to the master vs which ones are new (inserted after the last copy/paste operation). I also do not know how to programmatically store/handle which records were already copied, and thus can be skipped over during the next script execution.

Our Goal

• Automate the manual transfer of data from the 3 data feed worksheets into the master GI Orders worksheet.
• Repeat the process automatically whenever new rows are added into any of the feed sheets, or copy over new records (delta) at regular intervals (e.g., hourly), adding them to the bottom of sheet so as to not overwrite any existing records.

Many, many thanks in advance for all your assistance!

Link To Sheet

Answer 1

You have 3 external sheets containing order information. The data from the external sheets is imported into three separate sheets in a master spreadsheet.

As new orders are imported, you want to copy the data from each of the three "import" sheets and combine it on a fourth sheet "Gi Orders". However, you need to ensure that you don't create duplicate entries on "GI Orders".

A duplicate transaction can be identified if the combination of the "Order Number" and "SKU" are found to be duplicated on "GI Orders".

---

function updateOrders(){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  // create an array of the sheet names in this spreadsheet
  var source = ["ebay","etsy","amazon"]

  // get the GI Orders data
  var giOrders = ss.getSheetByName("GI Orders")
  var giOrdersData = giOrders.getDataRange().getValues()
  
  // get a 1D array of the order nuimbers and remove the header row
  var giorders = giOrdersData.map(function(e){return e[1]})
  giorders.splice(0,1);

  // get a 1D array of the SKU and remove the header row
  var gisku = giOrdersData.map(function(e){return e[24]})
  gisku.splice(0,1);

  // create an array to hold the combined order#/SKU
  var giArray = new Array
  for (var i=0;i<giorders.length;i++){
    var gikey =giorders[i]+gisku[i]
    // Logger.log("DDEBUG: source = GI Orders , i="+i+", order = "+orders[i]+", sku = "+sku[i]+", key = "+key)
    giArray.push(gikey)
  }

  // get the transaction data
  // create an array to hold any non-duplicate transactions
  var cleanTrans = new Array

  // loop through the sheets
  for (var s = 0;s<source.length;s++){
    var sourceSheet = ss.getSheetByName(source[s])

    // get transaction data
    var transData = sourceSheet.getDataRange().getValues()

    // get a 1D array of Orders and remove the header row
    var orders = transData.map(function(e){return e[0]})
    orders.splice(0,1);

    // get a 1D array of SKU and remove the header row
    var sku = transData.map(function(e){return e[23]})
    sku.splice(0,1);

    // loop through the order/sku  
    for (var i=0;i<orders.length;i++){
      // combine the order and sku
      var key = orders[i]+sku[i]
      // Logger.log("DEBUG: source = "+source[s]+", i="+i+", order = "+orders[i]+", sku = "+sku[i]+", key = "+key)

      // is the combined order#/sku in GI Orders?
      // indexOf returns -1 if there is no match
      var idx = giArray.indexOf(key);

      if (idx == -1){
        // no match found, so add to gi Orders
        // Logger.log("DEBUG: no match "+key+" result = "+idx)
        // add the source to the array
        transData[(i+1)].unshift(source[s])
        cleanTrans.push(transData[(i+1)])
      }
      else{
        // do nothing
        // Logger.log("DEBUG: match found:  "+key+" result = "+idx)
      }
    }
    // Logger.log(cleanTrans) // DEBUG
    // Logger.log("DEBUG: source = "+source[s]+", numbers of transactions = "+ cleanTrans.length) 
  } // end of transactions loop

  // if any new Trans, then append to giOrders
  if (cleanTrans.length >0){
    var giLR = giOrders.getLastRow()
    giOrders.getRange(giLR+1,1,cleanTrans.length,25).setValues(cleanTrans)
  }
}

---

• map
• splice
• push
• indexOf

---

Sample GI Orders - BEFORE consolidation

Sample GI Orders - AFTER consolidation

Sample Imported Orders - eBay

Sample Imported Orders - etsy

Sample Imported Orders - amazon