So, I've been teaching myself JavaScript to use with Google Sheets to make little programs to improve my life for several months now (side projects). I'm working on a randomizing Dinner Picker right now, using Google Sheets as the database, but I've been stuck on this looping array for weeks now, and scouring Google and forums hasn't yielded results yet.

This is where I am stuck. The array works with the logger portion, but nothing else. I cannot figure out what I need to replace the ??? with to get the values of certain cells in the rows, so I can check against them, and add the values of one cell into another.

// A looping function that goes down the rows, starting with row 2, and adds +1 to +5 depending on the priority of the meal.
// Stops and continues the main script once a priority is null. 
function iterateThroughRows() {
  var activePage = SpreadsheetApp.getActive().getSheetByName("Meal List");
  var data = activePage.getDataRange().getValues();

  data.forEach(function (row) { 
    Logger.log(row);
    var mealPriority = ???(row, 2).getValue(); // MEAL ( B / 2 )
    var mealSkip = ???(row, 3).getValue(); // TEMP_SKIP ( C / 3 )
    var mealPickedChance = ???(row, 4).getValue(); // PICKED_CHANCE ( D / 4 )

    console.log("Logger Row"); // DEBUGGING LOGS
    console.log("mealPriority = " + mealPriority); // DEBUGGING LOGS
    console.log("mealSkip = " + mealSkip); // DEBUGGING LOGS
    console.log("mealPickedChance = " + mealPickedChance); // DEBUGGING LOGS

    // if mealSkip == TRUE (set to FALSE and skip to the next row)
    // else mealPickedChance.setValue(mealPickedChance+mealPriority);
  });
}

I am trying to capture the values of 3 cells in each row.
One value is a bool, if it is set to TRUE, I want to change it to FALSE and them skip to the next row.
If that value is FALSE, I want to take the int value of one cell and add it into another.

(Example, if mealPriority = 5 and mealPickedChance = 4, then mealPickedChance would get updated to = 9.)
Rinse and repeat until the array finishes all rows.

So far I have tried:

• getRange

• getRow

• getColumn

• getCell

• row

• column

I'm pretty sure I can figure out the if and else statement and how to add the value from one cell to another. I'm just stuck on how to capture those values in the first place. Any help in the right direction would be greatly appreciated.

So, I've been teaching myself JavaScript to use with Google Sheets to make little programs to improve my life for several months now (side projects). I'm working on a randomizing Dinner Picker right now, using Google Sheets as the database, but I've been stuck on this looping array for weeks now, and scouring Google and forums hasn't yielded results yet.

This is where I am stuck. The array works with the logger portion, but nothing else. I cannot figure out what I need to replace the ??? with to get the values of certain cells in the rows, so I can check against them, and add the values of one cell into another.

// A looping function that goes down the rows, starting with row 2, and adds +1 to +5 depending on the priority of the meal.
// Stops and continues the main script once a priority is null. 
function iterateThroughRows() {
  var activePage = SpreadsheetApp.getActive().getSheetByName("Meal List");
  var data = activePage.getDataRange().getValues();

  data.forEach(function (row) { 
    Logger.log(row);
    var mealPriority = ???(row, 2).getValue(); // MEAL ( B / 2 )
    var mealSkip = ???(row, 3).getValue(); // TEMP_SKIP ( C / 3 )
    var mealPickedChance = ???(row, 4).getValue(); // PICKED_CHANCE ( D / 4 )

    console.log("Logger Row"); // DEBUGGING LOGS
    console.log("mealPriority = " + mealPriority); // DEBUGGING LOGS
    console.log("mealSkip = " + mealSkip); // DEBUGGING LOGS
    console.log("mealPickedChance = " + mealPickedChance); // DEBUGGING LOGS

    // if mealSkip == TRUE (set to FALSE and skip to the next row)
    // else mealPickedChance.setValue(mealPickedChance+mealPriority);
  });
}

I am trying to capture the values of 3 cells in each row.
One value is a bool, if it is set to TRUE, I want to change it to FALSE and them skip to the next row.
If that value is FALSE, I want to take the int value of one cell and add it into another.

(Example, if mealPriority = 5 and mealPickedChance = 4, then mealPickedChance would get updated to = 9.)
Rinse and repeat until the array finishes all rows.

So far I have tried:

• getRange

• getRow

• getColumn

• getCell

• row

• column

I'm pretty sure I can figure out the if and else statement and how to add the value from one cell to another. I'm just stuck on how to capture those values in the first place. Any help in the right direction would be greatly appreciated.

• javascript
• google-sheets
• google-apps-script

• You don't need to use getValue anymore. Change this line var mealPriority = ???(row, 2).getValue(); // MEAL ( B / 2 ) to var mealPriority = row[2]; // MEAL ( B / 2 ). Do the same with (row, 3) and (row, 4). – leylou Commented Mar 25 at 19:04
• That gets me much closer. I am learning that Javascript Arrays are a different beast, as code that works elsewhere doesn't work in an array. For example, I am getting an error when trying to update the mealSkip from true to false. "TypeError: mealSkip.setValue is not a function" mealSkip.setValue(false); I think I need to learn more about arrays and how they find and change data. If anyone has any links to resources that would be helpful, or internet search terms I should know to learn more, I would appreciate it, as a newbie – Kim Commented Mar 25 at 20:20
• Also, mealSkip = false; updates the value in the array, but doesn't update the value of the cell. – Kim Commented Mar 25 at 20:33

1 Answer 1

According to the documentation, getValues() returns a 2-dimensional array, meaning it can be indexed as arr[i][j].

The .forEach() takes care of the first index, so try this inside of it:

var mealPriority = row[2];

The index could be off, then just try another value.