CODEWITHSUNDEEP
google-apps-script
user4513271
user4513271

Reputation:

Google Apps Script find value and set value not working

Context is Google Sheets. My script is supposed to find a cell in column A with contents Cell 1 and then set the B and C columns in that row to Cell 2 and Cell 3. Unfortunately, despite rechecking this many times, I can't seem to get it to work.

var sheet = SpreadsheetApp.getActiveSheet();
for (var i = 1; i<sheet.getLastRow(); i++) {
  var value = sheet.getRange(i, 1).getValue();
  if (value == "Cell 1") {
    sheet.getRange(i, 2).setValue("Cell 2");
    sheet.getRange(i, 3).setValue("Cell 3");
  }
}

Can you help me solve this problem?

Upvotes: 0

Views: 253

Answers (1)

Tanaike
Tanaike

Reputation: 201358

In your script, at for (var i = 1; i<sheet.getLastRow(); i++) {}, there is i<sheet.getLastRow(). This means that the value of last row is not retrieved. So please modify

From :

for (var i = 1; i<sheet.getLastRow(); i++) {

To :

for (var i = 1; i<=sheet.getLastRow(); i++) {

or

for (var i = 1; i<sheet.getLastRow() + 1; i++) {

Other sample :

As an other sample, you can also achieve it using getValues() and setValues(). The sample script is as follows.

var sheet = SpreadsheetApp.getActiveSheet();
var values = sheet.getRange(1,1,sheet.getLastRow(),1).getValues();
var modified = values.map(function(e){return e[0] == "Cell 1" ? [e[0], "Cell 2", "Cell 3"] : [e[0], "", ""]});
sheet.getRange(1,1,modified.length,modified[0].length).setValues(modified);

References :

If I misunderstand your question, I'm sorry.

Upvotes: 1

Related Questions