Hello All,
I appreciate there are several posts by now on this subject, but none of them seems to be of help.
I put together a sample blueprint to simulate an HTTP request from an internal API, which need to be read, manipulated, then stored back into a GSheet.
Ideally I want to do multiple rows at once, to limit time, resources and credit consumption. But I cannot arrive to make a proper request.
I arrived to build my chunks in a text format as [“val1-0”,“val2-0”,“now”],[“val1-1”,“val2-1”,“now”],
The challenge is that I don’t know how many rows already exists in the GSheet, so I need to append at the very last, no matter what.
In this post here there is a possible solution.
It mentions a /spreadsheets/spreadsheetID/values/a:c:append entry point that fails with Invalid collection in parameter 'rows'. error.
In any case, the other major challenge is that I don’t know how to refer an exact sheet. Setting the GID via the querystring doesn’t work.
The solution proposed here suggest to use this
/spreadsheets/id/values:batchUpdate entrypoint which return a 400: INVALID_ARGUMENT - Invalid data[0]: Unable to parse range: 'A1:C2.
Yet the same problem as how to specify the destination sheet.
Adding an “includeValuesInResponde”: false into the body return the following other error Unknown name "includeValuesInResponde": Cannot find field.
The sole way I found so far is avoid chunking and pushing one line by one line. But it’s not efficien.
Any help?
blueprint.json (32.8 KB)
Welcome to the Make community!
Have you tried the “Bulk Add Rows” module instead of manually building the API query?
If you need further assistance, please provide the following:
1. Relevant Screenshots
Please share screenshots of your scenario, any error messages, relevant module fields, and filters in question? It would really help other community members to see what you’re looking at.
You can upload images here using the Upload icon in the text editor:
2. Output Bundles of Modules
Please provide the output bundles of the modules by running the scenario (or get from the scenario History tab), then click the white speech bubble on the top-right of each module and select “Download input/output bundles”.
A. Upload as Text File
Save each bundle contents in your text editor as a bundle.txt file, and upload it here into this discussion thread.
B. Insert as Formatted Code Block
If you are unable to upload files on this forum, alternatively you can paste the formatted bundles.
These are the two ways to format text so that it won’t be modified by the forum:
-
Method 1: Type code block manually
Add three backticks ``` before and after the content/bundle, like this:
```
content goes here
```
-
Method 2. Highlight and click the format button in the editor
Providing the input/output bundles will allow others to replicate what is going on in the scenario even if they do not use the external service.
Following these steps will allow others to assist you here. Thanks!
@samliew thanks. Yes, I’ve seen the bulk Add row, but I’ve the same challenge of knowing in advance how many rows I have, and from where these have to start?
For the sake of this experiment, I decided to concentrate on getting the bulk updated to start with, and to figure out later the location. So the workbook and sheet are empty as of now.
So let me try to add some more inputs. There are no filters anywhere.
Attached there is the Text aggregator output that I need to append into a google sheet.
Text_Integration_bundle.txt (149 Bytes)
Using the Bulk Update Rows, I get a Invalid collection in parameter 'rows'. error message.
I tried to format the row input as per the following attempts (I paste just one screenshot as they are both the same).
But none of the two have worked.
I’m not if this is indicative, but in the execution history, downloading the input results in an empty bundle content. Just a .
Frankly. I’m getting lost. The help doesn’t provide a valuable example to follow.
Furthermore, at this stage, I can’t understand whether I need to treat the output of my text aggregator with a further repeater to add items into an iterator. Just an iterator … or leave things as it is.
A quick attempt to add an iterator didn’t change the result.
Any help?
@gaboom I’ve been facing the same issue of yours, but although your suggestions in here I cannot make it. Now I’m getting a 502 error continuously.
Any chance you can share your blueprint?
That would really help.
Thanks
ok, using the API tester from Google was instrumental to find the right combination of values.
The working blueprint is attached for anybody struggling.
blueprint (3).json (21.1 KB)
That’s only one part, you’ll also need an array aggregator.
Aggregators
Every result (item/record) from iterator/list/search/match modules will output a bundle. This can result in multiple bundles, which then trigger multiple operations in future modules (one operation per bundle). To “combine” multiple bundles into a single variable, you’ll need to use an aggregator of some sort.
Aggregators are modules that accumulate multiple bundles into one single bundle. An example of a commonly-used aggregator module is the Array aggregator module.
Array Aggregator – mapping multiple bundles into a complex field
The Array Aggregator module is very powerful because it allows you to build a complex array of collections for a later module’s field to map multiple items (collections) to it.
This is done using the “Target structure type” of an Array Aggregator module.
Here is an example:
As you can see, the “Map” toggle on complex fields are used when you have an array. You can easily build an array variable to map to a future module’s field, by using an Array Aggregator module and select the “Target Structure Type” as the future module’s field you have mapped the array into.
Hope this helps! Let me know if there are any further questions or issues.
— @samliew
P.S.: Investing some effort into the Make Academy will save you lots of time and frustration using Make.
