Hello,
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
David
I could write something but I made a short screenshare for you. If something isnt clear, please let me know:
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?
/spreadsheets/SpreadsheetID/values/a:z:append
Thanks!
Sorry it took me some time to see your response. Happy you got it to work eventually.
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.
Grts!
David
I wonder, what is the raw input of that value? could you show me?
Maybe it requires some additional formatting.
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.
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 makemarket.io 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. https://www.any-api.com/googleapis_com/sheets/docs/spreadsheets/sheets_spreadsheets_values_append
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:
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 :).
