Skip to content

Instantly share code, notes, and snippets.

@dalmo3
Last active May 28, 2022 01:02
Show Gist options
  • Save dalmo3/48baafa51f93880450893c4aa7a99240 to your computer and use it in GitHub Desktop.
Save dalmo3/48baafa51f93880450893c4aa7a99240 to your computer and use it in GitHub Desktop.
Google Apps Script for fetching JSON from Coda
// CodaAPI reference: https://script.google.com/macros/library/d/15IQuWOk8MqT50FDWomh57UqWGH23gjsWVWYFms3ton6L-UHmefYHS9Vl/5
// CodaAPI source: https://script.google.com/d/15IQuWOk8MqT50FDWomh57UqWGH23gjsWVWYFms3ton6L-UHmefYHS9Vl/edit
// Setup and global variables
// first, generate an API token at https://coda.io/account
// then define apiKey at File > Project Properties > Script
var apiKey = PropertiesService.getScriptProperties().getProperty('apiKey');
//Logger.log(apiKey);
CodaAPI.authenticate(apiKey); // Replace with your token.
// Functions
function doGet(e) {
var params = e.parameters;
Logger.log(params);
var docId = params.codaDocId[0];
var tableId = params.codaTableId[0];
var rowId = params.codaRowId[0];
var columnId = params.codaColumnId[0];
// delete incoming params so when outgoing query string is rebuild they're not included
delete params.codaDocId;
delete params.codaTableId;
delete params.codaRowId;
delete params.codaColumnId;
var method = params.method[0];
delete params.method;
var options = {
method: method,
};
// check headers
if (params.headers[0]) {
var reqHeaders = JSON.parse(params.headers[0]); //needs to parse in order to check for Content-type
Logger.log(reqHeaders);
Logger.log(reqHeaders['Content-type']);
options.headers = reqHeaders;
// GAS needs 'Content-type' as separate parameter: see https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app#fetch(String,Object)
if (reqHeaders['Content-type']) {
options.contentType = reqHeaders['Content-type'];
// delete reqHeaders['Content-type'];
Logger.log(reqHeaders);
}
}
delete params.headers;
// options.muteHttpExceptions: true //debug
// check body
if ((method === 'POST') || (method === 'PUT')) {
var reqBody = JSON.parse(params.body[0]);
// var reqBody = params.body[0];
Logger.log(reqBody);
options.payload = (params.stringify[0]) ? JSON.stringify(reqBody) : reqBody;
Logger.log(options.payload);
}
if (method === 'GET'){
// options.method = 'get';
}
delete params.stringify;
delete params.body;
var reqUrl = params.codaReqUrl[0];
delete params.codaReqUrl;
Logger.log(params); //should include only params necessary for outgoing query
var cleanQuery = qs.stringify(params);
var sep = Object.keys(params).length > 0 ? "&" : "";
Logger.log(reqUrl+sep+cleanQuery); // the final url to be queried
var content;
try{
var response = UrlFetchApp.fetch(reqUrl+sep+cleanQuery, options);
content = response.getContentText();
// remove this test to allow returning other content, e.g. html
var regex = /application\/json/;
if (!regex.test(response.getAllHeaders()['Content-Type'])){
content = 'Content is not in json format: ' + response.getHeaders()['Content-Type'];
};
}
catch(e){
content = 'Request error: ' + e;
}
Logger.log(content);
// body format as per Coda API documentation
// https://coda.io/developers/apis/v1beta1#operation/updateRow
var body = {
row: {
cells: [
{column: columnId, value: content}
]
}
};
var row = CodaAPI.updateRow(docId, tableId, rowId, body);
return HtmlService.createHtmlOutput('<script>top.window.close();</script>');
}
@dalmo3
Copy link
Author

dalmo3 commented Aug 27, 2019

This script was meant to be used alongside [this doc]https://coda.io/d/Fetch-JSON-v1_dg4WCTa37F8/_su0fI).

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