How to get 5.000 - 10.000 collections into sheet


This is my scenario:

I will have about 10.000 collections or more

Is there a way to get these collections on rows in Google Sheets? (Or into Make datastores?)
Without using the Iterator, that would be impossible because of the operations it will take.

Many thanks

@makeitfuture @Msquare_Automation - both of you build batch sheet modules - maybe you can help.

I could write something but I made a short screenshare for you. If something isnt clear, please let me know:

1 Like

Well, that’s some great -advanced- stuff. Thanks a lot, I will dive into it asap!

When I’m trying to map the values into the Text aggregator, I’m not able to do it without pointing out “which collection”. Or should I use the map / get function?

I think it’s because my collections are inside an Array:

UPDATE: I got it, I had to add an Iterator before the text aggregator!

Is it possible to specify a certain “sheet” in this link?


Yes, thanks @SebastianMertens for mentioning it. @Davidof90 Here is the product page:


Sorry it took me some time to see your response. Happy you got it to work eventually.

1 Like

The spreadsheet id refers to a sheet so no need to do that.


Thanks! Now I’m looking for a last fix:

When importing numbers, Google Sheets automatically adds an apostrophe (') before each number, really strange. Is there a workaround for that? Changing the format to “numbers” or find&replace does work, but I need something automatically.



I wonder, what is the raw input of that value? could you show me?
Maybe it requires some additional formatting.

1 Like

Do you mean this?
The response from my soap request:

Yeah, so as I can see it is a string, have you tried parsing it as a number using parseNumber ?
That should solve these type of issues.

1 Like

I tried some combinations of parseNumber and formatNumber, but it’s still imported as a string. At some point (when I also switched to “Netherlands” and tried using a comma “,”) some values got recognized as string and some as number, but not all of them. (very strange!)

I found a workaround in sheets:
Sum function does not work:
Using + operator does work:

So I made a helper column to do (x+x-x), from that point, sheets makes a “number” of it:


When using the text aggregator the special characters are not escaped properly. You can use for free the Multi-Row Upload for Google Sheet / Excel, that will create the json structure automatically for your Goolge Make an api call.

Google sheets has an endpoint to append data.
I can’t remember exactly how many records it will let you post but it allows me to post thousands of records with one api call. You will need to create an IML function to format the data before posting. Feel free to reach out if you’d like to hire someone to help create a custom IML function for that purpose.

Hi @EyalGershon, thank you for your video, it’s very clear and it works as expected on Google Sheet. I’m trying to do it on MS Excel, but it doesn’t seem like the “Add rows in worksheet” API documentation exists, they only have “add a row in table”.

Is there a way to do the batch update/add on Excel? Could you please advise? Thank you very much!

FYI - @Durga


Yeah its possible via the make an API call module, the issue is it require 3 or 4 seperate requests:

  1. Creating a session:

    2.Getting the current range (so you know what the last row with data is) using the session ID we got from creating the session under the usid parameter:
  2. Append the rows, using the session ID we got from creating the session under the usid parameter:

  3. Close the session (even though I see this happening in the app, I am not able to reproduce it but the rows are being written.

Notice that when writing the rows you require an array inside the values key (similar to google sheets) but you will have to make sure when writing the rows, the URL needs to set the range of what you want to write.
For example if you are adding 100 rows starting at row 25 you need to set the range to A26:Z126.

Hope it helps :).


@EyalGershon Thank you so much! Your solution works perfectly! Thanks a lot! :smiley:

1 Like