How to Create a Workflow for Transferring Variables from a Spreadsheet to a Document?

Help
1

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

I would like to create a workflow to take variables from a SPREADSHEET and place them in a DOCUMENT.

For example:

In the form, the field would be FIRST NAME and in the document it would say {FIRST NAME}.

Any thoughts on how to accomplish this? I’m going to have multiple variables.

2

To accomplish this, you can create a Pipedream workflow with a trigger and actions for reading data from the spreadsheet and updating the document. First, let’s identify the services you’re using for the spreadsheet and document. Are you using Google Sheets and Google Docs, or another service? This information will help me provide you with the appropriate steps and components to use in your workflow.

3

I’m using Google Sheets and Google Docs.

4

Great! To create a workflow that takes variables from a Google Sheets spreadsheet and places them in a Google Docs document, you can follow these steps: 1. Use the Google Sheets - Get Sheet Values action to read data from your spreadsheet. You’ll need to provide the Spreadsheet ID, Sheet Name, and the range of cells you want to read. 2. Use the Google Docs - Get Document action to get the document you want to update. You’ll need to provide the Document ID. 3. Use the Google Docs - Batch Update Document action to replace the placeholders in the document with the values from the spreadsheet. You’ll need to provide the Document ID and the requests to update the document. Here’s an example of how to set up the workflow: 1. Add a Google Sheets - Get Sheet Values action: - Connect your Google Sheets account. - Provide the Spreadsheet ID and Sheet Name. - Set the range of cells you want to read (e.g., “A1:B10”). 2. Add a Google Docs - Get Document action: - Connect your Google Docs account. - Provide the Document ID. 3. Add a Google Docs - Batch Update Document action: - Connect your Google Docs account. - Provide the Document ID. - In the “Requests” field, you’ll need to provide the update requests in JSON format. For example, if you want to replace the placeholder {FIRST NAME} with the value from the spreadsheet, you can use the following JSON structure: on [ { "replaceAllText": { "containsText": { "text": "{FIRST NAME}", "matchCase": true }, "replaceText": "VALUE_FROM_SPREADSHEET" } } ] Replace VALUE_FROM_SPREADSHEET with the value you got from the Google Sheets action in step 1. You can add more requests in the JSON array to replace multiple placeholders with different values from the spreadsheet. Once you’ve set up the workflow, you can test it to make sure it updates the Google Docs document with the values from the Google Sheets spreadsheet.

5

I was unable to find the Google Docs - Batch Update Document.

6

I apologize for the confusion earlier. It seems I made an error in my response. Pipedream does not have a pre-built “Google Docs - Batch Update Document” action. Instead, you can use the Google Docs API directly within a code step to achieve the same result. Here’s an example of how to set up the workflow: 1. Add a Google Sheets - Get Sheet Values action: - Connect your Google Sheets account. - Provide the Spreadsheet ID and Sheet Name. - Set the range of cells you want to read (e.g., “A1:B10”). 2. Add a Code step with the following code to update the Google Docs document: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google: { type: "app", app: "google", }, documentId: { type: "string", label: "Document ID", }, }, async run({ steps, $ }) { const requests = [ { replaceAllText: { containsText: { text: "{FIRST NAME}", matchCase: true, }, replaceText: "VALUE_FROM_SPREADSHEET", }, }, ]; return await axios($, { method: "POST", url: `https://docs.googleapis.com/v1/documents/${this.documentId}:batchUpdate`, headers: { Authorization: `Bearer ${this.google.$auth.oauth_access_token}`, }, data: { requests, }, }); }, }); Replace VALUE_FROM_SPREADSHEET with the value you got from the Google Sheets action in step 1. You can add more requests in the requests array to replace multiple placeholders with different values from the spreadsheet. Once you’ve set up the workflow, you can test it to make sure it updates the Google Docs document with the values from the Google Sheets spreadsheet.

7

can you give me an example of “You can add more requests in the requests array to replace multiple placeholders with different values from the spreadsheet”

8

Certainly! Here’s an example of how to add multiple requests in the requests array to replace multiple placeholders with different values from the Google Sheets spreadsheet: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google: { type: "app", app: "google", }, documentId: { type: "string", label: "Document ID", }, }, async run({ steps, $ }) { const requests = [ { replaceAllText: { containsText: { text: "{FIRST NAME}", matchCase: true, }, replaceText: "VALUE_FROM_SPREADSHEET_FIRST_NAME", }, }, { replaceAllText: { containsText: { text: "{LAST NAME}", matchCase: true, }, replaceText: "VALUE_FROM_SPREADSHEET_LAST_NAME", }, }, ]; return await axios($, { method: "POST", url: `https://docs.googleapis.com/v1/documents/${this.documentId}:batchUpdate`, headers: { Authorization: `Bearer ${this.google.$auth.oauth_access_token}`, }, data: { requests, }, }); }, }); In this example, we’re replacing two placeholders: {FIRST NAME} and {LAST NAME}. Replace VALUE_FROM_SPREADSHEET_FIRST_NAME and VALUE_FROM_SPREADSHEET_LAST_NAME with the respective values you got from the Google Sheets action in your workflow.

9

how can I get the values of the last row added in a spreadsheet?

10

To get the values of the last row added in a Google Sheets spreadsheet, you can follow these steps: 1. Add a Google Sheets - Get Sheet Metadata action to your workflow: - Connect your Google Sheets account. - Provide the Spreadsheet ID. 2. Add a Code step with the following code to get the last row’s values: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google: { type: "app", app: "google", }, spreadsheetId: { type: "string", label: "Spreadsheet ID", }, sheetName: { type: "string", label: "Sheet Name", }, }, async run({ steps, $ }) { const sheetMetadata = steps.get_sheet_metadata.$return_value; const sheet = sheetMetadata.sheets.find((sheet) => sheet.properties.title === this.sheetName); const rowCount = sheet.properties.gridProperties.rowCount; const range = `${this.sheetName}!A${rowCount}:Z${rowCount}`; const response = await axios($, { method: "GET", url: `https://sheets.googleapis.com/v4/spreadsheets/${this.spreadsheetId}/values/${encodeURIComponent(range)}`, headers: { Authorization: `Bearer ${this.google.$auth.oauth_access_token}`, }, }); return response.values[0]; }, }); Replace steps.get_sheet_metadata.$return_value with the actual step name of the Google Sheets - Get Sheet Metadata action in your workflow. This code will fetch the values of the last row in the specified sheet. After setting up the workflow, you can test it to make sure it retrieves the values of the last row in the Google Sheets spreadsheet.

11

There is no such thing as a Google Sheets - Get Sheet Metadata action.

12

Apologies for the confusion earlier. You’re right, there isn’t a “Google Sheets - Get Sheet Metadata” action. Instead, you can use the Google Sheets API to get the metadata and the last row of the sheet. Here’s an example: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google: { type: "app", app: "google", }, spreadsheetId: { type: "string", label: "Spreadsheet ID", }, sheetName: { type: "string", label: "Sheet Name", }, }, async run({ steps, $ }) { // Get sheet metadata const metadataResponse = await axios($, { method: "GET", url: `https://sheets.googleapis.com/v4/spreadsheets/${this.spreadsheetId}?includeGridData=false`, headers: { Authorization: `Bearer ${this.google.$auth.oauth_access_token}`, }, }); const sheet = metadataResponse.data.sheets.find( (sheet) => sheet.properties.title === this.sheetName ); const rowCount = sheet.properties.gridProperties.rowCount; // Get the last row values const range = `${this.sheetName}!A${rowCount}:Z${rowCount}`; const lastRowResponse = await axios($, { method: "GET", url: `https://sheets.googleapis.com/v4/spreadsheets/${this.spreadsheetId}/values/${encodeURIComponent(range)}`, headers: { Authorization: `Bearer ${this.google.$auth.oauth_access_token}`, }, }); return lastRowResponse.data.values[0]; }, }); Replace this.spreadsheetId and this.sheetName with the respective Spreadsheet ID and Sheet Name. This code will fetch the metadata and the values of the last row in the specified sheet.

13

when I replace the variable sheet name with the sheet name, I am getting an error.

TypeError
Cannot read properties of undefined (reading 'sheets')
14

I apologize for the confusion earlier. It seems there might be an issue with the code I provided. Let’s try a different approach to get the last row of a Google Sheets spreadsheet: 1. Add a Google Sheets - Get Sheet Values action to your workflow. - Connect your Google Sheets account. - Provide the Spreadsheet ID and the Sheet Name. - Set the range to a large enough range, like A1:Z1000 (adjust the range according to your sheet size). 2. Add a Code step with the following code to get the last row’s values: const sheetValues = steps.get_sheet_values.$return_value.values; const lastRow = sheetValues[sheetValues.length - 1]; return lastRow; Replace steps.get_sheet_values.$return_value with the actual step name of the Google Sheets - Get Sheet Values action in your workflow. This code will find the last row with data in the specified range and return its values. Please let me know if you need further assistance or if you have any questions.

15

When running this code:

const sheetValues = steps.get_values_in_range.$return_value.values;
const lastRow = sheetValues[sheetValues.length - 1];
return lastRow;

I am getting this error.

Code Error
'return' outside of function. (3:0)
16

I apologize for the confusion earlier. It seems the code snippet provided was not within a Pipedream component. Here’s the corrected code snippet within a Pipedream component: export default defineComponent({ async run({ steps }) { const sheetValues = steps.get_values_in_range.$return_value.values; const lastRow = sheetValues[sheetValues.length - 1]; return lastRow; }, }); Make sure to replace steps.get_values_in_range.$return_value with the actual step name of the Google Sheets - Get Sheet Values action in your workflow. This code will find the last row with data in the specified range and return its values.

17

with this code, how do I insert my variable?

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

export default defineComponent({
  props: {
    google: {
      type: "app",
      app: "google",
    },
    documentId: {
      type: "string",
      label: "Document ID",
    },
  },
  async run({ steps, $ }) {
    const requests = [
      {
        replaceAllText: {
          containsText: {
            text: "{FIRST NAME}",
            matchCase: true,
          },
          replaceText: "VALUE_FROM_SPREADSHEET_FIRST_NAME",
        },
      },
      {
        replaceAllText: {
          containsText: {
            text: "{LAST NAME}",
            matchCase: true,
          },
          replaceText: "VALUE_FROM_SPREADSHEET_LAST_NAME",
        },
      },
    ];

    return await axios($, {
      method: "POST",
      url: `https://docs.googleapis.com/v1/documents/${this.documentId}:batchUpdate`,
      headers: {
        Authorization: `Bearer ${this.google.$auth.oauth_access_token}`,
      },
      data: {
        requests,
      },
    });
  },
});

My variable is steps.get_values_in_range.$return_value[1][1].

18

To insert your variable steps.get_values_in_range.$return_value[1][1] into the code, you can replace the placeholder values with the actual variable. Here’s the updated code: import { axios } from "@pipedream/platform"; export default defineComponent({ props: { google: { type: "app", app: "google", }, documentId: { type: "string", label: "Document ID", }, }, async run({ steps, $ }) { const firstName = steps.get_values_in_range.$return_value[1][1]; const lastName = steps.get_values_in_range.$return_value[1][2]; // Assuming the last name is in the next column const requests = [ { replaceAllText: { containsText: { text: "{FIRST NAME}", matchCase: true, }, replaceText: firstName, }, }, { replaceAllText: { containsText: { text: "{LAST NAME}", matchCase: true, }, replaceText: lastName, }, }, ]; return await axios($, { method: "POST", url: `https://docs.googleapis.com/v1/documents/${this.documentId}:batchUpdate`, headers: { Authorization: `Bearer ${this.google.$auth.oauth_access_token}`, }, data: { requests, }, }); }, }); This code will replace the {FIRST NAME} and {LAST NAME} placeholders with the values from your Google Sheets spreadsheet. Make sure to adjust the indices in steps.get_values_in_range.$return_value to match the correct row and column in your sheet.

19

I need a code that will output all of the variables in steps.get_values_in_range in the console.

20

To output all the variables in steps.get_values_in_range in the console, you can use the following Pipedream component code: export default defineComponent({ async run({ steps }) { const sheetValues = steps.get_values_in_range.$return_value.values; sheetValues.forEach((row, rowIndex) => { console.log(`Row ${rowIndex + 1}:`, row); }); return sheetValues; }, }); This code will log each row of the steps.get_values_in_range values in the console. Make sure to replace steps.get_values_in_range with the actual step name of the Google Sheets - Get Sheet Values action in your workflow.