CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Antor Cha
Antor Cha

Reputation: 83

Simplify .setFormula code in Google Apps Sheet

I have this code in google sheets and I want to make it more efficient, simplify the code.

It goes without saying I'm new to the code.

function onEdit(e){

  if(e.range.getA1Notation() != "A3") return;

  var sheet = SpreadsheetApp.getActive().getSheetByName("screen");
  var cell1 = sheet.getRange("D1:D3");
  var cell2 = sheet.getRange("j1:j3");
  var cell3 = sheet.getRange("o1:o3");
  var cell4 = sheet.getRange("r1:r3");
  var cell5 = sheet.getRange("t1:t3");
  var cell6 = sheet.getRange("w1:w3");
  var cell7 = sheet.getRange("z1:z3");
  var cell8 = sheet.getRange("ac1:ac3");
  var cell9 = sheet.getRange("ag1:ag3");
  var cell10 = sheet.getRange("a3");

 cell1.setFormula('="Any"');
 cell2.setFormula('="Any"');
 cell3.setFormula('="Any"');
 cell4.setFormula('="Any"');
 cell5.setFormula('="Any"');
 cell5.setFormula('="Any"');
 cell6.setFormula('="Any"');
 cell7.setFormula('="Any"');
 cell8.setFormula('="Any"');
 cell9.setFormula('="Any"');
 cell10.clearContent();
}

Upvotes: 0

Views: 44

Answers (1)

Tanaike
Tanaike

Reputation: 201338

I believe your goal as follows.

  • You want to simplify the script.

Modification points:

  • When I saw your script, it seems that the formula for putting to ranges is the same. I thought that this will be useful for your goal.
  • And, in your situation, I think that the event object can be used. When the event object is used, the process cost can be reduced. Ref

When above points are reflected to your script, it becomes as follows.

Modified script:

function onEdit(e) {
  const range = e.range;
  if(range.getA1Notation() != "A3") return;
  const a1Notations = ["D1:D3", "j1:j3", "o1:o3", "r1:r3", "t1:t3", "w1:w3", "z1:z3", "ac1:ac3", "ag1:ag3"];
  e.source.getSheetByName("screen").getRangeList(a1Notations).setFormula('="Any"');
  range.clearContent();
}
  • In this case, setFormula of Class RangeList is used for putting the formula. And, using the event object, the range is used for clearContent.
    • By this modification, I think that the process cost of the script can be also reduced.

References:

Upvotes: 2

Related Questions