Google sheets and pulling HTML data from Torn.

(I don't actually know where I should post this.)

I'm trying to make an automatic calculator in google sheets for my company so that I know exactly how much stock to buy, but I am running into a problem. I can't find the data where my current stock is stored. When I go to inspect element, I can find it but when I view the source, it never actually gives me anything close to it. Can anyone help?

you cant scrape pages automatically, its against the rules

you can however use your api key

https://api.torn.com/company/?selections=stock&key=APIKEY

your API can be used to load data, and using Json you can load it pretty easily into google docs

Took me a hot minute to figure all of it out (never even heard of JSON before), but now I got what I wanted. Thank you.

Edit: I am actually having trouble still. The data won't update. Like I tested it with my networth and wallet, but whenever I move money around, the cell never changes even after refreshing. The only way I can get it to update is when I edit the cell itself.

For certain pieces (such as Networth) they only update at specific times, once per day. So NW will update at midnight TCT. For company stocks it would likely update at ~18:00 TCT (end of company day).

I'm not sure if this is the issue you're running into (I'm not totally certain what you mean when you say "edit the cell itself", i.e. if you mean manually entering the value or simply clicking on the cell and having it auto-update), but if so there isn't any way to fix this as this information is only tracked by the API on a once/day interval.

Everything in the api updates live with the exception of stocks & bazaars

Really? That's good to know. I thought that the API updated the same as the profile statistics info.

The data on the API page updates, yes, but not the cells themselves in my sheet. To force them to update I need to change the formula in them, like delete 1 letter, save, and then put it back. They do end up updating if I leave the sheet long enough and the formulas are reloaded, but I'm needy and I would like it to update quicker, and on command.

It is not a problem of the APIs but google sheets itself. Google "cache" the datas of a function till its parameters changes (i.e. when you delete and rewrite 1 letter or when you load again the sheet).

To have an auto-updated function you can change the parameters with a script. For example you can put your API key (it is one of the parameters of the JSON importing function) in another cell and refer in the function to it. Then run a script that delete and rewrite the API key.

Example:

you could put your API key in the cell A1.
In this case the function will be:
=ImportJSON("https://api.torn.com/user/?selections=inventory&key="&$A$1&"")

"&$A$1&" is the referral for the cell A1

Then, you can write a little script to autoupdated a counter:

function incrementDummyValueToForceUpdate() {
var cell = SpreadsheetApp.getActive().getRange("dummyValueToForceUpdate");
cell.setValue(cell.getValue() + 1);
}

and when the counter change you can change the content of the cell where it is your parameter:

=if(isodd(Sheet1!A1),C1)

 

Note: Google spreadsheets DOESN'T permit to bind your script to a timed function (for example NOW()). You can do this via the google timed-triggers (and, for example, get an API call each minute, that automatically will fetch the datas).

Tell me if what I wrote is understandable.

Yeah I understand. Thank you lots.