Spreadsheet isn't opened by openById()

Question

maybe you can help me with my problem I tried to fix for hours. I have a function that should copy some values of cells of a row, if in column 6 of the row the value is being set to 6, to another spreadsheet. I already used ui.alert() to know where the function stops to work and it's when I overwrite the variable ss = SpreadsheetApp.getActiveSpreadsheet() with ss = SpreadsheetApp.openById('123456789'). I already made this in another Google Script and there it worked! Here you can see my Code:

var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  ui.createMenu('xyz')
      .addItem('xyz', 'menuItem1')     
      .addToUi();
}

function menuItem1() {
var row = SpreadsheetApp.getActiveSheet().getActiveCell().getRow();
var col = 2;
var x = SpreadsheetApp.getActiveSheet().getRange(row, col).getValue();
var y = Browser.inputBox(titletranslation , 'Please enter the translation here', Browser.Buttons.OK_CANCEL);
  if(y != "cancel" && y != "") {
var id ="123456789"
doc = DriveApp.getFileById(id).makeCopy(y)
doc.setSharing(DriveApp.Access.ANYONE_WITH_LINK,DriveApp.Permission.EDIT)
var url = doc.getUrl()
SpreadsheetApp.getActiveSheet().getRange(row, 5).setValue(url)
  }
}

function onEdit(event){
var row = event.range.getRow()
var column = event.range.getColumn()
if(column == 6) {
  if(ss.getActiveSheet().getRange(row, column).getValue() == 6) {
    var x = ss.getActiveSheet().getRange(row, 1)
    var y = ss.getActiveSheet().getRange(row, 2)
    var z = ss.getActiveSheet().getRange(row, 3)
    var xx = ss.getName()
    var yy = ss.getActiveSheet().getRange(row,4)

    ss = SpreadsheetApp.openById('123456')

    ss.setActiveSheet(ss.getSheetByName('Need to be published'))

    ss.getActiveSheet().getRange(ss.getActiveSheet().getLastRow() + 1,1).setValue(x)
    ss.getActiveSheet().getRange(ss.getActiveSheet().getLastRow() + 1,2).setValue(y)
    ss.getActiveSheet().getRange(ss.getActiveSheet().getLastRow() + 1,3).setValue(z)
    ss.getActiveSheet().getRange(ss.getActiveSheet().getLastRow() + 1,4).setValue(xx)
    ss.getActiveSheet().getRange(ss.getActiveSheet().getLastRow() + 1,5).setValue(yy)



  }

}

}

Hope you know what the problem is :) Thanks in advance

You can't do any *active* calls using scripts that are not container bound. Read this. In addition read minimal reproducible example, specifically, Describe the problem. "It doesn't work" isn't descriptive enough to help people understand your problem. Instead, tell other readers what the expected behavior should be. Tell other readers what the exact wording of the error message is, and which line of code is producing it. Use a brief but descriptive summary of your problem as the title of your question — TheMaster
As far as I unterstood, container bound scripts are coded in the Script Editor of Google Spreadsheets, Docs etc. right? - That's what I did... I also told what the Script should do - the important function is the onEdit(event) function that should set the Value of Cells in another Spreadsheet to the Values of certain cells of the current Spreadsheet. I also told which line is producing the error - line 11 of the onEdit(event) function ss = SpreadsheetApp.openById(). I don't know where I should have gotten error messages, so I din't add them. Maybe you could say where. — Niklas S
Error messages are found in view>Stackdriver error reporting. Yes. That's a container bound script. But the container is that spreadsheet to which it is bound. Not the new spreadsheet you're openingbyId(say, ss2). The script is not bound to ss2. Hence you cannot call active calls on ss2.ss.getsheetbyname() returns sheet. You can directly call .getRange() and other calls on sheet without activating it. — TheMaster

ziganotschka ziganotschka · Accepted Answer · 2020-04-13T09:51:31

The problem is the combination of

var x = ss.getActiveSheet().getRange(row, 1)

and

ss.getActiveSheet().getRange(ss.getActiveSheet().getLastRow() + 1,1).setValue(x)

Lets transform it to:

var range1 = ss.getActiveSheet().getRange(row, 1);
var range2 = ss.getActiveSheet().getRange(ss.getActiveSheet().getLastRow() + 1,1);
range2.setValue(range1);

range1 (or x) is not a value, and thus using it as a parameter for the method setValue() is wrong, see the documentation.

If what you want is to assign the value of range1 to range2, the correct syntax would be

var value = range1.getValue();
range2.setValue(value);

You should change all lines where you use the method setValue() accordingly.

Spreadsheet isn't opened by openById()

1 Answers