CODEWITHSUNDEEP
google-sheetsgoogle-apps-scripttriggers
Nichipedia
Nichipedia

Reputation: 40

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!

Upvotes: 0

Views: 54

Answers (3)

Wicket
Wicket

Reputation: 38391

From the question

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

The specific code lines related to the above are

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

console.log(things.length);

You should understand that sheet.getRange("G1:G100") returns a Class Range object. The code prints undefined to the Execution logs because this class doesn't have a length property. Instead of length use getNumRows() both in the console.log statement as in the for statement that follows.

Regarding getting a blank as the return of the function, this is because the Execution Logs will not print the function result, you should have to use Logger.log or console.log to print something.

To learn about the Class Range please read https://developers.google.com/apps-script/reference/spreadsheet/range. Also, you might find helpful to read Extending Google Sheets - Google Apps Script

Upvotes: 2

doubleunary
doubleunary

Reputation: 19145

For better performance, use Range.setValues(), like this:

function resetContactCol() {
  const ss = SpreadsheetApp.getActive();
  const range = ss.getRange('Sheet1!G1:G100');
  const result = range.getValues().flat()
    .map(v => v === 'Yes' ? ['No'] : [v]);
  range.setValues(result);
}

See batching to understand why reducing the number of API calls is relevant in Apps Script.

Upvotes: 0

Jats PPG
Jats PPG

Reputation: 790

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:

Upvotes: 2

Related Questions