Setting value to the next available cell below

userPZel1 0
Viewed 19

I have this small piece of function that accepts these specific objects (itemNumber, qty, bStaff). When I run the code, the values were written in a specific location which is A13, C13, and F13 at the first time it runs. When I run the code again, how do I write the values directly to the cell below and so on up until a certain cell (limit)?

function submitBtn(itemNumber, qty, bStaff)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var inventorySheet = ss.getSheetByName("Sheet2");
  var drSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

    drSheet.getRange("A13").setValue(qty);
    drSheet.getRange("C13").setValue(itemNumber);
    drSheet.getRange("F13").setValue(bStaff);
  
    
 
 
}

I tried doing for loops, but it only fills all the other cells with the same values. Maybe I was just doing it wrong. What I need is a function that adds an item, creating some kind of list of values every time it runs.

I also tried this but only sets Value for 1 cell and overwrites that cell when function runs again


    var lastRow = drSheet.getRange("A12").getLastRow(); 
    var values = drSheet.getRange(lastRow + 1, 1);
    values.setValues(qty);
google-sheets google-apps-script
1 Answers

First create a row:

 const row = new Array(inventorySheet.getLastColumn()).fill('');
 row[1] = qty;
 row[3] = itemNumber;
 row[6] = bStaff;

Then you can either use:

 inventorySheet.appendRow(row);

or:

inventorySheet.getRange(inventorySheet.getLastRow() + 1, 1, 1, 
  row.length).setValues([row]); // note the nested [row]
userFDSs0
Related