Why Can't Google Sheets Add a Row Using a Variable File Name in a Payroll Spreadsheet?

Help
1

This topic was automatically generated from Slack. You can find the original thread here.

I’m having an issue with the GOOGLE SHEET > ADD A ROW feature. When I hardcode the values into the application, it works. When I attempt to do it using variables, it does not work.

What I’m trying to do is a way to do a spreadsheet for payroll. Each employee has an ID, which is EMPLOYEE ID, then I created a file for each EMPLOYEE.

JOHN DOE is EMPLOYEE ID 1204730870340464. There is a file called 1204730870340464. I want Google Sheets to open the file and add a row. It seems that the Google Sheet name can not be a variable.

Any thoughts?

2

I’m sorry to hear that you’re having issues with the Google Sheets integration. To better understand the problem, could you please provide more information about the variables you’re using and how you’re trying to use them in the “Add a Row” action? This will help me identify the issue and provide a solution.

3

You are correct. I am using GOOGLE SHEET ADD SINGLE ROW.

It asks for THREE fields to work.

  1. Google Sheets Account
  2. Spreadsheet
  3. Sheet Name
    I am able to use the GOOGLE DRIVE FIND FILE MODULE to search for the doc with 1204730870340464. Then I take that path and put it into the Spreadsheet. However, the app hangs on the Sheet Name. I’ve even trying typing in the Sheet Name as ‘SHEET 1’ but nothing happens.
4

I understand that you’re having issues with the Google Sheets “Add Single Row” action when using variables. To help you better, could you please share the code or configuration you’re using for the “Add Single Row” action and how you’re setting the variables? This will allow me to identify the issue and provide a solution.

5

where would I find the code or configuration to share with you?

6

In Pipedream, the code or configuration for a specific step in your workflow can be found in the Pipedream UI. To share it with me, please follow these steps: 1. Open the workflow in the Pipedream UI. 2. Click on the specific step (e.g., “Add Single Row”) that you’re having issues with. 3. In the panel that opens, you’ll see the code or configuration for that step. Please copy and paste the relevant parts here, so I can help you troubleshoot the issue.

7

Google Sheets Account

Credentials are encrypted. Revoke anytime.
Spreadsheet

{{steps.find_file.$return_value[0].id}}

Sheet Name

Does the first row of the sheet have headers?

If the first row of your document has headers we’ll retrieve them to make it easy to enter the value for each column.

8

I see that you’re using the file ID from the find_file step as the Spreadsheet input. However, it seems that you’re having trouble with the Sheet Name input. Let’s try to debug this issue step by step. First, please make sure that the file ID is being correctly passed to the Spreadsheet input. You can add a step to log the file ID to verify this: ```
console.log(“File ID:”, steps.find_file.$return_value[0].id);

9

I’m getting an error:
steps is not defined

10

I apologize for the confusion earlier. It seems there was a misunderstanding in the code snippet I provided. Let’s try to resolve the issue with the Google Sheets “Add Single Row” action. Here’s an example of a Pipedream component that adds a row to a Google Sheet using the file ID and Sheet Name: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google: { type: "app", app: "google", }, fileId: { type: "string", label: "File ID", }, sheetName: { type: "string", label: "Sheet Name", }, rowData: { type: "string[]", label: "Row Data", }, }, async run({ $ }) { const apiUrl = `https://sheets.googleapis.com/v4/spreadsheets/${this.fileId}/values/${encodeURIComponent(this.sheetName)}:append`; const requestBody = { range: this.sheetName, majorDimension: "ROWS", values: [this.rowData], }; return await axios($, { method: "POST", url: apiUrl, headers: { Authorization: `Bearer ${this.google.$auth.oauth_access_token}`, "Content-Type": "application/json", }, data: requestBody, params: { valueInputOption: "RAW", }, }); }, }); Replace the fileId, sheetName, and rowData props with the appropriate values from your workflow. This component should add a new row to the specified Google Sheet. If you still encounter issues, please let me know and provide any error messages or logs that you see during the workflow execution.

11

How do I format the rowData?

12

How do I format the rowData variable?

13

To format the rowData variable, you’ll need to provide an array of values that represent the data for each cell in the row. Each value in the array corresponds to a cell in the row, in the same order as the columns in the Google Sheet. Here’s an example: rowData: { type: "string[]", label: "Row Data", description: "Enter the data for each cell in the row, separated by commas", } In your Pipedream workflow, when configuring the “Add Single Row” action, you’ll input the data for each cell in the row, separated by commas. For example, if you have three columns in your Google Sheet, you might enter: ```
Value1, Value2, Value3

14

This code works with one exception. If data being inserted in the row starts with a number, it stores the data incorrectly.

Instead of:
21 Savage's Playable Character Debuts In 'Call of Duty'
It will store:
21

15

I understand the issue you’re facing with the data being stored incorrectly when it starts with a number. To fix this, you can modify the rowData prop to be of type object[] instead of string[]. This allows you to pass an array of objects with a value and a type property for each cell. The type property will be used to specify the data type as a string, ensuring that the entire text is stored correctly. Here’s the updated component with the modified rowData prop: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google: { type: "app", app: "google", }, fileId: { type: "string", label: "File ID", }, sheetName: { type: "string", label: "Sheet Name", }, rowData: { type: "object[]", label: "Row Data", description: "Enter an array of objects with a 'value' and a 'type' property for each cell", }, }, async run({ $ }) { const apiUrl = `https://sheets.googleapis.com/v4/spreadsheets/${this.fileId}/values/${encodeURIComponent(this.sheetName)}:append`; const requestBody = { range: this.sheetName, majorDimension: "ROWS", values: [this.rowData.map((cell) => cell.value)], }; return await axios($, { method: "POST", url: apiUrl, headers: { Authorization: `Bearer ${this.google.$auth.oauth_access_token}`, "Content-Type": "application/json", }, data: requestBody, params: { valueInputOption: "RAW", }, }); }, }); Now, when configuring the “Add Single Row” action in your Pipedream workflow, you’ll input the data for each cell as an object with a value and a type property. For example: ```
on [ { “value”: “21 Savage’s Playable Character Debuts In ‘Call of Duty’”, “type”: “string” }, { “value”: “Some other data”, “type”: “string” } ]

16

I am getting the following error:

Code Error
unsupported array element type for prop rowData: object
17

I apologize for the confusion earlier. It seems that using an object array for the rowData prop is not supported. In that case, let’s try a different approach to handle the issue with data starting with a number. Instead of modifying the rowData prop type, you can wrap the cell value in quotes when entering the data in the Pipedream UI. This will ensure that the entire text is treated as a string and stored correctly. For example, when configuring the “Add Single Row” action, input the data for each cell in the row like this: ```
“21 Savage’s Playable Character Debuts In ‘Call of Duty’”, “Value2”, “Value3”

18

This code is still not working.

Code Error
unsupported array element type for prop rowData: object
19

I apologize for the confusion earlier. Let’s try another approach to handle the issue with data starting with a number. Instead of modifying the rowData prop type, you can use a formula in Google Sheets to combine the number and the text. In your Google Sheet, create a new column and use the CONCATENATE function to combine the number and the text cells. For example, if the number is in cell A1 and the text is in cell B1, you can use the following formula in cell C1: ```
=CONCATENATE(A1, " ", B1)

20

Earlier today, I asked you for a code which would take the HEADER out of a Google Doc. It was having the same issue of only grabbing the number and not the rest of the value. You fixed it.

Here is the code that you reworked.

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

export default defineComponent({
  props: {
    google: {
      type: "app",
      app: "google",
    },
    docId: {
      type: "string",
      label: "Google Doc ID",
    },
  },
  async run({ steps, $ }) {
    const response = await axios($, {
      url: `https://docs.googleapis.com/v1/documents/${this.docId}`,
      headers: {
        Authorization: `Bearer ${this.google.$auth.oauth_access_token}`,
      },
    });

    const firstH1 = response.body.content.find(
      (element) => element.paragraph && element.paragraph.paragraphStyle.namedStyleType === "HEADING_1"
    );

    if (firstH1) {
      const h1Text = firstH1.paragraph.elements.map(element => element.textRun.content).join("").trim();
      return h1Text;
    } else {
      console.log("No H1 found in the document");
    }
  },
});

So what I’m looking for is a way to connect to a Google Sheet using variables using the FILE ID, and ‘SHEET 1’ and then adding 3 values in a row.

This was the original code that worked, but cut off after the number:

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

export default defineComponent({
  props: {
    google: {
      type: "app",
      app: "google",
    },
    fileId: {
      type: "string",
      label: "File ID",
    },
    sheetName: {
      type: "string",
      label: "Sheet Name",
    },
    rowData: {
      type: "string[]",
      label: "Row Data",
    },
  },
  async run({ $ }) {
    const apiUrl = `https://sheets.googleapis.com/v4/spreadsheets/${this.fileId}/values/${encodeURIComponent(this.sheetName)}:append`;
    const requestBody = {
      range: this.sheetName,
      majorDimension: "ROWS",
      values: [this.rowData],
    };

    return await axios($, {
      method: "POST",
      url: apiUrl,
      headers: {
        Authorization: `Bearer ${this.google.$auth.oauth_access_token}`,
        "Content-Type": "application/json",
      },
      data: requestBody,
      params: {
        valueInputOption: "RAW",
      },
    });
  },
});