Programatically create a Google Sheet and get it's JSON URL

Question

I have a Google Apps Script that will copy a spreadsheet from one team drive to another team drive and then publish the new spreadsheet. I want to get the published URL of a specific sheet or get the JSON URL for the sheet.

This is the working code I have so far:

var dFolderID = "destination folder ID";
var sFileID = "source spreadsheet ID";

// get the destination folder
var dFolder = DriveApp.getFolderById(dFolderID);

// get the source file
var sFile = DriveApp.getFileById(sFileID);

// name of the new file
var nFileName = "test - " + Utilities.formatDate(new Date(), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "yyyyMMddHHmmss");

// make a copy of the spreadsheet
var nFile = sFile.makeCopy(nFileName, dFolder);

// get the ID of the new spreadsheet
var nFileID = nFile.getId();

// publish it
var publishResponse = Drive.Revisions.update({
    published: true,
    publishedOutsideDomain: false,
    publishAuto: true
}, nFileID, 1);

At this point, the new spreadsheet has been created, I know the spreadsheets' ID/key, and it is published.

From what I can tell, there is no way to get the published URL because publishResponse.publishedLink was depreciated.

So then I thought I could use this URL to get the JSON of a sheet:

https://spreadsheets.google.com/feeds/list/SPREADSHEET_ID/WORKSHEET_ID/public/values?alt=json

But for this to work I have to know the WORKSHEET_ID. I know I can use the sheet index like 1, 2, 3, etc... but that will break if the sheet order changes.

From what I can tell the only way to get the WORKSHEET_ID is by checking this URL:

https://spreadsheets.google.com/feeds/worksheets/SPREADSHEET_ID/private/full

If I load this URL in my browser I get an XML response and can see the WORKSHEET_ID in it. However, if I try to load this URL from my Google Apps Script it does not work. It looks like it redirects to Google's login page.

var privateFullUrl = Utilities.formatString("http://spreadsheets.google.com/feeds/worksheets/%s/public/full", nFileID);

var privateFullData = UrlFetchApp.fetch(privateFullUrl, {
    contentType : 'application/xml; charset=utf-8'
});

Logger.log(privateFullData.getContentText());

I also checked SpreadsheetApp.openById(nFileID).getSheetByName("..").getSheetId() and SpreadsheetApp.openById(nFileID).getSheetByName("..").getRange(1,1).getDataSourceUrl() but both of those return a numerical sheet ID instead of the alpha-numeric one that I need for https://spreadsheets.google.com/feeds/list/SPREADSHEET_ID/WORKSHEET_ID/public/values?alt=json.

Is there anyway to either get the published URL or the JSON URL for a specific sheet?

Edit

I forgot to mention this is Enterprise G-Suite and sharing outside the company domain is blocked.

Have you tried google-query-language? It provides a endpoint /gviz/tq?tqx=out:json for getting json. Also, try sending authorization headers in urlfetchapp to your end point /private/full. The bearer token can be obtained by ScriptApp.getAccessToken(). — TheMaster
I think that @TheMaster 's comment is correct. If the Spreadsheet is published to Web, you can retrieve the JSON data by var res = UrlFetchApp.fetch("https://docs.google.com/spreadsheets/d/###/gviz/tq?gid=0&tqx=out:json");. gid=0 is the sheet ID. ```###`` is the file ID. If the Spreadsheet is not published, please use the access token. — Tanaike
Thanks @TheMaster. If you put ScriptApp.getAccessToken() as an answer I will accept. — IMTheNachoMan

TheMaster TheMaster · Accepted Answer · 2019-06-17T15:15:29

To access private urls, you'd need oauth authorization. You can send the authorization header with the bearer token provided by ScriptApp.getOAuthToken() to bypass full oauth flow.

UrlFetchApp.fetch(url, {headers: {Authorization: 'Bearer ' + ScriptApp.getOAuthToken()}})

To get JSON from sheet, You can also use Google visualization api:

UrlFetchApp.fetch("https://docs.google.com/spreadsheets/d/[SPREADSHEET_ID]/gviz/tq?gid=0&tqx=out:json", 
{headers: {Authorization: 'Bearer ' + ScriptApp.getOAuthToken()}});

or

UrlFetchApp.fetch("https://docs.google.com/spreadsheets/d/[SPREADSHEET_ID]/gviz/tq?gid=0&tqx=out:json&access_token="+ ScriptApp.getOAuthToken())

Programatically create a Google Sheet and get it's JSON URL

2 Answers