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)

asked Jan 2 at 9:12

Ryota

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.

– s1c0j1
Jan 2 at 9:29

Also, your calls to the spreadsheet can be reduced from 250 ([4 gets + 1 set] * 50) to 2 with the correct understanding.

– s1c0j1
Jan 2 at 9: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 2 at 9: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?

– s1c0j1
Jan 2 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 2 at 10:12

add a comment |

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)

asked Jan 2 at 9:12

Ryota

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.

– s1c0j1
Jan 2 at 9:29

Also, your calls to the spreadsheet can be reduced from 250 ([4 gets + 1 set] * 50) to 2 with the correct understanding.

– s1c0j1
Jan 2 at 9: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 2 at 9: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?

– s1c0j1
Jan 2 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 2 at 10:12

add a comment |

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)

asked Jan 2 at 9:12

Ryota

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)

google-apps-script

asked Jan 2 at 9:12

Ryota

asked Jan 2 at 9:12

Ryota

asked Jan 2 at 9:12

Ryota

asked Jan 2 at 9:12

Ryota

asked Jan 2 at 9:12

Ryota

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.

– s1c0j1
Jan 2 at 9:29

Also, your calls to the spreadsheet can be reduced from 250 ([4 gets + 1 set] * 50) to 2 with the correct understanding.

– s1c0j1
Jan 2 at 9: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 2 at 9: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?

– s1c0j1
Jan 2 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 2 at 10:12

add a comment |

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.

– s1c0j1
Jan 2 at 9:29

Also, your calls to the spreadsheet can be reduced from 250 ([4 gets + 1 set] * 50) to 2 with the correct understanding.

– s1c0j1
Jan 2 at 9: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 2 at 9: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?

– s1c0j1
Jan 2 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 2 at 10:12

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.

– s1c0j1
Jan 2 at 9:29

Also, your calls to the spreadsheet can be reduced from 250 ([4 gets + 1 set] * 50) to 2 with the correct understanding.

– s1c0j1
Jan 2 at 9: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 2 at 9: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?

– s1c0j1
Jan 2 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 2 at 10:12

add a comment |

2 Answers
2

active

oldest

votes

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 2 at 10:57

answered Jan 2 at 9:28

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

51626

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 2 at 9:36

ahh i see! i didn't know you could use "setValue(u - r)" like that! I'll try it out now :)

– Ryota
Jan 2 at 9: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 2 at 10:02

Can you share the sheet for read only access? We can make a copy and debug the above code.

– Александр Ермолин
Jan 2 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 2 at 10:17

|
show 7 more comments

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

edited Jan 2 at 13:20

answered Jan 2 at 10:46

s1c0j1

9561717

Thanks for the help! The comments you put make it so much easier to understand!!

– Ryota
Jan 2 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. stackoverflow.com/a/50286994/9337071 stackoverflow.com/a/49519839/9337071 stackoverflow.com/a/51875692/9337071 stackoverflow.com/questions/18706085/… At minimum there should probably be a break; once the product is found, if you change nothing else.

– tehhowch
Jan 2 at 12:15

Thanks for the insight, @tehhowch.

– s1c0j1
Jan 2 at 12:21

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54003743%2fhow-should-i-properly-use-the-set-range-and-get-range-functions-in-google-apps-s%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

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 2 at 10:57

answered Jan 2 at 9:28

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

51626

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 2 at 9:36

ahh i see! i didn't know you could use "setValue(u - r)" like that! I'll try it out now :)

– Ryota
Jan 2 at 9: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 2 at 10:02

Can you share the sheet for read only access? We can make a copy and debug the above code.

– Александр Ермолин
Jan 2 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 2 at 10:17

|
show 7 more comments

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 2 at 10:57

answered Jan 2 at 9:28

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

51626

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 2 at 9:36

ahh i see! i didn't know you could use "setValue(u - r)" like that! I'll try it out now :)

– Ryota
Jan 2 at 9: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 2 at 10:02

Can you share the sheet for read only access? We can make a copy and debug the above code.

– Александр Ермолин
Jan 2 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 2 at 10:17

|
show 7 more comments

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 2 at 10:57

answered Jan 2 at 9:28

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

51626

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 2 at 10:57

answered Jan 2 at 9:28

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

51626

edited Jan 2 at 10:57

answered Jan 2 at 9:28

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

51626

answered Jan 2 at 9:28

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

51626

answered Jan 2 at 9:28

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

51626

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 2 at 9:36

ahh i see! i didn't know you could use "setValue(u - r)" like that! I'll try it out now :)

– Ryota
Jan 2 at 9: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 2 at 10:02

Can you share the sheet for read only access? We can make a copy and debug the above code.

– Александр Ермолин
Jan 2 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 2 at 10:17

|
show 7 more comments

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 2 at 9:36

ahh i see! i didn't know you could use "setValue(u - r)" like that! I'll try it out now :)

– Ryota
Jan 2 at 9: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 2 at 10:02

Can you share the sheet for read only access? We can make a copy and debug the above code.

– Александр Ермолин
Jan 2 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 2 at 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 2 at 9:36

ahh i see! i didn't know you could use "setValue(u - r)" like that! I'll try it out now :)

– Ryota
Jan 2 at 9: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 2 at 10:02

Can you share the sheet for read only access? We can make a copy and debug the above code.

– Александр Ермолин
Jan 2 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 2 at 10:17

|
show 7 more comments

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

edited Jan 2 at 13:20

answered Jan 2 at 10:46

s1c0j1

9561717

Thanks for the help! The comments you put make it so much easier to understand!!

– Ryota
Jan 2 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. stackoverflow.com/a/50286994/9337071 stackoverflow.com/a/49519839/9337071 stackoverflow.com/a/51875692/9337071 stackoverflow.com/questions/18706085/… At minimum there should probably be a break; once the product is found, if you change nothing else.

– tehhowch
Jan 2 at 12:15

Thanks for the insight, @tehhowch.

– s1c0j1
Jan 2 at 12:21

add a comment |

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

edited Jan 2 at 13:20

answered Jan 2 at 10:46

s1c0j1

9561717

Thanks for the help! The comments you put make it so much easier to understand!!

– Ryota
Jan 2 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. stackoverflow.com/a/50286994/9337071 stackoverflow.com/a/49519839/9337071 stackoverflow.com/a/51875692/9337071 stackoverflow.com/questions/18706085/… At minimum there should probably be a break; once the product is found, if you change nothing else.

– tehhowch
Jan 2 at 12:15

Thanks for the insight, @tehhowch.

– s1c0j1
Jan 2 at 12:21

add a comment |

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

edited Jan 2 at 13:20

answered Jan 2 at 10:46

s1c0j1

9561717

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

edited Jan 2 at 13:20

answered Jan 2 at 10:46

s1c0j1

9561717

edited Jan 2 at 13:20

answered Jan 2 at 10:46

s1c0j1

9561717

answered Jan 2 at 10:46

s1c0j1

9561717

answered Jan 2 at 10:46

s1c0j1

9561717

Thanks for the help! The comments you put make it so much easier to understand!!

– Ryota
Jan 2 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. stackoverflow.com/a/50286994/9337071 stackoverflow.com/a/49519839/9337071 stackoverflow.com/a/51875692/9337071 stackoverflow.com/questions/18706085/… At minimum there should probably be a break; once the product is found, if you change nothing else.

– tehhowch
Jan 2 at 12:15

Thanks for the insight, @tehhowch.

– s1c0j1
Jan 2 at 12:21

add a comment |

Thanks for the help! The comments you put make it so much easier to understand!!

– Ryota
Jan 2 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. stackoverflow.com/a/50286994/9337071 stackoverflow.com/a/49519839/9337071 stackoverflow.com/a/51875692/9337071 stackoverflow.com/questions/18706085/… At minimum there should probably be a break; once the product is found, if you change nothing else.

– tehhowch
Jan 2 at 12:15

Thanks for the insight, @tehhowch.

– s1c0j1
Jan 2 at 12:21

Thanks for the help! The comments you put make it so much easier to understand!!

– Ryota
Jan 2 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. stackoverflow.com/a/50286994/9337071 stackoverflow.com/a/49519839/9337071 stackoverflow.com/a/51875692/9337071 stackoverflow.com/questions/18706085/… At minimum there should probably be a break; once the product is found, if you change nothing else.

– tehhowch
Jan 2 at 12:15

Thanks for the insight, @tehhowch.

– s1c0j1
Jan 2 at 12:21

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu