Home > database >  Google Apps Script for Yahoo Finance Returns Empty Cell
Google Apps Script for Yahoo Finance Returns Empty Cell

Time:01-14

A previously working solution that was resolved here by @tanaike suddenly returns an empty cell upon execution. I don't get an error message and in the google apps scripts edit page I get "Notice Execution completed".

It looks like it's working in the background but having trouble returning a value to the cell, my guess would be something wrong with the last line that may resolve it?

function pressReleases(code) {
  var url = 'https://finance.yahoo.com/quote/'   code   '/press-releases'
  var html = UrlFetchApp.fetch(url).getContentText().match(/root.App.main = ([\s\S\w] ?);\n/);
  if (!html || html.length == 1) return;
  var obj = JSON.parse(html[1].trim());

  // --- I modified the below script.
  const { _cs, _cr } = obj;
  if (!_cs || !_cr) return;
  const key = CryptoJS.algo.PBKDF2.create({ keySize: 8 }).compute(_cs, JSON.parse(_cr)).toString();
  const obj2 = JSON.parse(CryptoJS.enc.Utf8.stringify(CryptoJS.AES.decrypt(obj.context.dispatcher.stores, key)));
  var res = obj2.StreamStore.streams["YFINANCE:"   code   ".mega"].data.stream_items[0].title;
  // ---

  return res || "No value";
}

The CryptoJS code saved as a script in google apps script is here

CodePudding user response:

When I tested this script, at if (!_cs || !_cr) return;, I confirmed that the values of _cs and _cr are undefined. From this result, I understood that recently, the specification of the key for decrypting the data has been changed at the server side. When I saw this thread, I confirmed the same situation. In the thread, I noticed that, in the current stage, the key can be simply retrieved from the HTML data. So, as with the current script, how about the following modification?

Usage:

1. Get crypto-js.

Please access https://cdnjs.cloudflare.com/ajax/libs/crypto-js/4.1.1/crypto-js.min.js. And, copy and paste the script to the script editor of Google Apps Script, and save the script.

2. Modify script.

function pressReleases(code) {
  var url = 'https://finance.yahoo.com/quote/'   code   '/press-releases'
  var html = UrlFetchApp.fetch(url).getContentText().match(/root.App.main = ([\s\S\w] ?);\n/);
  if (!html || html.length == 1) return;
  var obj = JSON.parse(html[1].trim());
  var key = Object.entries(obj).find(([k]) => !["context", "plugins"].includes(k))[1];
  if (!key) return;
  const obj2 = JSON.parse(CryptoJS.enc.Utf8.stringify(CryptoJS.AES.decrypt(obj.context.dispatcher.stores, key)));
  var res = obj2.StreamStore.streams["YFINANCE:"   code   ".mega"].data.stream_items[0].title;

  // console.log(res); // Check the value in the log.
  return res || "No value";
}
  • When this script is run with code = "PGEN", the value of Precigen Provides Pipeline and Corporate Updates at the 41st Annual J.P. Morgan Healthcare Conference is obtained.

Note:

  • If you want to load crypto-js directly, you can also use the following script. But, in this case, the process cost becomes higher than that of the above flow. Please be careful about this.

    const cdnjs = "https://cdnjs.cloudflare.com/ajax/libs/crypto-js/4.1.1/crypto-js.min.js";
    eval(UrlFetchApp.fetch(cdnjs).getContentText());
    
  • I can confirm that this method can be used for the current situation (January 14, 2023). But, when the specification in the data and HTML is changed in the future update on the server side, this script might not be able to be used. Please be careful about this.

Reference:

  • Related