CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user31445
user31445

Reputation: 47

Scrape data from website using Google apps script and paste to Google sheet

I adapted some code that I found that retrieves data from a url (https://www.lme.com/api/trading-data/day-delayed?datasourceId=9ca4cc7d-4812-479f-8068-23a9053921c1) in json format and pastes it to a Google Sheet.

It seems to be retrieving all the headers, but it is missing the data for all the contracts and prices (you can see it if you open the url). I am wondering what I am doing wrong. The code is below:

function getJSON() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheet = ss.getActiveSheet();
  var url="https://www.lme.com/api/trading-data/day-delayed?datasourceId=9ca4cc7d-4812-479f-8068-23a9053921c1"; // JSON URL
  var response = UrlFetchApp.fetch(url);
  var dataAll = JSON.parse(response.getContentText());
  var rows = [Object.keys(dataAll)]; // Retrieve headers.
  var temp = [];
  for (var i = 0; i < rows[0].length; i++) {
    temp.push(dataAll[rows[0][i]]); // Retrieve values.
  }
  rows.push(temp);
  sheet.getRange(1,1,rows.length,rows[0].length).setValues(rows); // Put values to Spreadsheet.
}

Upvotes: 1

Views: 2197

Answers (1)

Tanaike
Tanaike

Reputation: 201643

In your situation, how about the following modified script?

Modified script:

function getJSON() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var url = "https://www.lme.com/api/trading-data/day-delayed?datasourceId=9ca4cc7d-4812-479f-8068-23a9053921c1"; // JSON URL
  var response = UrlFetchApp.fetch(url);
  var dataAll = JSON.parse(response.getContentText());
  var rows = dataAll.Rows;
  var keys = Object.keys(rows[0]);
  var values = [keys, ...rows.map(o => keys.map(k => o[k] && Array.isArray(o[k]) ? o[k][0] : o[k]))];
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
  • When this script is run, the values of Rows are retrieved and put to the sheet.

Testing:

When this script is run, the following result is obtained.

enter image description here

Note:

  • From Yes the value of the Rows would be sufficient, I proposed the above modification. But, if you want to add more values, please tell me.

Upvotes: 2

Related Questions