cancel
Showing results for 
Search instead for 
Did you mean: 

Retrieve data via REST API and post it to a Google Sheet

Hi

 

I am trying to build something for work internally so we can get the data from our time tracker into our Google Sheet report.

Unfortunately I have next to zero knowledge about this.. After a few days of Google research I came this far:

 

function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Clockodo API')
      .addItem('pull data','Clockodo')
      .addToUi();
}
function Clockodo() {
var options = {};
options.headers = {"Authorization": "Basic " + Utilities.base64Encode('email' + ":" + 'token')};
var response = UrlFetchApp.fetch(options);
var data = response.getContentText();
Logger.log(response.getContentText());
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("api_test");
sheet.getRange(sheet.getLastRow() + 1,1).setValue([data]);
}

The data arrives all in 1 cell like this:

{"paging":{"items_per_page":1000,"current_page":1,"count_pages":1,"count_items":3},"filter":null,"entries":[{"id":46577355,"users_id":132201,"projects_id":1359052,"customers_id":1285331,"services_id":512899,...

And I just cannot figure out how to get this organized into columns or where to add which columns to pull.

If anyone can point me in the right direction I would be very grateful 

 

TuqueroIvantckJ
Contributor
2 REPLIES 2

Most REST APIs are used to interface with a database or perform other actions. Therefore, to interface with a Google Sheet, you first need to find a REST API that returns data. Then you need to find a way to make that data used for a Google Sheet. This can be done using Google Apps Script to execute queries on a Google Sheet.

michaljonny33
Member

Good comment... Excellent work... Click here