CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Moses
Moses

Reputation: 236

Fetch data to Google Sheets

I have the API request link - http://dataservice.accuweather.com/locations/v1/countries/ASI?apikey=r0wWEqEDfOM8KwWLIIw8ADXHu2JhgyeB

I need to update the data in the google sheet with ID in one column and Country Name in another column in Google Sheets

The JSON output is for the 1st two ID is as follows

[
    {
        "ID": "AF",
        "LocalizedName": "Afghanistan",
        "EnglishName": "Afghanistan"
    },
    {
        "ID": "AM",
        "LocalizedName": "Armenia",
        "EnglishName": "Armenia"
    },
]

Upvotes: 0

Views: 217

Answers (2)

Aresvik
Aresvik

Reputation: 4630

You can achieve this with the ARRAYFORMULA. Try this in any cell in your blank Google sheet:

=arrayformula(split(trim(flatten(split(join(" ",regexreplace(transpose(IMPORTDATA("http://dataservice.accuweather.com/locations/v1/countries/ASI?apikey=r0wWEqEDfOM8KwWLIIw8ADXHu2JhgyeB")),"(\{.*ID.*\:)|(LocalizedName.*)|(EnglishName)|([""\[\]])","")),"}"))),":"))

IMPORTDATA gets your content from http://dataservice.accuweather.com/locations/v1/countries/ASI?apikey=r0wWEqEDfOM8KwWLIIw8ADXHu2JhgyeB

TRANSPOSE transposes it.

REGEXREPLACE removes a few sets of characters.

JOIN merges onto one row.

SPLIT splits into columns by }.

FLATTEN flattens the result into a column.

TRIM gets rid of leading/trailing spaces.

SPLIT splits into columns by :.

ARRAYFORMULA works down the sheet.

Upvotes: 0

grreeenn
grreeenn

Reputation: 2555

In Google Sheets rows are arrays, where each member represents a column value. Your code should look something like this:

const sheet = SpreadsheetApp.openById('yourSpreadsheetId').getSheetByName('yourSheetName');
const data = [
    {
        "ID": "AF",
        "LocalizedName": "Afghanistan",
        "EnglishName": "Afghanistan"
    },
    {
        "ID": "AM",
        "LocalizedName": "Armenia",
        "EnglishName": "Armenia"
    },
]; // replace with your data fetch logic

// collect your row data into array of arrays
const rows = [];
for (const location of data) {
  rows.push([location.ID, location.LocalizedName]);
}

// get range beginning at the first row and first column,
// with number of rows equels to amount of rows, and 2 as a number of cols
// and fill it with your data
sheet.getRange(1, 1, rows.length, 2).setValues(rows);

See docs for SpreadsheetApp, Spreadsheet, Sheet, Sheet.getRange(), Range.setValues()

Upvotes: 1

Related Questions