CODEWITHSUNDEEP
javascriptarraysfor-loopgoogle-apps-scriptgoogle-sheets
Usmantastic
Usmantastic

Reputation: 43

Google Appscript For Loop runs only once when a function is called

I hope you are doing dandier than usual.

I am stuck on this problem for a couple days now and I don't know where I am making a mistake. Basically, I want to copy a column from one sheet(let's call it DailyOrders), make some changes and paste it into a column in another sheet in the same workbook.

Here is what it should look like:

1: DailyOrders Column M

2: Copy 2D array to a temp variable

3: Convert those array elements to string and do the processing 1 by one and keep pushing them in another 2D array

4: Push the new 2D array to Column A in the "sort" sheet.

The Problem:

The for loop that should traverse the original array and push those elements into the pusher function runs for correct times when I put Logger.Log statement for testing, but only once when I call the pusher function. I have no idea why that is happening.

function sort(){
var values = SpreadsheetApp.getActiveSheet().getRange("DailyOrders!M2:M4").getValues();
var newvalues = [];
for(i=0;i<values.length;i++){
  pusher(values[i]);
  Logger.log(i);
}

function pusher(item) {
  item = item.toString();
  item = item.split("|| ");
   for(i=0; i<item.length; i++){
    newvalues.push([item[i]]);        
  }
//push newvalues[] to "sort!A:A"}

This is my first question at stack overflow, please let me know if you need any further explanation.

Sample worksheet to regenerate the problem:

https://docs.google.com/spreadsheets/d/1709A3kLPQ5eVCL_bQcRSc0U33hdIz9_3juouqesHYZM/edit?usp=sharing

Upvotes: 0

Views: 1240

Answers (2)

fullfine
fullfine

Reputation: 1461

Answer

The loop inside the function pusher runs only once if the array item has only one element. Furthermore, you have to change i by j in order to avoid messing up the indexes. Once you have applied the pusher function to all the elements of DailyOrders, you can sort the resulting array with sort. Below is the modified code:

Code

function sort() {
  var values = SpreadsheetApp.getActiveSheet().getRange("DailyOrders!M1:M10").getValues();
  var newvalues = []
  for (i = 0; i < values.length; i++) {
    pusher(values[i])
  }

  function pusher(item) {
    item = item.toString()
    item = item.split("|| ")
    for (j = 0; j < item.length; j++) {
      newvalues.push([item[j]]);
    }
  }

  newvalues.sort()  
}

Upvotes: 0

Tanaike
Tanaike

Reputation: 201643

I believe your goal as follows.

  • You want to retrieve the values from the column "M" of "DailyOrders" sheet to an array.
  • When the cell value has ||, you want to split the value and put the splitted values to the array.
  • You want to put the created array to the column "A" of "sort" sheet.

Modification points:

  • The values retrieved by getValues is 2 dimensional array.
  • In your script, newvalues is not used in sort. By this, it is not used in pusher().
  • When getDisplayValues is used instead of getValues, the retrieved values are the string type. The values retrieved by getDisplayValues is also 2 dimensional array.

When above points are reflected to your script, it becomes as follows.

Modified script:

function sort() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var values = ss.getSheetByName("DailyOrders").getRange("M2:M").getDisplayValues();
  var newvalues = [];
  for (i = 0; i < values.length; i++) {
    var item = values[i][0];
    var items = item.split("||");
    if (items.length > 1) {
      for (var j = 0; j < items.length; j++) {
        newvalues.push([items[j].trim()]);
      }
    } else if (item) {
      newvalues.push([item]);
    }
  }
  var dstSheet = ss.getSheetByName("sort");
  dstSheet.getRange(dstSheet.getLastRow() + 1, 1, newvalues.length, 1).setValues(newvalues);
}

References:

Added:

From your following replying,

Thanks for a quick reply, but your answer doesn't address the problem I am facing and the code modifications you suggested don't seem to work. var item = values[i][0]; returns first character of M2, 2nd character of M3, 3rd character of M4 and so on. I don't have a problem with getting the value as an array or as a string, I just want to debug why the for loop only runs once when I call a function inside it.

In your script, the variable i is used in 2 for loops. By this, i is always initialized. And also, in your situation, i is always less than values.length. By this, the loop is not finished. I think that this is the reason of your issue. If you want to modify your script, please modify your script as follows.

From:

 for(i=0; i<item.length; i++){
  newvalues.push([item[i]]);        
}

To:

for (var j = 0; j < item.length; j++) {
  newvalues.push([item[j]]);
}

Upvotes: 2

Related Questions