How to shift down cells without removing formula when meeting conditions

Question

I'm a very green developer trying to make an inventory transaction program in google sheets w/ google app script. I work in the warehouse and am trying to make my job and the operations run better than the current system (physical counts every month).

There are 3 cells for manual input (SKU, Type/ Reason, Quantity), with a 4th cell auto-generating a time stamp when all 3 cells are filled in.

Once all 4 cells are filled in, I want the program to search for the proper SKU (all are uniquely #'d) then to update the quantity by + or -.

I'm looking at reducing input error on the +-QTY as well, so maybe looking at having the Reason determine if it is +/-.

eg. Type = S = Sold = - eg. Type = N = New Units = +

Inventory is arranged on a google sheet representing the physical location of the SKU's on the racking/ shelving.

I want the program to also shift the row down 1 row to leave an inventory transaction history.

enter code herefunction onEdit(e) { var activeSheet = e.source.getActiveSheet(); var range = e.range;

range = (obj1,obj2,obj3,obj4)
range[0] = obj1
range[0].getvalue = obj1.getvalue
range[1] = obj2
range[1].getvalue = obj2.getvalue
range[2] = obj3
range[2].getvalue = obj3.getvalue
range[3] = obj3
range[3].getvalue = obj4.getvalue

var var1=range[0].getvalue()
var var2=range[1].getvalue()
var var3=range[2].getvalue()
var var4=range[3].getvalue()

if var1<>"" and var2<>"" and var3<>"" and var4<>"")
var sheet = SpreadsheetApp.getActive()
sheet.getRange("C13:F13").moveto(sheet.getRange("D13:F13")

}

Answer 1

I played around with this a little. I know you were look to shift cells down but I found in easier to shift the SKU,Type,Quantity and TimeStamp to the right to keep a log of transactions on the spreadsheet.

So hopefully this will give you some insight as to how you can do this on your own in your own way.

Here's the code:

It's an onEdit() function (i.e. simple trigger)

function onEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()!='Sheet1')return;
  var rg=sh.getRange(1,2,3,1);
  var vA=rg.getValues().map(function(r){return r[0];});
  var sku=vA[0];
  var type=vA[1];
  var quan=vA[2];
  e.source.toast(sku + ',' + type + ',' + quan);
  SpreadsheetApp.flush();
  if(e.range.columnStart==2 && e.range.rowStart<5 && sku && type && quan) {
    sh.getRange("B4").setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyyMMddHHmm"));
    var sh2=e.source.getSheetByName('Sheet2');
    var rg2=sh2.getDataRange();
    var luA=rg2.getValues();
    for(var i=1;i<luA.length;i++) {
      if(luA[i][0]==sku) {
        if(type=='Sold') {
          //sh.getRange(3,2).setValue(Number(luA[i][2]-quan));
          sh2.getRange(i+1,3).setValue(Number(luA[i][2]-quan));
          e.source.toast('Sold');
        }
        if(type=='New') {
          //sh.getRange(3,2).setValue(Number(luA[i][2]+quan));
          sh2.getRange(i+1,3).setValue(Number(luA[i][2]+quan));
          e.source.toast('New');
        }
        sh.insertColumnAfter(2);  
        sh.getRange(1,3,4,1).setValues(sh.getRange(1,2,4,1).getValues())
        sh.getRange(1,2,4,1).clearContent();
      }
    }
  }
}

And here's what my Sheet1 and Sheet2 look like. Every items in Sheet2 started with 100.

Sheet1:

Sheet2:

In case you've never worked with an onEdit(e) function the e is a parameter that hold data from an event object which gets load when you get an event trigger. So you can't run this function from the script editor. You must copy it and the make the appropriate edits in column 2 by filling in the values for SKU, Type and Quantity.

Hopefully, this example will provide you with some insight as to how you can proceed.