How to set formula to get value from specific sheet

Question

I set formula in sheet1 cell (1,9) to get the value from other sheet through google apps script. Where "sheet" is a variable, I need to get value from specific sheets. The code below shows "=sheet!B1" rather than "=6!B1".

var ss1 = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxx/edit#gid=0");
var sheet1 = ss1.getSheetByName("Sheet1");  
var sheet;
sheet = 6;
var sheet = sheet.toString();
sheet1.getRange(1,9).setFormula('sheet!B1')
}

`setFormula(\`${sheet}!B1\`)` – TheMaster Jul 12 '20 at 16:22 — TheMaster, Jul 12 '20 at 16:22

score 2 · Accepted Answer · answered Jul 12 '20 at 16:53

2

Google Apps Script uses JavaScript as programming language
setFormula requires a string as parameter.

Considering the above you could use any string manipulation technique available using the JavaScript version supported by the runtime that your project is using.

Among other techniques you coul use:

String concatenation operator i.e. sheet + '!B1' (if you are using the old (Mozilla Rhino) or the new runtime (Chrome v8) )
String templates i.e. `${sheet}!B1` (only if you are using the new runtime (Chrome V8) )

answered Jul 12 '20 at 16:53

Rubén

34,714
9
70
166

Works with your 1st suggestion. – LookForward Chuang Jul 13 '20 at 08:56
It only works when I tested by function my Function { }. I have no idea why it shows "#REF!" in the cell when I use function doGet(e) { } and pop out "unsolved sheet name xx ". I also tried to click on the cell and then ENTER, amazing is it does work. How to correct it? – LookForward Chuang Jul 13 '20 at 13:28
Please post a new question including a [mcve] – Rubén Jul 13 '20 at 16:11

How to set formula to get value from specific sheet

1 Answers1