How to get Google Sheet Id after sheet creation via API?

Question

I have a method that creates and upload a sheet to Google Drive. When a user clicks on a button that says "create a report", the report is created and uploaded to the user google drive. It works great. However, the response from Google after the sheet is being saved to google drive doesn't contain the sheet id nor the sheet URL. Users are saying that when they click on the "create report" button, they want the google sheet to open in a new tab after it's being saved to their google drive. They don't want to go in their drive to open the file manually. I was thinking that the response being return after the sheet is being uploaded would've contained at least the sheet id or the URL to the resource that's being created in google drive. Does anyone have an idea on how to accomplish what I am driving to do? I am using Google API to upload the file to the users drive.

Here is the response that get sent after the sheet is uploaded

   "https://www.googleapis.com/upload/drive/v3/files? 
   uploadType=multipart&fields=id%2Cname%2Ckind", redirected: false, 
   status: 200, ok: true, …}
   body: ReadableStream
   bodyUsed: false
   headers: Headers {}
   ok: true
   redirected: false
   status: 200
   statusText: ""
   type: "cors"
   url: "https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart&fields=id%2Cname%2Ckind"

 //Here is the code: 
           let metadata = {
                name: excelFileName,
                mimeType: "application/vnd.google-apps.spreadsheet",
            };
            let form = new FormData();
            form.append('metadata', new Blob([JSON.stringify(metadata)], { type: 'application/json' }));
            form.append('file', excelFile);
            let url = new URL('https://www.googleapis.com/upload/drive/v3/files');
            url.search = new URLSearchParams({ uploadType: 'multipart', fields: 'id,name,kind' });
            try {
                let res = await fetch(url, {
                    method: 'POST',
                    headers: new Headers({ 'Authorization': 'Bearer ' + gapi.auth.getToken().access_token }),
                    body: form
                });


                if (res.ok) {
                    console.log(gapi)
                    console.log(res)
                    alert(`"${excelFileName}" has successfully saved to your google drive!`);
                } else {
                    console.error(`Encounter a problem saving excel file, ${excelFileName}, to google drive`);
                }
            } catch (ex) {
                console.error(`Oops excel file for "${excelFileName}" wasn't saved. ${error}`)
            }

You're using app script to upload the file? You want the url or id in app script right? can you post your code when you make the request? — Andres Duarte, Jun 25 '19 at 14:59
Please review your tags, you are not using Google apps script but drive API as mentioned in your question. you'll have better chances to get an answer. — Serge insas, Jun 25 '19 at 15:28

score 1 · Answer 1 · answered Jun 25 '19 at 22:45

When you uploaded a file, you want to retrieve the file ID and URLs of the uploaded file.
You want to achieve by modifying your current script.

If my understanding is correct, how about this modification?

Modification points:

In order to return the file ID and URLs, in this case, these fields are used.
- exportLinks,id,kind,name,webContentLink,webViewLink
In order to retrieve the returned values from res, it uses res.json().

When above modification points are reflected to your script, it becomes as follows.

Modified script:

Please modify 2 parts of your script as follows.

From:

url.search = new URLSearchParams({ uploadType: 'multipart', fields: 'id,name,kind' });

To:

url.search = new URLSearchParams({ uploadType: 'multipart', fields: 'exportLinks,id,kind,name,webContentLink,webViewLink' });

And

From:

if (res.ok) {
  console.log(gapi)
  console.log(res)

To:

if (res.ok) {
  res.json().then((value) => {
    console.log(value)
  });

References:

If I misunderstood your question and this was not the direction you want, I apologize.

I was able to figure it out. All I needed to do to get the sheet id was to use ```res.json().then(response => console.log(response.id)```. — Intelligent, Jun 26 '19 at 13:36
@Intelligent Thank you for replying. I'm glad your issue was resolved. From your latest question and answer, I thought that this modification might be a hint for resolution of your issue. https://stackoverflow.com/q/54697079 — Tanaike, Jun 26 '19 at 22:44

score 1 · Answer 2 · answered Jun 26 '19 at 11:44

You need to do 2 changes to obtain the URL from the file:

1) You have to add the webViewLink attribute [1] between your fields in the search parameters: url.search = new URLSearchParams({ uploadType: 'multipart', fields: 'id,name,kind,webViewLink' });

This attribute is the link to open the file in the web.

2) The response body you get is a ReadableStream, you need to convert it to a json object that you can manipulate. This can be done using the json() function [2] to the response you’re getting, which will return the response body parsed to a Json object with the attributes: id, webViewLink, etc.

I tweaked and tested your code and worked as expected, showing the URL for the newly created file in the alert message:

let metadata = {
name: excelFileName,
mimeType: "application/vnd.google-apps.spreadsheet",
};
let form = new FormData();
form.append('metadata', new Blob([JSON.stringify(metadata)], { type: 'application/json' }));
form.append('file', excelFile);
let url = new URL('https://www.googleapis.com/upload/drive/v3/files');
url.search = new URLSearchParams({ uploadType: 'multipart', fields: 'id,name,kind,webViewLink' });
try {

    const res = await fetch(url, {
        method: 'POST',
        headers: new Headers({ 'Authorization': 'Bearer ' + gapi.auth.getToken().access_token}),
        body: form
    });

    //Convert response to json
    const resJson = await res.json();

    if (res.ok) {
        console.log(gapi);
        console.log(res);
        console.log(resJson);
        alert(`"${excelFileName}" has successfully saved to your google drive!: ` + resJson.webViewLink);
    } else {
        console.error(`Encounter a problem saving excel file, ${excelFileName}, to google drive:` + res.Json);
    }
} catch (ex) {
    console.error(`Oops excel file for "${excelFileName}" wasn't saved. ${ex}`)
}

All the code above inside a sync function of course, to be able to use the await statement.

[1] https://developers.google.com/drive/api/v3/reference/files#resource

[2] https://developer.mozilla.org/en-US/docs/Web/API/Body/json

How to get Google Sheet Id after sheet creation via API?

2 Answers2

Modification points:

Modified script:

References: