google-sheetsgoogle-apps-scripttriggers

How to use Google AppScript to edit a spreadsheet?

I am trying to write a Apps Script function to edit a column in a spreadsheet.

Specifically, I have a column that contains values two values. The values are "Yes" and "No".

Every Sunday morning, I want to trigger my function to run and toggle any "Yes" values to "No" values.

I have tried several approaches and none have been fruitful. When I try to run the script from the spreadsheet I get a blank return in the execution history.

Below is the code.

function resetContactCol() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  
  var things = sheet.getRange("G1:G100");

  console.log(things.length);

  for (var i = 0; i < things.length; i++) {
    if (things.getCell(i, 1).getValue() === "Yes") {things.getCell(i,1).setValue("No")}
  }

}

It is important to note, the console log prints undefined.

I am very new to Apps Script but have significant experience in Java and JavaScript. I think I am missing something simple!

Solution

  • Use getNumRows() instead of .length

    The reason you're getting undefined when running your code is because var things = sheet.getRange("G1:G100"); is being used on the range G1:G100 itself, not on the values within that range. If you want it to return 100, add .getValues()

    I slightly modified your code and used getNumRows() to get the number of rows in the range.

    Here's the modified code:

     function resetContactCol() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var things = sheet.getRange("G1:G100");
  var rows = things.getNumRows(); // Getting all the rows specified in the range

  for (var i = 1; i <= rows; i++) { 
    var row = things.getCell(i, 1); // Declaring a variable for getCell
    if (row.getValue() === "Yes") { 
      row.setValue("No"); // Changing all the "Yes" to No
    }
  }
}

    If you want to use .length, here's a modified version of your code:

    function resetContactCol() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");

  var things = sheet.getRange("G1:G100").getValues();

  for (var i = 0; i < things.length; i++) {
    if (things[i][0] === "Yes") {
      sheet.getRange(i + 1, 7).setValue("No"); 
    }
  }
}

    References: