I need to update the values of cells. Whatever I try, it either coughs up an error, or only updates the value in the script temporarily, but doesn't update the cell.

Here is my array:

// 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 the array finishes going through all filled rows.
function iterateThroughRows() {
  var activePage = SpreadsheetApp.getActive().getSheetByName("Meal List");
  var data = activePage.getDataRange().getValues();
  data.shift(); // Skips the first row header.

  data.forEach(function (row) { 
    console.log("Logger Row -----------------------------------------------------------------------------------------------------"); // DEBUGGING LOGS
    Logger.log(row); // DEBUGGING LOGS

    var mealSkip = row[2];
    var mealPickedChance = row[3];
    var mealPriority = row[4];

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

    if (mealSkip === true) {
      //mealSkip.push(false);
      //mealSkip.setValue(false);
      //mealSkip = [false];
      //data.setValue[2](false);
      console.log("Set mealSkip to equal FALSE = " + mealSkip); // DEBUGGING LOGS
    }
    else {
      //mealPickedChance.setValue(mealPickedChance+mealPriority);
      console.log("Update mealPickedChance = " + mealPickedChance); // DEBUGGING LOGS
    }
  });
}

I want to make two changes:

• If mealSkip equals TRUE, set it to FALSE and continue to the next row.
• Else, take the int value from mealPriority and add/sum it to int value of mealPickedChance, then continue to the next row.

How do I get an array to update the value of a cell here?

I need to update the values of cells. Whatever I try, it either coughs up an error, or only updates the value in the script temporarily, but doesn't update the cell.

Here is my array:

// 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 the array finishes going through all filled rows.
function iterateThroughRows() {
  var activePage = SpreadsheetApp.getActive().getSheetByName("Meal List");
  var data = activePage.getDataRange().getValues();
  data.shift(); // Skips the first row header.

  data.forEach(function (row) { 
    console.log("Logger Row -----------------------------------------------------------------------------------------------------"); // DEBUGGING LOGS
    Logger.log(row); // DEBUGGING LOGS

    var mealSkip = row[2];
    var mealPickedChance = row[3];
    var mealPriority = row[4];

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

    if (mealSkip === true) {
      //mealSkip.push(false);
      //mealSkip.setValue(false);
      //mealSkip = [false];
      //data.setValue[2](false);
      console.log("Set mealSkip to equal FALSE = " + mealSkip); // DEBUGGING LOGS
    }
    else {
      //mealPickedChance.setValue(mealPickedChance+mealPriority);
      console.log("Update mealPickedChance = " + mealPickedChance); // DEBUGGING LOGS
    }
  });
}

I want to make two changes:

• If mealSkip equals TRUE, set it to FALSE and continue to the next row.
• Else, take the int value from mealPriority and add/sum it to int value of mealPickedChance, then continue to the next row.

How do I get an array to update the value of a cell here?

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

• 3 Regarding "coughs up an error", what's the error message and to which line of code does it refer? – mykaf Commented Mar 27 at 19:08
• mealSkip is the value of a particular cell, not a reference to that cell. – James Commented Mar 27 at 19:18
• 2 Also, add some sample data to be read from the spreadsheet and the expected results. – Wicket Commented Mar 27 at 19:20
• function iterateThroughRows() { const ss = SpreadsheetApp.getActive(); var sh = SpreadsheetApp.getActive().getSheetByName("Meal List"); const sr = 2; var data = sh.getRange(sr , 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues(); data.shift(); // Skips the first row header. data.forEach(function (row, i) { var mS = row[2]; var mPi = row[3]; var mPr = row[4]; if (mS === true) { sh.getRange(i + sr, 3).setValue("FALSE"); } }); } – Cooper Commented Mar 27 at 20:01

1 Answer 1

You may change:

if (mealSkip === true) {
      //mealSkip.push(false);
      //mealSkip.setValue(false);
      //mealSkip = [false];
      //data.setValue[2](false);
      console.log("Set mealSkip to equal FALSE = " + mealSkip); // DEBUGGING LOGS
    }

To:

  if (mealSkip === true) {
      row[2] = false; // <<< change the value of the cell to false here
      console.log("Set mealSkip to equal FALSE = " + mealSkip); // DEBUGGING LOGS
    }

and add this to the last line to update the current values:

activePage.getRange(2,1,data.length, data[0].length).setValues(data); // << overwrite current data to set true to false here

Reference:

JavaScript Arrays