Btukfyl

What should I do to change the code below (at the very bottom) so that it works for multiple column pairs, not just one column pair?

I am tracking inventory in a sheet, something like this:

ColA      ColB  ColC  ColD  ColE  ColF  ColG  ColH

Category  Item  R+/-  G+/-  B+/-  Red   Green Blue

AAA        A                       1     0     0

AAA        B                       2     1     0

I want to be able to type numbers into ColC, ColD and ColE and then click a button to subtract those numbers from the totals in ColF, G and H, respectively.

I found a similar question with a great answer here, for ONE column pair:
Google Sheets - How to create add and subtract buttons to track inventory. The code got me started. I'm pretty sure I needed to update the getRange stuff from what it was (shown immediately below) to what is now listed in the whole function code further down. (I also changed some names/variables to better match my inventory needs.)

  var updateRange = sheet.getRange(2, 3, maxRows); // row, column, number of rows

  var totalRange = sheet.getRange(2, 4, maxRows);

But what do I do with the for section so that it works for all three column pairs, not just for ColC & ColF? I tried adding a "var col in updateValues" but it didn't like col (or column); besides i wasn't sure how to nest it with the var row that's already there. (I did notice that if I changed the 0 after each [row] to 1, it would do the 2nd columns. But it didn't like it when I did "var col in updateValues" and then "updateValue[0][col]".)

function subtractUpdateBulk() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var maxRows = sheet.getMaxRows();



  var updateRange = sheet.getRange(2, 3, maxRows, 3); // row, column, # of rows, # of cols

  var totalRange = sheet.getRange(2, 6, maxRows, 3);



  var updateValues = updateRange.getValues();

  var totalValues = totalRange.getValues();

  for (var row in updateValues) {

    var updateCellData = updateValues[row][0];

    var totalCellData = totalValues[row][0];



    if (updateCellData != "" && totalCellData != "") {

      totalValues[row][0] = totalCellData - updateCellData;

      updateValues[row][0] = "";

    }

  }



  updateRange.setValues(soldValues);

  totalRange.setValues(totalValues);

}

If you offer code alone is great. A bit of explanation to go with it would be even better, so I understand the WHY and can hopefully apply it elsewhere.

Your code was pretty close.

This is the substitute code to manage three columns of data movements and three columns of totals. Most of the code is self-explanatory but I'll focus on a couple of points. Its good that you're interested in the why as well as the how, and I've left some DEBUG lines that hopefully with assist.

1) I setup the spreadsheet and sheet using "standard" commands. In this case, I used getSheetByName to ensure that the code always would execute on the desired sheet.

2) I didn't use getMaxRows because this returns "the current number of rows in the sheet, regardless of content.". So if your spreadsheet has 1,000 rows but you've only got, say, 20 rows of data, getmaxRows will return a value of 1,000 and force you to evaluate more rows than are populated with data. Instead I used the code on lines 30 and 30 var Avals and var Alast which use a javascript command to quickly return the number of rows that have data. I chose Column A to use for this, but you could change this to some other column.

3) Rather than declare and get values for two ranges (updateRange and totalRange), I declared only one data range totalRange and got the values for all 6 columns. getValues is a fairly time costly process; by getting values for all rows and all columns, you can then pick and choose which columns you want to add together.

The command is:
var totalRange = sheet.getRange(NumHeaderRows + 1, 3, Alast - NumHeaderRows, 6);

The syntax (as you noted) is row, column, # of rows, # of cols.

The start row is the row following the header => (NumHeaderRows + 1).

The start column is Column C => 3.

The number of rows is the data rows less the header rows => (Alast - NumHeaderRows)

The number of columns is ColC, ColD, ColE, ColF, ColG => 6

4) for (var row in totalValues) {

This was a new one for me, and its so simple, so I keep it.

5) I used two arrays, just as you did. I used one array (RowArray) to build the values for each row, and the second array (MasterArray)is cumulative.

BTW, in your code soldValues isn't ever declared and no values are ever assigned to it.

6) The most important thing is the calculation of the adjustments on each line:

For the sake of clarity, I declared three variables totalRed, totalGreen and totalBlue, and showed how the totals were calculated for each value. This wasn't strictly necessary (I could have just pushed the formulas for each new totals), but they enable you to how each movement is calculated, and the column numbers used in each case.

function so_53505294() {



    // set up spreadsheet

    // include getsheetbyname to ensure calculations happen on the correct sheet

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var sheet = ss.getSheetByName("Sheet1");



    //set some variables

    var NumHeaderRows = 1; // this is the number of header rows - user changeable

    var totalRed = 0;

    var totalGreen = 0;

    var totalBlue = 0;



    // arrays used later in loop

    var RowArray = ; // row by row values

    var MasterArray = ; // row by row, cumulative values



    // get number of rows of data

    var Avals = ss.getRange("A1:A").getValues();

    var Alast = Avals.filter(String).length;

    // Logger.log("the last row = "+Alast);// DEBUG



    // define the entire data range

    var totalRange = sheet.getRange(NumHeaderRows + 1, 3, Alast - NumHeaderRows, 6); // row, column, # of rows, # of cols

    //Logger.log("the TotalRange = "+totalRange.getA1Notation());//DEBUG



    // get the data fior the entire range

    var totalValues = totalRange.getValues();



    // loop through thr rows

    for (var row in totalValues) {



        // clear RowArray at the start of each new row

        RowArray = ;



        // calculate the new totals

        totalRed = totalValues[row][0] + totalValues[row][3];

        totalGreen = totalValues[row][1] + totalValues[row][4];

        totalBlue = totalValues[row][2] + totalValues[row][5];

        //Logger.log("row = "+row+", Movement RED  = "+totalValues[row][0]+", Old Stock RED = "+totalValues[row][3]+", New RED = "+totalRed); //DEBUG

        //Logger.log("row = "+row+", Movement GREEN  = "+totalValues[row][1]+", Old Stock GREEN = "+totalValues[row][4]+", New GREEN = "+totalGreen); //DEBUG

        //Logger.log("row = "+row+", Movement BLUE  = "+totalValues[row][2]+", Old Stock BLUE = "+totalValues[row][5]+", New BLUE = "+totalBlue); //DEBUG



        // update the RowArray for this row's values

        RowArray.push(0, 0, 0, totalRed, totalGreen, totalBlue);

        // update the MasterArray for this row's values

        MasterArray.push(RowArray);

    }



    // Update the data range with the new Master values.

    totalRange.setValues(MasterArray);

}