I have created a Google Spreadsheet with five columns;
Once the user filled the values in the first three columns, It has to call a 3rd party API and fill the value(response) in the forth and fifth columns.
Is it possible to write a code in Google Spreadsheet for API call? Is it possible to call and get response from external API in Google Spreadsheet?
There's a way to make API calls and have the results go into a spreadsheet - the only way I know to do it is create/open the target spreadsheet, go to tools and then Script editor, and use this as a bound script:
function Maestro() {
var ss = SpreadsheetApp.getActiveSpreadsheet(); //get active spreadsheet (bound to this script)
var sheet = ss.getSheetByName('mae'); //The name of the sheet tab where you are sending the info
var apiCall = 'getUpcomingConference';
var apiKey = '_____key here______';
var apiToken = '______security token______';
var url = 'http://myaccount.maestroconference.com/_access/' + apiCall +"?customer=" + apiKey + "&key=" + apiToken; //api endpoint as a string
var response = UrlFetchApp.fetch(url); // get api endpoint
var json = response.getContentText(); // get the response content as text
var mae = JSON.parse(json); //parse text into json
Logger.log(mae); //log data to logger
var stats=[]; //create empty array to hold data points
var date = new Date(); //create new date for timestamp
//The number in brackets refers to which instance we are looking at - soonest upcoming call is [0], next after that is [1], etc.
stats.push(date); //timestamp
stats.push(mae.value.conference[0].name);
stats.push(mae.value.conference[0].scheduledStartTime);
stats.push(mae.value.conference[0].UID);
//append the stats array to the active sheet
sheet.appendRow(stats);
}
It needs a little interface work but functions! It takes info from an API call and puts it in a spreadsheet.
I've recently come accross the same requirement to read the rows of the sheet and send the data in request and record the response. I thought I would share what I worked out after googling a bit...
function testing_this() {
var data = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
for (row in data) {
Logger.log(data[row]);
var row = data[row]
var options = {
'method': 'post',
'payload': { email:row[1]}
};
// sending to API. for example:
UrlFetchApp.fetch('https://your-rest-api-url/v1/customers/', options);
}
}
If you want to get the data in the sheet you should use the function:
var response = UrlFetchApp.getRequest("http://your-api-url/");
for(data in response) {
var respData = response[data];
// do whatever u want to do with this data...
}
Hope it is useful to you all who are facing similar requirement as above.
I've posted this script in github if you want to fork/pull...
https://github.com/joshiparthin/gsheetScriptExperiments/blob/master/readAndSendToApi.js
Cheers,
Parth
I can only echo the answer recommending using the powersheets.app extension for Google Sheets.
I had a long list of text paragraphs to translate from one language to another. Something like "in cell A2 is the original text", "in cell B2 I want an automated translation", a thousand times.
Deepl.com provides APIs for free - up to a certain volume - and calling them from Google Sheets with the powersheets extension was a snap:
=JSONPATH(API(CONCATENATE("https://api-free.deepl.com/v2/translate?text=";ENCODEURL(A2);"&target_lang=DE&auth_key=xxxMyDeeplKEYxxx"));"translations.0.text”)
For simple requests you can use IMPORTDATA.
Example query to a JSON API =IMPORTDATA("https://jsonplaceholder.typicode.com/todos/1")
Visual example of what you'll get with IMPORTDATA
If you need something more advanced, I found the extension KPIBees. You simply download it from the marketplace and you're ready to make queries. It has a variety of integrations including Google (Ads, Analytics, Search Console), Facebook Ads, CSV, JSON and much more.
I tried JSON and it works perfectly. It supports request headers (useful for APIs with authentication) and it allows you to choose among different display strategies based on how you want your data to be in your sheet.
Using the ImportData function with https://github.com/bradjasper/ImportJSON this custom functions (read the readme for more info)
