Inventory Line Items to update a Master Inventory Sheet

[ak_trading]

Hi There,

Given a user inserts into table STOCKING with a form. How can I

1. if SKU/Location doesn’t exist: insert into table INVENTORY
2. if SKU/Location exists: update table INVENTORY CURRENT_QTY and LAST_STOCKING_ID

Table: STOCKING

|STOCKING_ID |STOCK_DATE_TIME   |SKU       |LOCATION_ID| STOCK_QTY| USER|
|PfclXrwJ    |4/21/2019 14:01:01|agt48bw   |22         | 4        |  a@a |

Table: INVENTORY

|SKU|LOCATION_ID|CURRENT_QTY|LAST_STOCKING_ID|
| --- | --- | --- | --- |

Please and thank you!

[ak_trading]

I wasn’t able to come up with a solution using AppSheet but found some examples of Google script that triggers inserts . I used Google script that waits for a change in the initial insert table (STOCKING table). The script checks if the item already exists in INVENTORY table and will then either

1. create a new record (when item didn’t exist in INVENTORY)
2. update existing record (when item exists in INVENTORY)
3. delete record (when sku-location (concat key) quantity is 0)

I left negative numbers a possible state for skus in an INVENTORY table, not ideal, as there is no concept of back ordering. But this way a user would at least know something went wrong when they see an item has a negative inventory and can then examine the transaction records on “STOCKING” table.

// Global variables
var TRIGGER_SHEET = 'STOCKING';
var UPDATE_SHEET = 'INVENTORY';

function replicateRowInsertion(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  if (ss.getSheetName() == TRIGGER_SHEET) {
    var updateSheet = ss.getSheetByName(UPDATE_SHEET);
    var triggerSheet = ss.getSheetByName(TRIGGER_SHEET);
    var targetRow = ss.getActiveCell().getRow();
    var targetSku  = triggerSheet.getRange(targetRow, 3).getValue();
    var targetLocation = triggerSheet.getRange(targetRow, 4).getValue();
    var updateQuantity = triggerSheet.getRange(targetRow, 5).getValue();
    var stockingId = triggerSheet.getRange(targetRow, 1).getValue();

    var allUpdateSkus = updateSheet.getRange(2, 1, updateSheet.getLastRow()).getValues();
    var allLocations =  updateSheet.getRange(2, 2, updateSheet.getLastRow()).getValues();
    
    var found = false;
    
    //first we check if the sku exists in inventory and location
    for (var i = 0; i < allUpdateSkus.length; i++) {
      if (allUpdateSkus[i].toString() == targetSku && allLocations[i].toString() == targetLocation) {
        var range = updateSheet.getRange(i + 2, 3);
        var currentQuantity = range.getValue();
        var newQuantity = currentQuantity + updateQuantity
          
        if (newQuantity === 0) { //delete item if it is zero
            updateSheet.deleteRow(i + 2);
        } else { //otherwise, update quantity and stocking id
          range.setValue(newQuantity);
          updateSheet.getRange(i + 2, 4).setValue(stockingId);
        }
        found = true;
        break;
      }
    }
      
    //if item was not found than we add a new item by inventory location and sku
    if (!found) {  
      updateSheet.appendRow([
        targetSku, 
        targetLocation, 
        updateQuantity,
        stockingId
      ]);
    }
  }
}