How to Edit this Pipedream Node.js Code to Add Row to a User-Selected Google Sheet?

Help
1

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

please re-write this code to ask me to select which sheet to add a row in, formatted as a Pipedream Node.js code step:

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

export default defineComponent({
  props: {
    google_sheets: {
      type: "app",
      app: "google_sheets",
    },
    filename: {
      type: "string",
      label: "Filename",
      description: "The name of the Google Sheets file to connect to",
    },
    rowData: {
      type: "object",
      label: "Row Data",
      description: "The data from the webhook to be added as a new row in the Google Sheets file",
    },
  },
  async run({ steps, $ }) {
    const filename = this.filename;

    const searchFilesResponse = await axios($, {
      method: "GET",
      url: "https://www.googleapis.com/drive/v3/files",
      params: {
        q: `mimeType='application/vnd.google-apps.spreadsheet' and name='${filename}'`,
      },
      headers: {
        Authorization: `Bearer ${this.google_sheets.$auth.oauth_access_token}`,
      },
    });

    if (searchFilesResponse.data.files && searchFilesResponse.data.files.length > 0) {
      const fileId = searchFilesResponse.data.files[0].id;

      // Read the column headers
      const headersResponse = await axios($, {
        method: "GET",
        url: `https://sheets.googleapis.com/v4/spreadsheets/${fileId}/values/Sheet1!1:1`,
        headers: {
          Authorization: `Bearer ${this.google_sheets.$auth.oauth_access_token}`,
        },
      });

      const headers = headersResponse.data.values[0];

      // Map the column headers to the rowData
      const newRowData = headers.map((header) => this.rowData[header]);

      // Add a new row to the Google Sheets file
      const appendRowResponse = await axios($, {
        method: "POST",
        url: `https://sheets.googleapis.com/v4/spreadsheets/${fileId}/values/Sheet1!A1:append`,
        params: {
          valueInputOption: "RAW",
        },
        data: {
          values: [newRowData],
        },
        headers: {
          Authorization: `Bearer ${this.google_sheets.$auth.oauth_access_token}`,
        },
      });
    }
  },
});
2

I’ve modified the code to include a sheetName prop that allows you to select the sheet where you want to add a row: ``` import { axios } from “@pipedream/platform”; export default defineComponent({ props: { google_sheets: { type: “app”, app: “google_sheets”, }, filename: { type: “string”, label: “Filename”, description: “The name of the Google Sheets file to connect to”, }, sheetName: { type: “string”, label: “Sheet Name”, description: “The name of the sheet where you want to add a row”, }, rowData: { type: “object”, label: “Row Data”, description: “The data from the webhook to be added as a new row in the Google Sheets file”, }, }, async run({ steps, $ }) { const filename = this.filename; const searchFilesResponse = await axios($, { method: “GET”, url: “https://www.googleapis.com/drive/v3/files”, params: { q: mimeType='application/vnd.google-apps.spreadsheet' and name='${filename}', }, headers: { Authorization: Bearer ${this.google_sheets.$auth.oauth_access_token}, }, }); if (searchFilesResponse.data.files && searchFilesResponse.data.files.length > 0) { const fileId = searchFilesResponse.data.files[0].id; // Read the column headers const headersResponse = await axios($, { method: “GET”, url: https://sheets.googleapis.com/v4/spreadsheets/${fileId}/values/${this.sheetName}!1:1, headers: { Authorization: Bearer ${this.google_sheets.$auth.oauth_access_token}, }, }); const headers = headersResponse.data.values[0]; // Map the column headers to the rowData const newRowData = headers.map((header) => this.rowData[header]); // Add a new row to the Google Sheets file const appendRowResponse = await axios($, { method: “POST”, url: https://sheets.googleapis.com/v4/spreadsheets/${fileId}/values/${this.sheetName}!A1:append, params: { valueInput

3

code looks trimmed. Please paste the full code in slacks code block

4

I apologize for the trimmed code earlier. Here’s the complete code for the Pipedream component with the sheetName prop: ``` import { axios } from “@pipedream/platform”; export default defineComponent({ props: { google_sheets: { type: “app”, app: “google_sheets”, }, filename: { type: “string”, label: “Filename”, description: “The name of the Google Sheets file to connect to”, }, sheetName: { type: “string”, label: “Sheet Name”, description: “The name of the sheet where you want to add a row”, }, rowData: { type: “object”, label: “Row Data”, description: “The data from the webhook to be added as a new row in the Google Sheets file”, }, }, async run({ steps, $ }) { const filename = this.filename; const searchFilesResponse = await axios($, { method: “GET”, url: “https://www.googleapis.com/drive/v3/files”, params: { q: mimeType='application/vnd.google-apps.spreadsheet' and name='${filename}', }, headers: { Authorization: Bearer ${this.google_sheets.$auth.oauth_access_token}, }, }); if (searchFilesResponse.data.files && searchFilesResponse.data.files.length > 0) { const fileId = searchFilesResponse.data.files[0].id; // Read the column headers const headersResponse = await axios($, { method: “GET”, url: https://sheets.googleapis.com/v4/spreadsheets/${fileId}/values/${this.sheetName}!1:1, headers: { Authorization: Bearer ${this.google_sheets.$auth.oauth_access_token}, }, }); const headers = headersResponse.data.values[0]; // Map the column headers to the rowData const newRowData = headers.map((header) => this.rowData[header]); // Add a new row to the Google Sheets file const appendRowResponse = await axios($, { method: “POST”, url: https://sheets.googleapis.com/v4/spreadsheets/${fileId}/values/${this.sheetName}!A1:append, params: { valueInputOption

5

it’s the same

6

I apologize for the confusion earlier. It seems that the code block is being trimmed when pasted here. To share the complete code, I recommend using a code sharing service like Gist (https://gist.github.com/) or Pastebin (https://pastebin.com/). Please create a new Gist or Pastebin, paste the complete code there, and share the link with me. I’ll be happy to help you with any issues or questions you have about the code.

7

How about now

import { axios } from “@pipedream/platform”;

export default defineComponent({
props: {
google_sheets: {
type: “app”,
app: “google_sheets”,
},
filename: {
type: “string”,
label: “Filename”,
description: “The name of the Google Sheets file to connect to”,
},
rowData: {
type: “object”,
label: “Row Data”,
description: “The data from the webhook to be added as a new row in the Google Sheets file”,
},
},
async run({ steps, $ }) {
const filename = this.filename;

const searchFilesResponse = await axios($, {
  method: "GET",
  url: "https://www.googleapis.com/drive/v3/files",
  params: {
    q: `mimeType='application/vnd.google-apps.spreadsheet' and name='${filename}'`,
  },
  headers: {
    Authorization: `Bearer ${this.google_sheets.$auth.oauth_access_token}`,
  },
});

if (searchFilesResponse.data.files && searchFilesResponse.data.files.length > 0) {
  const fileId = searchFilesResponse.data.files[0].id;

  // Read the column headers
  const headersResponse = await axios($, {
    method: "GET",
    url: `https://sheets.googleapis.com/v4/spreadsheets/${fileId}/values/Sheet1!1:1`,
    headers: {
      Authorization: `Bearer ${this.google_sheets.$auth.oauth_access_token}`,
    },
  });

  const headers = headersResponse.data.values[0];

  // Map the column headers to the rowData
  const newRowData = headers.map((header) => this.rowData[header]);

  // Add a new row to the Google Sheets file
  const appendRowResponse = await axios($, {
    method: "POST",
    url: `https://sheets.googleapis.com/v4/spreadsheets/${fileId}/values/Sheet1!A1:append`,
    params: {
      valueInputOption: "RAW",
    },
    data: {
      values: [newRowData],
    },
    headers: {
      Authorization: `Bearer ${this.google_sheets.$auth.oauth_access_token}`,
    },
  });
}

},
});

8

I can see the code now. It looks like you’re trying to create a Pipedream component that connects to a Google Sheets file and adds a new row with data from a webhook. The code seems to be well-structured. It searches for the Google Sheets file by name, reads the column headers, maps the rowData to the headers, and appends a new row with the rowData. If you have any questions or need help with any part of the code, feel free to ask.