update google sheets with variables

Hi everyone,
I’m experiencing some troubles trying to dynamically update some google sheets.

I created a table with sheets names and ids, then with a “table row to variable loop start” node I pass these parameters to a filter that determines the rows to write on each spreadsheet and to a “google sheets updater” node (using the flow variables spreadsheetID and spreadsheetName, if I use just the Name I don’t receive the error but just a single spreadsheet is overwritten several times) but when i launch the job I receive this error:

ERROR Google Sheets Updater 3:18 Execute failed: 404 Not Found
{
“code” : 404,
“errors” : [ {
“domain” : “global”,
“message” : “Requested entity was not found.”,
“reason” : “notFound”
} ],
“message” : “Requested entity was not found.”,
“status” : “NOT_FOUND”

What am I doing wrong?

Thanks in advance for your help

1 Like

Hi @Yaghm and welcome to the Knime Community.

Based on the error message, it seems like you are trying to access something that does not exist - there are quite a number of “not found” just in that message :slight_smile:

Are you trying to add new sheets? The Google Sheets Updater updates data on existing sheets. So if you are trying to update a sheet that does not exist, it will complain. You need to add the sheet instead.

Can you show us what you are trying to do?

Ideally if you could share your workflow, that would be best.

Thanks a lot for your help,
on your first question I selected the option “select first sheet”, in any case all the sheets are already existing and having same structure with just one sheet (and with the same name also). I also attach the workflow as you requested

Giacomo

ok I’ve removed the attachment thanks

Hi @Yaghm , I did not think I would be able to connect via your workflow, it looks like you included your profile in the workflow.

I suggest your remove your workflow for now (just Edit the post and remove it) as I suppose you don’t want anyone to access your google account…

Since I have the data, I will look at the workflow and delete it once the issue is resolved.

Hi @Yaghm , the issue here is that we need to make a distinction between “spreadsheet” and “sheet”. I am sure you know the difference between the 2, but just to be sure that we’re understanding the same thing, and for other readers, a spreadsheet would be the equivalent of an Excel file, and sheet would be the different sheets that the spreadsheet has.

Looking at the flow variable names from the Google Sheets Updater:

It looks like you are using the sheet id values as spreadsheet ids, which is why Google is complaining that it cannot find these spreadsheet ids.

From the google api docs https://developers.google.com/sheets/api/guides/concepts:
https://docs.google.com/spreadsheets/d/**spreadsheetId**/edit#gid=0
https://docs.google.com/spreadsheets/d/aBC-123_xYz/edit#gid=**sheetId**

Based on the data that you have, they are sheetids and not spreadsheetids.

Looking at the Knime Settings page and the Flow Variables page, I don’t see where you can pass the sheetid. What is also weird is that we can pass the spreadsheetId only via the Flow Variables. Normally, whatever you have as Flow Variables should also be in the Settings page. The Flow Variables simply allows you to overwrite the Settings dynamically.

You can see in the Flow Variables window that it provides the option for both the spreadsheet name and the sheet name, but only provides the option for spreadsheet id and not for the sheet id.

The error that you are getting is for sure that you are assigning sheet id values as spreadsheet id.

But the issue remains on how do you pass the sheetid. As an alternative, can you use the spreadsheetid instead?

Tagging @ScottF and @Iris from the Knime team to see if it’s a bug that the sheetid is missing from the Flow Variables.

3 Likes

Thanks a lot @bruno29a !
The problem was exactly that I was recording/inputing sheet ids and not spreadsheet ids, now I corrected my table and everything works fine. I don’t have any issue with sheet ids because I have always to update the first sheet of any spreadsheet, but in that case I think that the problem does not exist because on every spreadsheet sheet names are uniques, so it’s enough to pass that parameter and not the id (in your drive you can have several spreadsheets with same name, I think this is why the id is needed).

I provided to change my account pw, is this enough in case I do again the same security mistake (sharing a google auth node with token embedded)?

Regards,
Giacomo

1 Like

Hi @Yaghm , I’m glad that it’s resolved.

Regarding your credentials, yes, the change is good, I could no longer access anything right after you removed the workflow, I’m assuming you changed your credentials at the same time, which is the best move to do.

Just make sure that if you have other workflows that use the same credentials, you will need to change them in these workflows as well.

For providing the workflow, in this particular case, it could have been difficult to see the data since it’s all coming from the google account - I guess it was a “good” accident in this case as it allowed me to see what you had as sheet id. It’s always tricky to share a workflow, sometimes the data is sensitive, and it’s hard for users to share data, in which case you can provide dummy data, but in your particular case, it’s more tricky as the data was coming from the google sheet.

Anyways, it happened this way, as I said, it was “good” accident as it enabled me to see the data, and it’s also good that it was removed almost right away. Suggestions will vary depending on the situation, but in general, never share your credentials or a node that has your credentials embedded.

Let’s see what you have in your next post, and we’ll suggest the best way to share your workflow/data :slight_smile:

1 Like

HI @bruno29a - it turns out we do have a ticket for this (AP-17528). I added this thread as a +1 on the ticket. :+1:

2 Likes

Thanks for the info and for the +1 @ScottF

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.