CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Sowmay Jain
Sowmay Jain

Reputation: 403

App Script: How to run a similar script all over the rows?

Following is the "onEdit(e) code which calls other 2 functions. I want to apply the other functions till rows number 11. This script currently works on second row only.

Do I need to copy & paste the similar code and change the row numbers? Or is there any way to do this by scripting?

I'm a complete beginner.

function onEdit(e){
  // Set a comment on the edited cell to indicate when it was changed.
 {recordValue()};
 {clear()};
}

/////////

function recordValue() {
  //read the current trigger price in 'Set Alert'!G2 and record it in cell P2
  var triggerPrice = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Set Alerts").getRange('G2').getCell(1, 1).getValue();
  var outputCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Set Alerts").getRange('P2').getCell(1, 1);
  if (triggerPrice != "") {
  outputCell.setValue(triggerPrice);
}
}

/////////

function clear() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Set Alerts').getRange('S2');
  var rangedata = ss.getCell(1, 1).getValue();
  if (rangedata == "Fired") {
  {var ss2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Set Alerts').getRange('G2').getCell(1, 1).clear({contentsOnly: true});};
  }
 }

Upvotes: 0

Views: 56

Answers (1)

user6655984
user6655984

Reputation:

A quick way to edit these functions is to run a for loop, say over the rows 2 to 11: 

function recordValue() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Set Alerts");
  for (var row = 2; row <= 11; row++) {
    var triggerPrice = sheet.getRange('G' + row).getValue();
    if (triggerPrice != "") {
      sheet.getRange('P' + row).setValue(triggerPrice);
    }
  }
}

And similarly,

function clear() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Set Alerts');
  for (var row = 2; row <= 11; row++) {
    var rangedata = sheet.getRange('S' + row).getValue();
    if (rangedata == "Fired") {
      sheet.getRange('G' + row).clear({contentsOnly: true});
    }
  }
}

Remarks

  • There is no need to call .getCell(1, 1) on a one-cell range. There is no separate "cell" class in Apps Script, a cell is just a Range with one cell. All of your calls to getCell were redundant.
  • Getting a handle on the sheet first (var sheet = ...) shortens subsequent code.
  • There is no need to assign the return of clear method to anything, if you are not using it (the method returns the range that was cleared).
  • As written above, the code still doesn't conform to best practices - namely, it uses multiple getRange / getValue calls to access data, instead of a single getRange("G2:G11").getValues(). For such a small number of rows this isn't a major flaw, but something to keep in mind for the future.

Upvotes: 1

Related Questions