Skip to content

Instantly share code, notes, and snippets.

@dhlavaty
Last active January 1, 2023 00:27
Show Gist options
  • Save dhlavaty/9cb9f50aed62320329636e805e654eae to your computer and use it in GitHub Desktop.
Save dhlavaty/9cb9f50aed62320329636e805e654eae to your computer and use it in GitHub Desktop.
Allows to make simple unauthenticated read-only GET requests to Google Spreadsheet
{
"timeZone": "Europe/Paris",
"dependencies": {
"enabledAdvancedServices": [{
"userSymbol": "Sheets",
"serviceId": "sheets",
"version": "v4"
}]
},
"webapp": {
"access": "ANYONE_ANONYMOUS",
"executeAs": "USER_DEPLOYING"
},
"exceptionLogging": "STACKDRIVER",
"oauthScopes": ["https://www.googleapis.com/auth/spreadsheets.currentonly"]
}
// documentation says apps-script is javascript1.6 + some parts of 1.7 and 1.8,
// but does not say what exactly. so we try to be conservative here.
// we write javascript 1.6, and use the JSON.stringify method from the "later",
// parts because it is documented that it works in apps-script.
function makeJSONResponse(data) {
var text = JSON.stringify(data);
return ContentService.createTextOutput(text).setMimeType(ContentService.MimeType.JSON);
}
function makeSuccessResponse(data) {
var obj = {
data: data
};
return makeJSONResponse(obj);
}
function makeErrorResponse(message) {
var obj = {
error: message,
};
return makeJSONResponse(obj);
}
function cleanData(data) {
function cleanItem(item) {
return item.toString();
}
function cleanRow(row) {
return row.map(cleanItem);
}
return data.map(cleanRow);
}
function doGet(e) {
var sheetName = e.parameter.sheet;
if (sheetName == null) {
return makeErrorResponse('missing sheet name');
}
var s = SpreadsheetApp.getActiveSpreadsheet();
if (s == null) {
return makeErrorResponse('unable to access active spreadsheet');
}
var sheet = s.getSheetByName(sheetName);
if (sheet == null) {
return makeErrorResponse('no sheet with name:' + sheetName);
}
var data = cleanData(sheet.getDataRange().getValues());
return makeSuccessResponse(data);
}

Howto setup Google Spreadsheet

  1. Open Spreadsheet you want to allow access to
  2. Go to Tools -> Script Editor
  3. Enable google sheets api:
    1. Open in the menu Resources / Advanced Google Services ...
    2. Find Google Sheets in the list and enable it
    3. Finish the dialog
  4. In Script Editor make sure View -> Show manifest file is checked
  5. Make sure appscript.json file looks like our ./appscript.json
  6. Make sure Code.gs file looks like our ./code.gs
  7. Publish script by opening Publish -> Deploy as web app
    1. Set Project version to New
    2. Set Execute the app as to Me
    3. Set Who has access to the app to Anyone, even anonymous
    4. Hit Update
    5. (On first try) You have to Review permissions and Allow permissions. Follow on screen instructions.
  8. You will get an URL - like: https://script.google.com/macros/s/xxxxx-yyyyy/exec
  9. Use this with query param ?sheet=header, where header is Sheet Tab name (do not use spaces in Tab names)

Example:

https://script.google.com/macros/s/xxxxx-yyyyy/exec?sheet=Sheet1

-- many thanx to @gabor

@coolaj86
Copy link

coolaj86 commented Oct 19, 2021

And for those that need a curl example:

URL looks like this:

https://docs.google.com/spreadsheets/d/${my_key}/export?format=csv&usp=sharing

Working Example:

https://docs.google.com/spreadsheets/d/1KdNsc63pk0QRerWDPcIL9cMnGQlG-9Ue9Jlf0PAAA34/export?format=csv&usp=sharing

curl looks like this:

curl -fsSL "https://docs.google.com/spreadsheets/d/${my_key}/export?format=csv&usp=sharing"

Working Example:

curl -fsSL "https://docs.google.com/spreadsheets/d/1KdNsc63pk0QRerWDPcIL9cMnGQlG-9Ue9Jlf0PAAA34/export?format=csv&usp=sharing"

Key is taken from the "Anyone with a link can access" link (like https://docs.google.com/spreadsheets/d/1KdNsc63pk0QRerWDPcIL9cMnGQlG-9Ue9Jlf0PAAA34/edit?usp=sharing)

Re: https://gist.github.com/dhlavaty/6121814#gistcomment-3930810

@dhlavaty
Copy link
Author

I'm not able to test it now, but many thanks @coolaj86 for sharing it it us.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment