Importjson script multiple imports

#1

How to do?
Case:
Import data from external source where response data is delivered in Json.
Dataset is large and will be delivered in chunks
A chunk is 500 objects and part of the response of the first chunk will be an id for the next chunk. This means that the request for the second chunk can only be performed when data from the first chunk has been fetched.

I want the imported chunks to be listed after each other
Chunk 1’s data
Chunk 2’s data
Etc

Anyone?

Extra info:
I found out how to get the first chunk by updating a field in google sheet and then onchange event trigger to run a script which is actually just inserting a new formula in a cell like =importjson(xxx?123) - 123 is just a incremented value. The importjson will then run.

#2

Does ImportHTML only allow 500 rows per import or is there something else here?

I was thinking of using an Array_Constrain or Query, then let’s say verify if cell A501 is not empty (so the first batch has been completed), then push the formula to cell A502 for it to continue the next batch.

#3

@ThinhDinh
It is the provider that can only send 500 objects at a time. I’m using importjson.

I only input a importjson statement in A2 cell. Then cell A2-A501 is filled with data.

I’m unsure how to use the array_constrain or query to get the value to A502. I must look into that

#4

Oh sorry I meant to write ImportJSON.

So when you have the second import it will automatically detect the next 500 items?

#5

@ThinhDinh well, in the first import you will find information about the next import. Here the next object id will be given. But you will have do do a new request. And data should be inserted in line 502–>

#6

So would a flow like this work?

A script triggers applying a formula to cell A2, first 500 rows are returned.

Check if A501 is empty or not (probably give a sleep function of 30 seconds or something). If not empty, get new object ID, apply new function to A502 and so on.

#7

@ThinhDinh jep. Something like that. I would like to avoid sleep thing as I might need to return maybe 5000 objects.
And actually would like to avoid loading the whole dataset again when sheets opens

1 Like