-
-
Save jrichardsz/70d27fc778119e05500b07edfcc994a5 to your computer and use it in GitHub Desktop.
spreadsheet as json api using Google Apps Script
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* Source: https://gist.github.com/daichan4649/8877801 */ | |
| function doGet(e) { | |
| //tab name in sheet | |
| var sheetName = "Sheet 1"; | |
| //key in url of document | |
| var sheetId = "1234..."; | |
| var book = SpreadsheetApp.openById(sheetId); | |
| var sheet = book.getSheetByName(sheetName); | |
| var json = convertSheet2JsonText(sheet); | |
| return ContentService | |
| .createTextOutput(JSON.stringify(json)) | |
| .setMimeType(ContentService.MimeType.JSON); | |
| } | |
| function convertSheet2JsonText(sheet) { | |
| // first line(title) | |
| var colStartIndex = 1; | |
| var rowNum = 1; | |
| var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn()); | |
| var firstRowValues = firstRange.getValues(); | |
| var titleColumns = firstRowValues[0]; | |
| // after the second line(data) | |
| var lastRow = sheet.getLastRow(); | |
| var rowValues = []; | |
| for(var rowIndex=2; rowIndex<=lastRow; rowIndex++) { | |
| var colStartIndex = 1; | |
| var rowNum = 1; | |
| var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn()); | |
| var values = range.getValues(); | |
| rowValues.push(values[0]); | |
| } | |
| // create json | |
| var jsonArray = []; | |
| for(var i=0; i<rowValues.length; i++) { | |
| var line = rowValues[i]; | |
| var json = new Object(); | |
| for(var j=0; j<titleColumns.length; j++) { | |
| json[titleColumns[j]] = line[j]; | |
| } | |
| jsonArray.push(json); | |
| } | |
| return jsonArray; | |
| } |
Hi @jrichardsz ,
I know it's quite old but in case someone is interested in a working request with cURL I will provide a solution for your statement:
So you can not get the desired json using curl.
I refer to:
From Curl tool
Perform a request to https://script.google.com/macros/s/abcdefghijk/exec
curl https://script.google.com/macros/s/abcdefghijk/exec
You will get :
<HTML>
<HEAD>
<TITLE>Moved Temporarily</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<H1>Moved Temporarily</H1>
The document has moved <A HREF="https://script.googleusercontent.com/macros/echo?user_content_key=kjggttrfdt&lib=eruurweybua">here</A>.
</BODY>
</HTML>
So you can not get the desired json using curl.
Solution:
Instead of using
curl https://script.google.com/macros/s/abcdefghijk/exec
use the -L parameter to tell curl to follow redirects.
Use this
curl -L https://script.google.com/macros/s/abcdefghijk/exec
That's all. Best regards
Sven
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Steps :
Note:
https://trevorfox.com/2015/03/rest-api-with-google-apps-script/
From Browser
When a request is performed using browser to url https://script.google.com/macros/s/abcdefghijk/exec, Google Apps Script Web Apps return 302 status code and a header "Location" with new URL, something like :
https://script.googleusercontent.com/a/macros/utec.edu.pe/echo?user_content_key=145safdasjdafsdtreh&lib=ksdfsdry
Browser manage this behavior and show the desired json.
From Curl tool
Perform a request to https://script.google.com/macros/s/abcdefghijk/exec
You will get :
So you can not get the desired json using curl.
From some programming language
Perform a request to https://script.google.com/macros/s/abcdefghijk/exec . Oddly the response code is not a 302 as in browser, is 200. I dont know why, but fortunately, the immediate response is the desired json :D