Challenges with Google Sheets "Make an API call"

[gaboom]

Hi Makers

I guess the TL;DR version is: I need to automate invoicing for sales on a marketplace into my accounting system.

The long version

1. The “Make a Request” http call gets data from the marketplace in html
2. The combination of the iterator and the aggregator massages the data into bundles.

The challenge I have is getting this into Google Sheets

I have tried various URLs viz the Google Sheets API like

• batchUpdate
• append

In most cases the sandbox in the API document seems to work but despite trying various Body’s and variations of the URL, nothing seems to work.

Most recently, I have been tinkering around with append and the error is The operation failed with an error. 400: INVALID_ARGUMENT - Unable to parse range:

Any pointers would be VERY helpful

[Philippe_Billet]

Hello gaboom,

Could you, please, add the following screenshots:

• output data of the text aggregator
• data mapping of the “Make an API call” module
• detailed error of the “Make an API call” module.

Philippe

[gaboom]

Hi @Philippe_Billet,

• Output data of the aggregator (added the config of it too)
  
  
  
  
  
• data mapping of the “Make an API call” module
  There is no other mapping than the screengrab in the previous post
• detailed error of the “Make an API call” module
  Does this work?
  
  

Cheers,
G

[samliew]

Welcome to the Make community!

1. Scenario blueprint

Please export the scenario blueprint file to allow others to view the mappings and settings. At the bottom of the scenario editor, you can click on the three dots to find the Export Blueprint menu item.

^{(Note: Exporting your scenario will not include private information or keys to your connections)}

Uploading it here will look like this:

blueprint.json (12.3 KB)

2. And most importantly, Output bundles

Please provide the output bundles of the modules [20] by running the scenario, then click the white speech bubble on the top-right of module [20], save the contents as a text file, and upload it here into this discussion thread:

Providing the output bundles will allow others to replicate what is going on in the scenario even if they do not use the external service(s).

Following these steps will allow others to assist you here. Thanks!

[Philippe_Billet]

It means that the JSON you sent is not valid… The data you sent is not well formatted.

If I well understand your goal, you want to add a row into an existing Google Sheets, I think that the action module “Add a row” could be easier to implement.

PBI

[gaboom]

Thanks @Philippe_Billet.

Will look at the formatting of the JSON.

Add a row would be easier to implement but would cost more in terms of operations. I would have around 2000 rows to add per month and would save operations using the “Make an API call” method…

Cheers,
G

[Philippe_Billet]

Hi gaboom,

I looked at your problem a little bit more precisely, maybe you should use:
{
“value”: [20.text]
}
instead of:
{
value: [20.text]
}
in the body part of the Google Sheets module.

PBI

[gaboom]

So, I finally figured it out. And although I haven’t completed the solution, I solved the Google sheet thing… Thanks @Philippe_Billet and @samliew for trying to help on this!

It turns out some of the settings in the “Make an API Call” were incorrect in the example I was following at Insert multiple rows in Google Spreadsheets from HTTP request that give xml of products

So, these settings worked for me

• URL = Spreadsheets/SpreadsheetID/values/a:z where Spreadsheet refers to the https://docs.google.com/spreadsheets/d/**SpreadsheetID**/edit#gid=SheetID.
• Method = PUT not POST per the previous example
• valueInputOption = User_Entered but will be fiddling with this going further
• Body is currently {values: [[“Order ID”,“Date”,“SKU”],{{20.text}}]} though am sure that it may not have overly influenced the result/solution to this part of the puzzle.

Thanks to this video for steering me to the final solution.

[Michaela]

Hi @gaboom

I want to jump in to quickly say congrats on figuring out the Google Sheet issue

Also, thanks a bunch for sharing that helpful video with the community – it’s awesome to know there are resources like that to keep our users on the right track.