CODEWITHSUNDEEP
functionapigoogle-sheetsimport
M Evans
M Evans

Reputation: 11

importing api into google sheets

I am wanting to use an api to import sunrise and sunset times into a google sheet. I found this api: https://sunrise-sunset.org/api

I am new to using functions and was wondering if someone could point me in the right direction on how exactly to get my desired results.

Cheers

Upvotes: 1

Views: 146

Answers (1)

Tanaike
Tanaike

Reputation: 201428

When I saw your provided URL, it seems that the result values are returned as a JSON data. In this case, when Google Apps Script is used, the JSON data can be easily parsed. So, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet and save the script. When you use this script, please put a custom function like =SAMPLE("https://api.sunrise-sunset.org/json?lat=36.7201600&lng=-4.4203400&formatted=0") to a cell. By this, the script is run and the result value is obtained.

function SAMPLE(url) {
  const res = UrlFetchApp.fetch(url).getContentText();
  const obj = JSON.parse(res);
  const values = ["sunrise", "sunset"].map(e => [e, new Date(obj.results[e])]);
  return values;
}
  • In this sample script, the date string is parsed as the date object. So, please include formatted=0 to the query parameter of the endpoint like https://api.sunrise-sunset.org/json?lat=36.7201600&lng=-4.4203400&formatted=0. When this query parameter is used, it seems that the date string is returned as ISO 8601. So, I used this.

Testing:

When this script is tested, the following result is obtained. In this case, the result value is put as the date object.

enter image description here

Note:

  • When you put =SAMPLE("https://api.sunrise-sunset.org/json?lat=36.7201600&lng=-4.4203400&formatted=0") to a cell, an error occurs, please reopen Spreadsheet and test it again.

  • From I am new to using functions and was wondering if someone could point me in the right direction on how exactly to get my desired results., this is a simple sample script for explaining the script. So please modify this sample script for your actual situation.

References:

Upvotes: 1

Related Questions