Doing operations on JSON parsed data from Funds360 on Google Sheet

English
1

Hello,

I’m using a function in App Script that scraps quotes from Funds360 and puts it into a Google Sheet.

But the type of quote I get is a string. I need this to be a number so I can make an operation on it because the quote does not take into account the subscription fee of the security.

I tried to do this on Google Sheet directly but can’t manage (I tried VALUE, REGEXREPLACE and TO_PURE_NUMBER in vain).

I wanted to know if there is any possibility to make that operation (which is a simple multiplication) directly in PP in the quote path since PP seems to read the string as a number this time.

I’m unable to modify the function since I did not create it myself but here it is in case (thanks to zeroz and jakwarrior from hardware.fr) :

function immorente() {
  var nom_fonds = "immorente";
  var baseurl = "https://funds360.fefundinfo.com";
  var period = "all";
  var post_url = baseurl + "/opcvm/graphiques/" + nom_fonds + "/quote/period/" + period + "/isSimple/1";
  var headers = {"X-Requested-With": "XMLHttpRequest"};
  var options = {"method": "post", "headers": headers, "followRedirects": false, "muteHttpExceptions": true};
  var response = UrlFetchApp.fetch(post_url, options);
  var html = response.getContentText();
  var searchstring_start = "\"dataProvider\":";
  var debut = html.search(searchstring_start);
  var searchstring_end = ",\"fieldMappings\"";
  var fin = html.search(searchstring_end);
  var liste = "";
  if (debut >= 0) {
    var pos = debut + searchstring_start.length + 1;
    liste = html.substring(pos, fin);
  }
 
  liste = '[' + liste
  var dataAll = JSON.parse(liste);
 
  return dataAll;
}

Thanks for you help

2

Hi!
I am not familiar with Google Sheets but in Excel I often face a similar problem to convert something to a number.
I then fill a cell with the number 1, copy it and then use the “paste special” function. Select all cells with the stubborn content and “paste special → multiply”.

More often than not that solves the problem.
Regards
Stefan

3

Thanks, but it doesn’t work in this case.

I think I tried all methods for Google Sheet. I assume I should do something in the function itself or in PP if possible but don’t know where to look at.

4

What exactly does the string look like?

This line looks incomplete. It should end with a ;, and probably look like liste = '[' + liste + ']';.

5

Well, the script is working and I get values as decimals. Did you try to change the language settings in the Google sheets settings? I use the German one.

Screenshot_20230315_200209_Sheets
Screenshot_20230315_200209_Sheets2280×1080 179 KB

6

What exactly does the string look like?

Capture d’écran 2023-03-15 235240

This line looks incomplete. It should end with a ; , and probably look like liste = '[' + liste + ']'; .

Tried it but the function doesn’t work anymore in this case…

Did you try to change the language settings in the Google sheets settings?

Capture d’écran 2023-03-15 235547

Did it, seems to be working, just need to trick the formula (had to divide by 1000000) but I think it’s gonna work like that, I will check tomorrow if it all goes good in PP ! Thank you

Any tricks to extend this formula automatically to the bottom (in case some lines are being added) instead of pulling it the classic way ? Thanks

7

Double-Click on the blue dot ??

8

Double-Click on the blue dot ??

Thanks !

1 Like