Google Sheets charts image into google doc template

[terryble66]

Hello Guys
After looking everywhere i can’t find the way to extract the chart from google sheet to replace a variable in my google doc

Please share the workflow

The workflow is 
1/ Extract image url from Google-sheet : done
2/ manipulate the data to be as images : This question
3/ Replace the defined variable by the images : next step

Describe the issue/error/question

I have published the chart as image

(https://docs.google.com/spreadsheets/d/e/2PACX-1vTYs4384E49QQhU-N5Ql*******1YiToijvILSeGcpt/pubchart?oid=1958854601&format=image)

What is the error message (if any)?

I don’t see how to grab the image maybe as binary (i have not much experience with script )
I tried with HTTP request but get the data as html file

Share the output returned by the last node

The binary output below is an html format not the pictures expected

Thanks for your help

Information on your n8n setup

• n8n version:0.160.0
• Database you’re using (default: SQLite):none
• Running n8n with the execution process [own(default), main]:don’t know
• Running n8n via [Docker, npm, n8n.cloud, desktop app]:desktop app

[marcus]

Hey Thierry,
I created a google sheet with a chart and published it like so.

I made sure it is published as image and that the access is not restricted and I could access it using the http request node.

From your screenshots I can see that your response is of type text/plain instead of type image/png. It seems like either the image url you are using is not correct or that the access is restricted. When I published my chart image I could use the url in the browser to see the image. Does that work for your image url?

[terryble66]

Hello Marcus
Yes when I copy/paste the url in my browser i get the image.
As I’m in entreprise i can only publish it to my organisation

[marcus]

Hi Thierry,
I am also using google workplace. Can you disable this restriction?

I know that google workplace admins can restrict their users to not be able to share ressources outside of their organization.

I just tried using the Http Request node to load the image with restricted access and it works if you enable authentication using your predefined credentials for google sheets.

Can you use your google sheets credentials and try again?

[marcus]

I just saw that you are using n8n v0.160.0. To use predefined credentials you may have to update your n8n instance. We introduced that feature in n8n v0.178.0.

[terryble66]

Marcus, just updated it to version 0.182.0
Gert the same result:

Any idea ?
Thanks

[marcus]

Great to hear that you were able to update.

Can you set the Http Request parameters are shown in my last screenshot.

• Authentication = Predefined Credential Type
• Credential Type = Google Sheets OAuth2 API
• Credentials = Your google sheets account

[terryble66]

Hi Marcus

I check all choices and they gave me no known credential
I don’t see either the predefinied choice.

Thank you for your help

[terryble66]

Is anyone can help I’m stuck with the authentication, thank you

[MutedJam]

Hi @terryble66

First thing I’d try is restarting your n8n app and creating a new HTTP Request node from scratch (rather than copying an existing one).

If you still can’t find the existing Google credentials afterwards you can still create generic OAuth2 credentials from your HTTP Request node.

You can find most of the required values (apart from your own client ID and secret) in the files describing the Google-specific authentication (here and here). The scopes would need to be separated by a whitespace.

[terryble66]

Thank you MutedJam i can see now the predifined credential type

[terryble66]

I have tried but gave me authorisaton error below

"status": "rejected",
"reason": {
"context": {
},
"name": "NodeApiError",
"cause": {
"message": "403 - {"type":"Buffer","data":[60,33,68,79,67,84,89,80,69,32,104,116,109,108,62,60,104,116,109,108,32,108,97,110,103,61,34,101,110,34,62,60,104,101,97,100,62,60,109,101,116,97,32,110,97,109,101,61,34,100,101,115,99,114,105,...,114,103,105,110,45,116,111,112,58,32,56,48,112,120,59,32,112,111,115,105,116,105,111,110,58,32,114,101,108,97,116,105,118,101,59,125,60,47,115,116,121,108,101,62,60,47,104,116,109,108,62]}",
"name": "Error",
"stack": "Error: Request failed with status code 403 at createError

Is there any more parameter in google cloud console the the one i have followed to prepare the Authentication ?

Thank you

[marcus]

Did you use the Google Sheets node already? The first time you make a request to google sheets it will prompt an error with a link that you have to enable google sheets. Did you do that or did you just try to load the chart image?

[terryble66]

Yes I do, It prompted me and I already did the validation for the authentification, the other request to google API is working in my workflow i call the sheet first (see below.)

I stopped sharing the graphs and shared them again. Now i have a new cause

{
"status": "rejected",
"reason": {
"context": {
},
"name": "NodeApiError",
"cause": {
"code": "ERR_INVALID_ARG_TYPE",
"cause": {...}, // 1 keys
"error": {...}, // 1 keys
"options": {...} // 0 keys
},
"timestamp": 1662132148651,
"message": "UNKNOWN ERROR - check the detailed error for more information",
"node": {
"parameters": {...}, // 11 keys
"name": "HTTP Request",
"type": "n8n-nodes-base.httpRequest",
"typeVersion": 2,
"position": [...], // 2 items
"credentials": {...} // 1 keys
},
"httpCode": "ERR_INVALID_ARG_TYPE",
"description": "The "url" argument must be of type string. Received undefined"
}
}

the picture of the cause

Hope could help finding the root cause.

Thanks

[marcus]

Hello Thierry,
in your Http Request node you are using this expression.

You are getting the following error.

Apparently your charts url is undefined, meaning the column is empty when you read it from google sheets. I assume you created a column charts url and paste the shared url into it, right?

Is it working if you just copy/paste the shared chart url into your http request node without using an expression?

[terryble66]

Hello @marcus
Yes, i copied/pasted the input on my browser and get the image

here is the check

any clue ?
Thanks

[marcus]

Hey Thierry,
i just tried it out using version 0.182.0 that you are using and it is still working for me. I don’t know what could go wrong at your side. I see that you are still using an expression to bind charts url. Can you remove that expression and insert your url directly in the http request node? Does that work?

[terryble66]

Hello Marcus

It’s working with the Url direct link.
I have reapply the expression and working now.

My question now is how can i include this image in my Google Doc ?
I serached and find this post but nothing relevant for me.
And this one is not updated.
This post is maybe something that could work but not with my knowledge
Any clue/Help to do import it ?

[marcus]

Hey Thierry,
I am sorry to say but our Google Docs node currently does not support inserting images. You could use the Http Request Node to insert images.

Here is an example workflow to illustrate.

I am not a google docs expert but

• This example is based on the Insert Inline Image docs
• I think the image url must be publicly accessible so it might not work with your chart images with restricted access.
• You may need to upload you chart image with public access
• There is also a replaceImage batchUpdate mentioned here

[terryble66]

@marcus Thank you

I’m not a real expert in Json and programming i want to use N8N for the moment as simply as possible. The simplest way for me is to use the inbound module from google.
So this part (chats) of my building G-doc will be by coping with linked option the chart from G-sheet to G-doc.
The rest of my building text will be by n8n.
I have another clue to resolved in another post is how to loop text for similar datas into my doc.

Again thanks for all this help.