CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-formula
LookForward Chuang
LookForward Chuang

Reputation: 145

How can Google Apps Script setFormula set a formula to sheet cell containing variables

My plan is to calculate how many days between 2 given dates by using function DATEDIF in Google Spreadsheet and the function must be from Google Apps Script. I can get it by below.

sheet.getRange(x,10).setFormula('datedif(K32,L32,"d")');

However, as the row is increasing by AppendRow whenever GAS calls spreadsheet so I tried to use variable x to get the last row. It makes me know where the target row is. My trouble is that I could not let setFormula to use variable as below, where start and due are A1Notation of the 2 given cells with date.

sheet.getRange(x,9).setFormula('datedif([start],[due],"d")');

MY code is as below.

   var x = sheet.getLastRow();
   
   sheet.getRange(x,11).setValue("2020/05/20");
   sheet.getRange(x,12).setValue("2020/07/21");
    
   start = sheet.getRange(x,11).getA1Notation();
   due = sheet.getRange(x,12).getA1Notation();  
    
   
  sheet.getRange(x,9).setFormula('datedif([start],[due],"d")');

  sheet.getRange(x,10).setFormula('datedif(K32,L32,"d")');

Upvotes: 0

Views: 4835

Answers (1)

Rafa Guillermo
Rafa Guillermo

Reputation: 15357

Answer:

You need to concatenate the cells' A1 notations into your string in setFormula().

Code Modification:

As of V8, you can use template literals inside Google Apps Script. You can do this as below:

start = sheet.getRange(x,11).getA1Notation();
due = sheet.getRange(x,12).getA1Notation();  

sheet.getRange(x,9).setFormula(`datedif(${start},${due},"d")`);

References:

Upvotes: 1

Related Questions