Google Sheets to HTTP Request

Hoping someone can help me - I’m new to Pipedream but am struggling to make what seems like a very basic workflow. I’ve searched through the docs, google results, and this forum but am still having trouble.

I am simply trying to use a Google Sheet as a product list with SKUs, purchase rates, etc. that sends updates to a HTTP request. I have the trigger connected just fine but what I can’t figure out is the data that actually gets sent.

The goal is that when, for example, a product’s purchase rate (or other field) needs to be updated for whatever reason, we just edit that field on our spreadsheet. Then that entire row’s data is sent as the data for the HTTP request to the endpoint URL.

Any help would be greatly appreciated - I think I’m close but I’m not sure what I’m missing

Hi @jcpacelli welcome and thanks for the question!

So it sounds to me like the current trigger that should initiate the workflow is a product’s purchase rate (or some other field) needing to be updated? In this scenario, what happens before that, that signals the update needing to occur?

I am using the built-in GSheets trigger - I guess I assumed that this works whenever there’s any update whatsoever that’s made in that spreadsheet?

No specific scenario really to answer your question. We can use an example of us getting an email stating that our supplier increased/decreased pricing on a certain product or variation of a product. We would then just go in and make the update manually in the spreadsheet.

Okay so it sounds like when you update data in a given row in your GSheet, you want that (or some form of that) data to be sent to an HTTP endpoint?

If so it sounds like the GSheets trigger you have configured is the right one, then you’ll probably want one of the HTTP / Webhook actions in the next step, to send that data somewhere.

Is that helpful? Are we on the right track?

yup, totally - that’s exactly what I want to do. So far, I have that trigger setup for the GSheet, then I have a send HTTP request as the next step.

In that, I’ve got it set to a PUT method and have gotten as far as having empty data being sent. I think that I use the Data param? and not the Header? Not positive though.

Screenshot of my current (clearly incorrect lol) setup:

Are you getting any errors surfaced in Pipedream? Is any data showing up in Zoho? By the way, we have some built-in actions for Zoho in case that might be helpful. Just add a new action step and search for “zoho” to see if any of those are relevant for your use case.

Hi, sorry for the delay. Yes, that could work - I looked at the action steps but I am not sure how to Fetch an Item from Books utilizing a product’s SKU from my Google Sheet.

I think the product needs to be fetched as its own step? Or can Update Item (in Zoho Books) also fetch based on cell D3 (column D is our SKU column), for example?

I’m not sure that the Zoho actions are going to work - it seems that it needs the actual Item ID in Books in order to update that Item. I think what would work would be to use the same trigger step but go back to the webhook as the action step. That webhook triggers a Zoho Flow which allows searching by our SKUs.

Hi @jcpacelli , I am not super familiar with the Zoho Books API. I’d recommend reaching out to the Zoho Books support team / community to ask them questions about the API. Someone there should be able to help you determine what keys you can use to update / fetch items!

Let us know what you find. Once you determine how the Zoho APIs work, let us know if you have any more questions about how to implement that in your Pipedream workflow.

I don’t want to use the Zoho Books action step. It seems limiting. I would rather use the webhook for Zoho Flow like I was saying so that I can search by our SKU, which is what we use everywhere as identifiers. But I am having trouble getting data to the webhook properly. It is giving me ErrorRequest failed with status code 410

@jcpacelli would you mind doing two things to help me troubleshoot further?

  1. Click the Share button at the top-right of your workflow and share it with dylan@pipedream.com
  2. Share the Zoho Flow webhook docs you’re using to construct this HTTP request? I’d like to take a look at those, as well.

Shared. Thank you for looking into this. In my Pipedream, you’ll notice incorrect params and headers as I was using them to test.

And sure, here’s their doc on their webhook trigger:

@jcpacelli thanks. I’m publishing new versions of our HTTP actions that should give us some more information on the error.

Do you mind if I update your workflow to the latest version of those actions and replay one of the requests that failed?

Sure, do anything you need to, no problem at all

@jcpacelli I pushed new versions of our HTTP actions to return better debugging information and I reran the most recent event in your workflow. Unfortunately, Zoho doesn’t return any more information in the error response.

A 410 error code means that the resource is no longer available. Did you happen to remove this specific webhook from Zoho? I’d first try creating a new webhook endpoint and sending the HTTP request there. If that still fails, I’d recommend reaching out to Zoho support. Since the error is being returned from Zoho, they’ll be able to best help.

Ah great, thank you, that makes sense.

I’ve been testing a bit and I’m having trouble gathering the data I need when the spreadsheet is modified. I essentially need to grab the whole row if one of its cells is updated. Do I add a ‘Get Values in Range’ ?

So luckily, the current values of all rows arrives in the incoming Google Sheets event, in the variable steps.trigger.event.currentValues.values. You also have access to the row that was modified in the variable steps.trigger.event.changes[0].cell (for example, E:3). Technically, updates for multiple cells can arrive from Google at the same time, so you’ll need to handle that case, as well.

I’d recommend adding a new Node.js code step to your workflow and doing something like the following:

import { axios } from "@pipedream/platform"

for (const change of steps.trigger.event.changes) {
  // Get the cell that was updated
  const { cell } = change

  // Split the cell by : to get just the row number
  const [column, row] = cell.split(":")

  // Retrieve the data for that row. Note that Google spreadsheet row numbers
  // start at 1, while JavaScript arrays start at 0. For example, if we're retrieving
  // data for Google row 3, we'll want to get the row data at array index 2.
  // rowData will be an array of row data, e.g. ["1", "2", "3"]
  const rowData = steps.trigger.event.currentValues.values[parseInt(row) - 1]

  // See https://pipedream.com/docs/workflows/steps/code/nodejs/http-requests/#send-a-post-request-to-submit-data
  await axios(this, {
    method: "POST",
    url: <your Zoho URL>,
    headers: {
     "header-key": "header-value" // replace with your headers
    },
    data: {
      // Pass the data required in the body of the POST request here
    }
  })
}

You might have to modify that a bit depending on your exact needs. Take a look at our JavaScript resources and the docs on making HTTP POST requests to learn more.

2 Likes

Okay great, definitely getting closer - hopefully this can work for the handful of spreadsheets we have. I tried implementing this with test data but I’m getting this error:
TypeErrorCannot read property ‘split’ of undefined

I corrected the above post, but I had a bug in the code. It previously read:

for (const change in steps.trigger.event.changes) {

but should have been:

for (const change of steps.trigger.event.changes) {

(just of instead of in)

I modified and reran your workflow, and you’re getting a different 410 error now (see the data for the last event). It’s not obvious from the error exactly what the issue is, so you may want to Google it / reach out to Zoho.

1 Like