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

Question

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.
}

About missing the data for all the contracts and prices, when I saw the data of your provided URL, I cannot understand your expected values. So, can I ask you about your expected values? — Tanaike
– Tanaike, Commented Jun 6, 2022 at 12:02
In the URL I need what is called "Ric" and "Values". For example MALc3 and 2721.00 — user31445
– user31445, Commented Jun 6, 2022 at 12:11
Thank you for replying. It seems that such the values are existing in an array of Rows. So, can I ask you about your expected situation? For example, you want to retrieve only the values of Rows? Or, you want to retrieve other situation? — Tanaike
– Tanaike, Commented Jun 6, 2022 at 12:14
Hi, can you visually clarify the expected behavior? A screenshot showing the desired output could be useful. Title, Strapline, etc would not be exported? — Iamblichus
– Iamblichus, Commented Jun 6, 2022 at 12:33

Tanaike · Accepted Answer · 2022-06-06 12:36:16Z

2

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.

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.

answered Jun 6, 2022 at 12:36

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tanaike Over a year ago

@user31445 Thank you for replying and testing it. I'm glad your issue was resolved. Thank you, too.

Collectives™ on Stack Overflow

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

1 Answer 1

Modified script:

Testing:

Note:

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modified script:

Testing:

Note:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related