How to open a sheet by script from a spreadsheet?

Question

I have a sheet on my Google Drive called "Titito." I use SpreadsheetApp.open(Titito) to open it but it returns:

ReferenceError: "Titito" is not defined. (ligne 5, fichier "macros"

When I try to open it by its URL:

SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1NTql7GMdPg8at0VEaz2jFmzOd4zBVjwArqDqWBww_Ww/edit#gid=0');

it returns:

You do not have permission to call SpreadsheetApp.openByUrl. Required permissions:

While it's my file, created by me.

How can I open a sheet in my drive by a script in an other sheet?

I just would like to know how to do as I did it on Visual Excel (Workbooks.Open Filename:="c:\TermFB\Charges\chargecpt.xls") but on google script

Thanks.

So the "sheet on my drive" is a Google Spreadsheet on a Google Drive? — Scott Hunter, Sep 06 '19 at 12:49
You may need to enable some permissions as documented here: https://developers.google.com/apps-script/guides/sheets/functions#permissions — Cat, Sep 06 '19 at 12:54
Possible duplicate of [OpenByUrl throwing error: You do not have permission to call openByUrl (line 39, file "Code")](https://stackoverflow.com/questions/17712078/openbyurl-throwing-error-you-do-not-have-permission-to-call-openbyurl-line-39) — Cat, Sep 06 '19 at 12:54
In the context of your first issue, `Titito` is used a reference variable, make sure you define it. If you intended to use it as a string, wrap it in quotes. Seems to be a simple typographical error. — Mr. Polywhirl, Sep 06 '19 at 12:58
if I use SpreadsheetApp.open("Titito") it returns Cannot find method open(string) — Franck BOUISSOU, Sep 06 '19 at 13:49
The possible duplicate on few comments above is about using openByUrl in a custom function. If aren't using a custom function please edit your question to clarify it. Consider to add a [mcve]. — Rubén, Sep 06 '19 at 14:10

AMolina · Accepted Answer · 2019-09-10T12:38:01.243

0

If "Titito" is the name of the actual file, then SpreadsheetApp.open(Titito) is not the way; sa you can see in the documentation, that uses file objects. In order to get it through the name, you will need to instead use DriveApp (Documentation here). With it you can get the files in your Drive and get the one you're looking for, the code for that looks like this:

function fn(){
  var files = DriveApp.getFiles();
  var file;
  while (files.hasNext()){
    file = files.next();
    if (file.getName() == "Titito")
      break;
  }
  var ss = SpreadsheetApp.open(file).getUrl();
  // rest of your code...
}

UPDATE

OP needed a way to open the actual Spreadsheet through the script, after following the instructions in this video, they were able to achieve what they wanted.

edited Sep 10 '19 at 12:38

answered Sep 06 '19 at 14:02

AMolina

1,355
1
7
17

Code is correct but my sheet Titito don't open itself; it is always closed. – Franck BOUISSOU Sep 09 '19 at 06:57
Do you mean open as in Open a new tab with the Sheet? – AMolina Sep 09 '19 at 07:27
In that case open is not what you're looking for, the `openById()` & `open()` and other similar methods are for "opening" a reference to the file in question, not to open the actual file editor in a way that you can see it. It's used to access the information in the Sheets. In order to do what you want you will need to follow the approach at the end of [this answer](https://stackoverflow.com/a/10745403/11581830), it will show you how to open the new tab. I will update my code to show you what you need. – AMolina Sep 09 '19 at 08:52
If the answer doesn't help you, [this video](https://www.youtube.com/watch?v=2y7Y5hwmPc4) offers a good tutorial on the matter. – AMolina Sep 09 '19 at 08:58
Glad to hear it's solved, I'll update the answer to include the video, please consider marking it as accepted to mark the question as solved. – AMolina Sep 10 '19 at 12:36
If you store the new file with `var newSS = SpreadsheetApp.create('Toto')` then `newSS` will hold the reference for that Spreadsheet. As you can see in [the documentation](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#createname), `create()` returns a reference to the new Spreadsheet. – AMolina Sep 10 '19 at 13:10
How to mark the question as solved ? Sorry I'm new ! – Franck BOUISSOU Sep 10 '19 at 14:11
No problem, nest to the top of my answer, where you see the voting buttons there should be a green check-mark button under it, click that – AMolina Sep 10 '19 at 14:30

score 0 · Answer 2 · answered Sep 10 '19 at 14:09

function myFunction() {

// CREATE AND OPEN A FILE CALLED Toto

var ssNew = SpreadsheetApp.create('Toto'); var tyty = ssNew.getUrl();

// var selection=SpreadsheetApp.getActiveSheet().getActiveCell().getValue();

var html = "window.open('" + tyty + "');google.script.host.close();";

var userInterface = HtmlService.createHtmlOutput(html)

SpreadsheetApp.getUi().showModalDialog(userInterface, 'Open Tab');

}

Thanks to AMolina very helpful for my first step on google script !

How to open a sheet by script from a spreadsheet?

2 Answers2