Google Sheets Script / Dynamically Add Formula

1
votes

I'm trying append some daily values to a sheet but can't quite figure out how to capture the current cell in a variable and then reuse it to generate the formula.

This is the code that I'm using

var emptyA = sheet.getRange(sheet.getLastRow()+1,1,1)
var emptyB = sheet.getRange(sheet.getLastRow()+1,2,1)
var emptyC = sheet.getRange(sheet.getLastRow()+1,3,1)

I'd like multiply the daily value in the A column with the value in cell F5 which also updates daily, because this changes it should be a formula so I figure it's something along the lines of

var dailyValue = sheet.getRange('F5')
var lastA = sheet.getRange(sheet.getLastRow(),2,1)
var formula = "=" + lastA + "*" + dailyValue

emptyB.setFormula([formula])

The problem that I'm having is lastA gives me "RANGE" rather than the cell ID

Does anyone have any thoughts?

Cheers, Rich

2
javascriptgoogle-sheets
Change to: var dailyValue = sheet.getRange('F5').getValue var lastA = sheet.getRange(sheet.getLastRow(),2).getValue emptyB.setFormula(formula) - Stefan van Aalst

2 Answers

0
votes

I guess you need to change

var formula = "=" + lastA + "*" + dailyValue

to

var formula = "=" + "F5" + "*" + dailyValue.getA1Notation();

Use getA1Notation() to return cell address like A500.

Use getValue() if you need the value to be hardcoded instead of a link to cell.

0
votes

Change the following lines:

var dailyValue = sheet.getRange('F5').getValue
var lastA = sheet.getRange(sheet.getLastRow(),2).getValue

emptyB.setFormula(formula)