I am getting the following error when I try to write a list of text to a column in Google Sheets. Any idea what it doesn’t like? I can’t seem to find any documentaion on the error message
What is the error message (if any)?
ERROR: Bad request - please check your parameters
Invalid value at 'data[0].values' (type.googleapis.com/google.protobuf.ListValue), "galeriefritz.com" Invalid value at 'data[1].values' (type.googleapis.com/google.protobuf.ListValue), "psjza.com" Invalid value at 'data[2].values' (type.googleapis.com/google.protobuf.ListValue), "proxyinjector.com" Invalid value at 'data[3].values' (type.googleapis.com/google.protobuf.ListValue),
Hey @Nicolai, I am sorry to hear you’re having trouble here. Could you share an example workflow as well as the structure of your Google Sheet? This will hopefully help us figure out what’s wrong here.
Thank you @MutedJam I will paste the workflow below, and as for the Google Sheet, it is currently just a blank sheet, no column names even. I was just trying to get the plain old text pasting to work first.
It really is very simple, no looking up required, I just want to replace the first column with the contents of the txt file. You could replicate it by creating a new blank spreadsheet.
I look forward to any insight you might have. Thanks in advance!
Wowee. That worked perfectly (I copied and pasted the ‘create rows’ node into my workflow)
My data appeared in my sheet! Like a miracle.
Not workflow related, but I had to add a new url to my list od “authorized redirect urls” in my API settings in Google Console or my connection failed.
Not sure how often I will have to do that. Do the return authorization urls change frequently? That would put a damper on the automated nature of this automation.
Thanks so much @MutedJam this is great and I would never have figured it out without this community.
I didn’t expect your data structure to match mine that closely, but I suppose it makes sense to explain what the Create Rows code does then. Skip it if you’d rather not want to know
The first line const columns = items.map(e => Object.keys(e.json))[0]; simply reads all the field names of the first item this node receives and stores them in a constant named columns (since this will be our column headers). It uses Object.keys() to get the field names and .map() to only look at the actual field names rather than n8n’s internal data structure. The [0] simply points to the first item ([1] would be the second item, [2] the third and so on).
I am then creating an array/a list of rows which will later be sent to Google Sheets. The first element of this list are our columns since they’ll be the first row.
Afterwards I am running through all incoming items using for (item of items) and create a new row for each item (let row = [];). I am then going throw each known column name with for (column of columns) and find the corresponding value for that column which is then added to the row. Once done with all columns, the single row is added to our existing rows.
The last return simply returns the data structure expected by n8n.
Do the return authorization urls change frequently?
As for the authorization URL, are you using the desktop app by any chance (or have n8n’s tunnel configured manually)? If so, the redirect URL would indeed change frequently (since it uses a temporary tunnel allowing external services to send data into n8n).
For a permanent URL, you’d need to use a server that would have a permanent hostname on the internet (or use n8n.cloud).
However, the authorization would usually need to be done only once unless you change your password or revoke your OAuth credentials or stop n8n for a long time (in which case it might no longer be able to renew the token used to authenticate with Google) - not sure how long exactly that timespan is though as this might differ for each service.
You have replied to an older thread that has been solved, Can you create a new post for your issue and follow the template that it provides so we can get the information we will need to start with.
