I have a document that pulls in data via a script from another site. The API the data pulls from doesn't permit any historical data queries, but what I'd like to do is every time I refresh my document and it pulls in fresh data, is to keep a record of that data for x days or x amounts of refreshes.
So, an example document:
I manually refresh the sheet and columns A and B get populated with say, share prices for companies. What I'd like is for columns DEF to keep track of the prices and to then get overwritten as the prices update, such that there's only ever a trailing history n intervals long (3 intervals in the example above.). Else it can just insert the price into column D and shift everything to the right to infinity.
Another issue I have is the share prices come into the document in random orders, so any record would have to query the ID and dynamically move the records to match the new row where the ID is located, and also account for new ID's appearing and keeping track of them too.
Bit of a long shot, would probably need a custom script written but hey - worth asking anyway.
So, an example document:
I manually refresh the sheet and columns A and B get populated with say, share prices for companies. What I'd like is for columns DEF to keep track of the prices and to then get overwritten as the prices update, such that there's only ever a trailing history n intervals long (3 intervals in the example above.). Else it can just insert the price into column D and shift everything to the right to infinity.
Another issue I have is the share prices come into the document in random orders, so any record would have to query the ID and dynamically move the records to match the new row where the ID is located, and also account for new ID's appearing and keeping track of them too.
Bit of a long shot, would probably need a custom script written but hey - worth asking anyway.
Last edited:
