How should I properly use the set range and get range functions in Google Apps Script?

Question

I need to update the value of "product quantity" based on the value of "order quantity" but only when "order sku" is equal to "product sku".

function productLoop2() {

  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();

  var t = 2;
  var n = 2;
  var s = 0;

  for(var t = 2; t < 52; t++) {
    var x = activeSheet.getRange(t, 1).getValue();
    //x is the ORDER SKU
    var r = activeSheet.getRange(t, 2).getValue();
   //r is the ORDER QUANTITY
    var q = activeSheet.getRange(n, 3).getValue();
    //q is the PRODUCT SKU  
    var u = activeSheet.getRange(n, 4).getValue();
    //u is the PRODUCT QUANTITY

    if (x != q) {
    n++;
    } else {
      s = u - r;
    }
      var m = activeSheet.getRange(n,4).setValue(s);
  }

}

I need the cell "n,4" (order quantity) to update so the value equals the result of "u"(product quantity) minus "r"(order quantity)

With one-letter variable names, it's quite hard to read your code. I'm working on it now, but I'm not sure the purpose of `n` or why `t` and `n` are set to `2`. I need to understand this to help you make your code more efficient and properly use set/get range methods. It would also be helpful to know the shape of the range you're reading; `A1:D1` or `A1:B2` etc. — CalamitousCode, Jan 02 '19 at 09:29
Also, your calls to the spreadsheet can be reduced from 250 ([4 gets + 1 set] * 50) to 2 with the correct understanding. — CalamitousCode, Jan 02 '19 at 09:36
i'm extremely new to coding so just copied examples from here and there to try get the result i needed. in regards to the 2, its because the values begin from row 2 in the spreadsheet. — Ryota, Jan 02 '19 at 09:57
What shape is your data; Do all 5 values, (`order sku, order qty, product sku, product qty and stock level`) exist in the same row? In A2:E2 etc? — CalamitousCode, Jan 02 '19 at 10:04
i have 4 columns with 50 rows. What i'm trying to achieve is for every value where order sku = product sku, the order qty needs to minus from the product qty. for example, the order sku can be A1 with a cell value of "123" and i need it to find cell C7 where product sku = "123" and then decrease the order qty cell "B1" from product qty cell "D1". A messy example but hopefully you can understand it. — Ryota, Jan 02 '19 at 10:12

Александр Ермолин · Answer 1 · 2019-01-02T10:57:15.083

1

The code "if" fragment should be corrected as below:

if (x != q) {
  n++;
} else {
  activeSheet.getRange(n,4).setValue(u - r);
}

Update after discussion:

function updateProductQuantities() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var values = activeSheet.getDataRange().getValues();

  // Sum quantities by orders (columns A and B)
  var sku = {};
  for (var i in values) {
    if (i == 0) continue;  // headers row
    if (sku[values[i][0]] == undefined) {
      sku[values[i][0]] = values[i][1];
    } else {
      sku[values[i][0]] += values[i][1];
    }
  }

  // Update product quantities (columns C and D)
  for (i in values) {
    if (sku[values[i][2]] != undefined) {
      values[i][3] -= sku[values[i][2]];
    }
  }

  // return values to the sheet
  activeSheet.getDataRange().setValues(values);
}

You should use 2 "for" loops. One is for sum of orders quantities, and the other is for subtraction.

edited Jan 02 '19 at 10:57

answered Jan 02 '19 at 09:28

Александр Ермолин

1,145
1
10
17

The variables s and m are not required here. The last code line `var m = activeSheet.getRange(n,4).setValue(s);` should be removed too. – Александр Ермолин Jan 02 '19 at 09:36
ahh i see! i didn't know you could use "setValue(u - r)" like that! I'll try it out now :) – Ryota Jan 02 '19 at 09:58
After trying it, the values in my rows don't seem to change at all, it just skips through the data and ends the loop. Perhaps I haven't set something up properly? – Ryota Jan 02 '19 at 10:02
Can you share the sheet for read only access? We can make a copy and debug the above code. – Александр Ермолин Jan 02 '19 at 10:11
Pay attention, you take x and q values from **different rows** (t and n) and compare them. Are you sure the logic to be correct? – Александр Ермолин Jan 02 '19 at 10:17
Here's a shareable link, it's just sample data here so if you would like editing privileges i can do that too. https://docs.google.com/spreadsheets/d/16V-lj_fKXvgOxabq7AOsfnB2nm5OkDt45q4RfLIYPwE/edit?usp=sharing – Ryota Jan 02 '19 at 10:18
Thats correct, i need the first 2(A,B) columns to compare with the following 2(C,D) as A and B are specific to each other and C and D are linked – Ryota Jan 02 '19 at 10:20
I have noticed that row values are not all logically dependent. In general, you can leave A and B columns on the first sheet and move C and D columns to another sheet. No relations will be broken. Correct? – Александр Ермолин Jan 02 '19 at 10:34
Yes that's correct, theres no reason for them to be on the same page, however, i don't know how to connect 2 sheets in a booklet so i compiled them onto a single page. – Ryota Jan 02 '19 at 10:37
Thanks for the update, i'll try it out and see how it works! – Ryota Jan 02 '19 at 11:09
Just tried it and it works like a charm! I've never even considered an approach like that, it's genius! Thanks! – Ryota Jan 02 '19 at 11:14

CalamitousCode · Answer 2 · 2019-01-02T13:20:58.883

Here is how you can get all the data, modify it and set it in the sheet.

function productLoop2() {

    var sheet = SpreadsheetApp.getActive().getActiveSheet();
    // Get the active sheet of the active spreadsheet

    var orderRange = sheet.getRange('A2:B52');
    var productRange = sheet.getRange('C2:D52');
    // Get the data ranges (change these references as necessary)

    var orderData = orderRange.getValues();
    var productData = productRange.getValues();
    // Get the values from the ranges. This method returns an array.

    for (var row = 0; row < orderData.length; row++) {
    // Loops through every row of the order array

        // Arrays are zero-based; this means the first element is element 0,
        // the second element in element 1 and so on.
        // Data is accessed with [row index][column index];

        var oSku = orderData[row][0];
        var oQty = orderData[row][1];

        for (var productIndex = 0; productIndex < productData.length; productIndex++) {
        // Loops through every product in the product array

           var pSku = productData[productIndex][0];
           var pQty = productData[productIndex][1];

            if (oSku === pSku) {
                productData[productIndex][1] = pQty - oQty;
                // Changes the pQty value in the array

                break;
                // Added upon suggestion from user tehhowch
            }
        }
    }

    productRange.setValues(productData);
    // Sets all product values in the array to the range from which they were taken
}

References:

Multidimensional arrays

Best practices - batch operations

For loops

Thanks for the help! The comments you put make it so much easier to understand!! — Ryota, Jan 02 '19 at 11:18
Yikes, a nested `for` loop. This is not scalable to large product counts, especially as the number of orders grows. Review some of my answers on this subject for examples with better scaling. https://stackoverflow.com/a/50286994/9337071 https://stackoverflow.com/a/49519839/9337071 https://stackoverflow.com/a/51875692/9337071 https://stackoverflow.com/questions/18706085/is-checking-an-object-for-a-key-more-efficient-than-searching-an-array-for-a-str At minimum there should probably be a `break;` once the product is found, if you change nothing else. — tehhowch, Jan 02 '19 at 12:15

How should I properly use the set range and get range functions in Google Apps Script?

2 Answers2