Google Apps Script setFormula() in sheet cell encounters "#REF!" issue by using function doGet(e)

0
votes

Sheet cell can successfully show the result of the formula I setup from myFunction() but failed when I input variable by using doGet(e) { }. It shows "#REF!" in the cell and "unresolved sheet name xx".

In this function, setFormula() can successfully show the result in sheet cell.

function myFunction() {
var ss1 = SpreadsheetApp.openByUrl("https://xxxxxxxxxxxxxxxxxxxxxxxxgid=0");
var sheet1 = ss1.getSheetByName("Sheet1");  
var sheet;
sheet = 6;
var sheet = sheet.toString();
sheet1.getRange(6,9).setFormula(sheet + '!B1');
}

Later on, I followed the syndax of setFormula to setup as below, but somehow it not work. It shows "#REF!" in sheet cell.

function doGet(e) {

var shopname = e.parameter.shop1;    
var shopname = shopname.toString();
var row = sheet.activate().getLastRow();
sheet.activate().getRange(row,9).setFormula( shopname + '!B1');

//  sheet.activate().getRange(row,9).setFormula("'" + shopname + "'!" + "B1");
//    sheet1.getRange(6,9).setFormula("'" + sheet + "'!" + "B1");
}
1
google-apps-scriptgoogle-sheets

1 Answers

2
votes

sheet is not defined within the function doGet()

  • If you want to make it accessible by both myFunction and doGet - you need to make it a global variable
  • However, currently sheet is a value (6) rather than a sheet object
  • What you probably want to do is to write the data in sheet1?

A sample to do so:

var ss1 = SpreadsheetApp.openByUrl("https://xxxxxxxxxxxxxxxxxxxxxxxx");
var sheet1 = ss1.getSheetByName("Sheet1"); 

function myFunction() { 
  var sheet;
  sheet = 6;
  sheet1.getRange(6,9).setFormula(sheet + '!B1');
}


function doGet(e) {
  
  var shopname = e.parameter.shop1;    
  var shopname = shopname.toString();
  var row = sheet1.getLastRow();
  sheet1.getRange(row,9).setFormula( shopname + '!B1');
}

Note: You do not need to activate() a sheet or range to access it