Unable to find a Spreadsheet from Data Store

Help
, ,
1

Hello, I have a workflow that store a value from a recently created sheet in GDrive. Here is the workflow:

image
image351×567 25.5 KB

Each time at 4am, I create a spreadsheet from a template then I store the ID of the worksheet in my data stores with the key name “daily_sheet”. This workflow work well (the double quote are here to force pipedream to use the id as a string and not a json):
image
image885×544 40.6 KB

In a second workflow, I get the same key value to get the spreadsheet created during the night. The returned value is ok.

image
image403×711 34 KB

So my problem is when I use the action “add a single row”. Pipedream seems to be unable to recover the file from an ID in a variable.

image
image885×483 33.4 KB

But when I paste the ID, Pipedream can find the spreadsheet like so:

image
image880×539 36.1 KB

First, I thought the variable format in my data storage wasn’t correct but when I try different action like
“get file by id” or “get spreadsheet by id” and giving the return value from the data storage, it work

image
image879×343 23.4 KB

image
image887×680 55.8 KB

so i’m little bit confused on why I can’t use the “add single row” action with a variable as the spreadsheet parameter.

Any idea on my issue? Or any kind of workaround I can use?

Thank you :slight_smile:

2

Hi @xavier1,

It looks like you’re encountering an issue where the “Add Single Row” action in your workflow isn’t accepting the spreadsheet ID from a variable, even though other actions like “Get File by ID” and “Get Spreadsheet by ID” work fine with the same variable.

This issue might be related to how the variable is being passed or interpreted in the “Add Single Row” action. Here are a few steps and potential workarounds to help resolve this issue:

Step-by-Step Solution

  1. Verify the Variable Format:
    Ensure that the variable storing the spreadsheet ID is in the correct format. It should be a string. You can add a code step to log the type and value of the variable to confirm this:

    export default defineComponent({
  async run({ steps, $ }) {
    const spreadsheetId = steps.getSpreadsheetId.$return_value;
    console.log(typeof spreadsheetId, spreadsheetId);
    return spreadsheetId;
  }
});

  2. Use a Code Step to Pass the ID:
    Instead of directly using the variable in the “Add Single Row” action, you can use a code step to pass the ID. This ensures that the ID is correctly formatted and passed to the action.

    export default defineComponent({
  async run({ steps, $ }) {
    const spreadsheetId = steps.getSpreadsheetId.$return_value;
    $.export("spreadsheetId", spreadsheetId);
  }
});

    Then, in the “Add Single Row” action, use the exported value {{steps.code_step.spreadsheetId}}.

  3. Check for Leading/Trailing Spaces:
    Ensure there are no leading or trailing spaces in the spreadsheet ID. You can trim the ID before using it:

    export default defineComponent({
  async run({ steps, $ }) {
    const spreadsheetId = steps.getSpreadsheetId.$return_value.trim();
    $.export("spreadsheetId", spreadsheetId);
  }
});

Workaround

If the above steps do not resolve the issue, you can use the Google Sheets API directly in a code step to add a row to the spreadsheet. Here’s an example:

import { axios } from "@pipedream/platform";

export default defineComponent({
  props: {
    google_sheets: {
      type: "app",
      app: "google_sheets",
    },
   

I'm a bot powered by Pipedream and GPT-4. I'm still learning, so please double-check my answers and code! [Learn how to build your own](https://pipedream.com/blog/build-your-own-chat-bot-with-openai-and-pipedream/).
3

This is known and expected. The reason is that we calculate the props we will show and allow user configuration depending on the spreadsheet columns. Since each spreadsheet can be different, if you supply a custom expression for the ID of the spreadsheet, there isn’t a way for us to reliably allow user configuration when the next spreadsheet ID can have a different column structure.

In summary, this Google Sheets action does not allow a custom expression for the Spreadsheet ID.

4

Thank you for your reply. Is there a way to insert data from a specific sheet created each day then?

5

Would you mind creating a support ticket at Support - Pipedream?
I’d like to take a look at your workflow so I can help more effectively.
Thanks!