[BUG] Resource not found with dynamically created google spreadsheet

[AbdulRafay]

As described in a closed issue here #6237 (comment)

I am facing the same issue again, pasting id of spreadsheet works but getting it from previous step does not.

[vunguyenhung]

I can reproduce this issue:

It does not work when Spreadsheet and Worksheet Id is referenced from previous steps

It works when selecting an async option

[AbdulRafay]

@vunguyenhung thank you for detailed explanation but as per a resolved issue, mentioned below, dynamically created sheets should be accessible or maybe now it is not the case?

#6237 (comment)

some screenshots from the aforementioned issue are as below:

[vunguyenhung]

Hi @AbdulRafay, that issue was one year ago - which might not be applicable to today issue. From my side, I can still see the error - so this ticket is still valid

[mat250]

@vunguyenhung The issue is here inside the connector's code :

pipedream/components/google_sheets/actions/add-single-row/add-single-row.mjs

Line 80 in 954063d

const worksheetName = this.worksheetId.label;

I think a lookup using the Worksheet ID to retrieve the sheet's title is missing.

@AbdulRafay I've found a workaround : put inside your Worksheet Id field an object with the following properties :

{
label: "Title of your spreadsheet sheet"
}

[andrewjschuang]

Note: the cause of this bug is because the prop is configured with withLabel: true, so it expects a label/value dictionary instead of a simple value. This is why using async options work but not when using a custom expression

[vunguyenhung]

Hello everyone, I have tested this PR and there're some test cases failed or needed improvement.

Please check the test report below for more information
https://vunguyenhung.notion.site/BUG-Resource-not-found-with-dynamically-created-google-spreadsheet-46fc86ed11194b528fb5366add6aebe7

[jcortes]

Hello everyone, I have tested this PR and there're some test cases failed or needed improvement.

Please check the test report below for more information https://vunguyenhung.notion.site/BUG-Resource-not-found-with-dynamically-created-google-spreadsheet-46fc86ed11194b528fb5366add6aebe7

Hi @vunguyenhung I think those errors are thrown because you are running each step individually so when you try to capture the value inside one of the paths what you are sending is just the path as a string, so the best way to test this I would say is to do it by running an entire workflow like I do in the following video as a demo

Screen.Recording.2024-07-16.at.12.11.20.PM.mov

So I decided to add a more clear message when the sheetID is not found so we can see what is the ID we are passing to the function

[vunguyenhung]

Hi @jcortes, I see that testing the whole workflow would work.

Though there're some unexpected behaviors when using Custom Expression as below:

• On add_single_row action:
  • When setting Does the first row of the sheet have headers? = TRUE, there's error shown Error fetching worksheet with ID {{steps.create_spreadsheet.$return_value.sheets[0].properties.sheetId}} - Requested entity was not found.
  • When setting Does the first row of the sheet have headers? = TRUE, additional props did not load in this case.
  • Expected behaviors:
    • There's no error when the user set custom expression
    • The prop Does the first row of the sheet have headers? should be hidden. The user should input the value manually in this case
• On add_multiple_rows action:
  • There's error shown Error fetching worksheet with ID {{steps.create_spreadsheet.$return_value.sheets[0].properties.sheetId}} - Requested entity was not found.
  • Expected behaviors:
    • There's no error when the user set custom expression

[vunguyenhung]

Hello everyone, I have tested this PR and there're some test cases failed or needed improvement.

Please check the test report below for more information
https://vunguyenhung.notion.site/BUG-Resource-not-found-with-dynamically-created-google-spreadsheet-46fc86ed11194b528fb5366add6aebe7

[jcortes]

Hi @jcortes, I see that testing the whole workflow would work.

Though there're some unexpected behaviors when using Custom Expression as below:

• On add_single_row action:

  • When setting Does the first row of the sheet have headers? = TRUE, there's error shown Error fetching worksheet with ID {{steps.create_spreadsheet.$return_value.sheets[0].properties.sheetId}} - Requested entity was not found.

  • When setting Does the first row of the sheet have headers? = TRUE, additional props did not load in this case.

  • Expected behaviors:

    • There's no error when the user set custom expression
    • The prop Does the first row of the sheet have headers? should be hidden. The user should input the value manually in this case

• On add_multiple_rows action:

  • There's error shown Error fetching worksheet with ID {{steps.create_spreadsheet.$return_value.sheets[0].properties.sheetId}} - Requested entity was not found.

  • Expected behaviors:

    • There's no error when the user set custom expression

Hi @vunguyenhung so the thing here is that I think the Custom Expression is not parsing the path for the right value so that's why we are passing the expression as a string to the function. IMO this is more like a UI issue instead.
cc @malexanderlim