CODEWITHSUNDEEP
google-apps-script
Ryota
Ryota

Reputation: 11

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

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)

Upvotes: 1

Views: 1956

Answers (2)

CalamitousCode
CalamitousCode

Reputation: 1414

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

Upvotes: 1

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

Reputation: 1175

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.

Upvotes: 1

Related Questions