This topic was automatically generated from Slack. You can find the original thread here.
can you write a code step that retrieves values from a googlesheet row by row - then with each vaslue insert into a hubspot company record
To achieve this, you can use the Google Sheets and HubSpot integrations available in Pipedream. First, you’ll need to set up a trigger to read rows from your Google Sheet, and then use the HubSpot “Update Company” action to update the company records. Here’s a high-level outline of the steps: 1. Use the Google Sheets “Get Rows” action to retrieve rows from your sheet. 2. Loop through the rows and for each row, use the HubSpot “Update Company” action to update the company record. Please follow these steps to set up the workflow: 1. Add the Google Sheets “Get Rows” action to your workflow. Connect your Google Sheets account and select the sheet you want to read from. 2. Add a “Run Node.js code” step after the “Get Rows” action. Use the following code to loop through the rows and prepare the data for the HubSpot action: const rows = steps.get_rows.$return_value; const companies = rows.map((row) => { return { objectId: row["Company ID"], properties: [ { name: "property_name", value: row["Value"] }, // Add more properties if needed ], }; }); return { companies }; Replace "Company ID" with the column name in your Google Sheet that contains the company IDs, and "Value" with the column name that contains the values you want to insert into the HubSpot company records. Update "property_name" with the actual property name in HubSpot. 3. Add the HubSpot “Batch Create or Update Contact” action after the code step. Connect your HubSpot account and use {{steps.nodejs_code.companies}} as the “Contacts Array” input. This workflow will read rows from your Google Sheet and update the HubSpot company records with the values from the sheet.
there is not a “Get Rows” action for google sheets
You’re right, I apologize for the confusion. There isn’t a “Get Rows” action for Google Sheets. Instead, you can use the “Read Worksheet” action to read data from a Google Sheet. Here’s a revised outline of the steps: 1. Add the Google Sheets “Read Worksheet” action to your workflow. Connect your Google Sheets account and select the sheet and worksheet you want to read from. 2. Add a “Run Node.js code” step after the “Read Worksheet” action. Use the following code to loop through the rows and prepare the data for the HubSpot action: const rows = steps.read_worksheet.$return_value.data; const companies = rows.map((row) => { return { objectId: row["Company ID"], properties: [ { name: "property_name", value: row["Value"] }, // Add more properties if needed ], }; }); return { companies }; Replace "Company ID" with the column name in your Google Sheet that contains the company IDs, and "Value" with the column name that contains the values you want to insert into the HubSpot company records. Update "property_name" with the actual property name in HubSpot. 3. Add the HubSpot “Batch Create or Update Contact” action after the code step. Connect your HubSpot account and use {{steps.nodejs_code.companies}} as the “Contacts Array” input. This workflow will read rows from your Google Sheet and update the HubSpot company records with the values from the sheet.
on the nodejs step its erroring saying “‘return’ outside of function”
I apologize for the mistake in the code. It seems I missed wrapping the code inside the run method of the defineComponent. Here’s the corrected code: ```
const rows = steps.read_worksheet.$return_value.data; export default defineComponent({ async run({ steps, $ }) { const companies = rows.map((row) => { return { objectId: row[“Company ID”], properties: [ { name: “property_name”, value: row[“Value”] }, // Add more properties if needed ], }; }); return { companies }; }, });
I now get “ReferenceError: steps is not defined”
I apologize for the confusion. It seems I made a mistake in the code. Please replace the first line inside the defineComponent with the following: const rows = this.read_worksheet.$return_value.data; Here’s the corrected code: ```
export default defineComponent({ async run({ steps, $ }) { const rows = this.read_worksheet.$return_value.data; const companies = rows.map((row) => { return { objectId: row[“Company ID”], properties: [ { name: “property_name”, value: row[“Value”] }, // Add more properties if needed ], }; }); return { companies }; }, });
the batch hubspot contact actoion errors with "“status”:“error”,“message”:“The maximum number of inputs supported in a batch is 100”
I apologize for the oversight. The HubSpot “Batch Create or Update Contact” action has a limit of 100 contacts per batch. To handle this, you can modify the Node.js code step to split the companies array into chunks of 100 and then loop through these chunks to perform the HubSpot action for each chunk. Here’s the updated code for the Node.js step: export default defineComponent({ async run({ steps, $ }) { const rows = this.read_worksheet.$return_value.data; const companies = rows.map((row) => { return { objectId: row["Company ID"], properties: [ { name: "property_name", value: row["Value"] }, // Add more properties if needed ], }; }); // Function to split the array into chunks function chunkArray(array, chunkSize) { const chunks = []; for (let i = 0; i < array.length; i += chunkSize) { chunks.push(array.slice(i, i + chunkSize)); } return chunks; } // Split the companies array into chunks of 100 const companyChunks = chunkArray(companies, 100); // Loop through the chunks and perform the HubSpot action for each chunk for (const chunk of companyChunks) { await this.hubspot.batch_create_or_update_contact({ contactsArray: chunk, }); } return { companies }; }, }); This code will split the companies array into chunks of 100 and perform the HubSpot “Batch Create or Update Contact” action for each chunk.
rather than a batch update hubspot action can i loop through and update the company in hubspot
Yes, you can loop through and update each company individually using the HubSpot “Update Company” action. Here’s an example of how to do this in a code step: export default defineComponent({ async run({ steps, $ }) { const rows = this.read_worksheet.$return_value.data; const companies = rows.map((row) => { return { objectId: row["Company ID"], properties: [ { name: "property_name", value: row["Value"] }, // Add more properties if needed ], }; }); for (const company of companies) { await this.hubspot.update_company({ objectId: company.objectId, properties: company.properties, }); } return { companies }; }, }); This code will loop through the companies array and perform the HubSpot “Update Company” action for each company. Make sure to replace the property_name and other placeholders with the actual property names you want to update.
if I use that code can you please give me the total steps I need to create this workflow?
Sure, here are the steps to create the workflow: 1. Create a new workflow in the Pipedream UI. 2. Add the appropriate trigger for your workflow (e.g., HTTP, Scheduler, or an app-based event). 3. Add a “Read Worksheet” action from the Google Sheets app to read the data from your worksheet. 4. Add a “Run Node.js code” action and paste the following code: export default defineComponent({ async run({ steps, $ }) { const rows = this.read_worksheet.$return_value.data; const companies = rows.map((row) => { return { objectId: row["Company ID"], properties: [ { name: "property_name", value: row["Value"] }, // Add more properties if needed ], }; }); for (const company of companies) { await this.hubspot.update_company({ objectId: company.objectId, properties: company.properties, }); } return { companies }; }, }); 5. Replace the property_name placeholder with the actual property name you want to update in HubSpot. 6. Connect your Google Sheets and HubSpot accounts if you haven’t already. 7. Configure the trigger, Google Sheets action, and HubSpot action with the necessary details. Once you’ve completed these steps, your workflow should be ready to run.
wheres the update hubspot action?
To use the “Update Company” action in HubSpot, follow these steps: 1. In your Pipedream workflow, click on the “+” button to add a new step. 2. Search for the HubSpot app in the step menu. 3. Select the “Update Company” action. 4. Connect your HubSpot account. 5. Select an Object ID or pass a string from a previous step. 6. Select one or more Property Groups or pass an array of strings from a previous step. 7. At the bottom of the step, click “Test” to run the action and test the configuration. For more details, you can refer to this source: https://pipedream.com/apps/hubspot/actions/update-company
the script errors with “Cannot read properties of undefined (reading ‘update_company’)”
Apologies for the confusion earlier. I made a mistake in the code. Instead of using the HubSpot action directly in the code, we should use the HubSpot API. Here’s the updated code: import { axios } from "@pipedream/platform"; export default defineComponent({ async run({ steps, $ }) { const rows = this.read_worksheet.$return_value.data; const companies = rows.map((row) => { return { objectId: row["Company ID"], properties: [ { name: "property_name", value: row["Value"] }, // Add more properties if needed ], }; }); for (const company of companies) { const properties = company.properties.map((prop) => ({ name: prop.name, value: prop.value, })); await axios(this, { method: "PATCH", url: `https://api.hubapi.com/crm/v3/objects/companies/${company.objectId}`, headers: { "Content-Type": "application/json", Authorization: `Bearer ${auths.hubspot.oauth_access_token}`, }, data: { properties, }, }); } return { companies }; }, }); This code uses the HubSpot API to update the companies. Make sure to replace the property_name placeholder with the actual property name you want to update in HubSpot.
I get an error - “Cannot read properties of undefined (reading ‘read_worksheet’)”
Apologies for the confusion earlier. It seems I made a mistake in referring to the Google Sheets step. Please follow these steps to correct the issue: 1. Add a “Read Worksheet” action from the Google Sheets app to your workflow and give it a step name, for example, read_worksheet. 2. Update the code to reference the correct step name. Replace this.read_worksheet with steps.read_worksheet: import { axios } from "@pipedream/platform"; export default defineComponent({ async run({ steps, $ }) { const rows = steps.read_worksheet.$return_value.data; const companies = rows.map((row) => { return { objectId: row["Company ID"], properties: [ { name: "property_name", value: row["Value"] }, // Add more properties if needed ], }; }); for (const company of companies) { const properties = company.properties.map((prop) => ({ name: prop.name, value: prop.value, })); await axios(this, { method: "PATCH", url: `https://api.hubapi.com/crm/v3/objects/companies/${company.objectId}`, headers: { "Content-Type": "application/json", Authorization: `Bearer ${auths.hubspot.oauth_access_token}`, }, data: { properties, }, }); } return { companies }; }, }); Make sure to replace the property_name placeholder with the actual property name you want to update in HubSpot. This should resolve the error you encountered.