Sync Google Sheet and your API's data with Zapier and Hypi webhooks

Transferring data between Google Worksheet and backend is a much-needed task.

In this post, we will see how to transfer data between Google Sheet and Hypi’s low code backend using webhooks. We are going to use Zapier for connecting Hypi with Google sheet.

Google Sheet to Hypi

Let’s say we have a google sheet with employee information. The sheet has some columns as shown in the data type GoogleSheetData. Row id of the sheet gets stored in the RowNo.

type GoogleSheetData {
    rowNo: Int
    srNo: Int
    name: String
    role: String
    workDone: Boolean
    payment: Float    
}

We will save GoogleSheetData data type in the App’s schema on Hypi. Let’s write webhook function to process incoming data from Google Sheet.

type Query {
newWorkSheetRow(payload: WebhookPayload!): 
    WebhookResponse @tan(type:Groovy, inline: """
    import com.fasterxml.jackson.databind.ObjectMapper
    def mapper = new ObjectMapper()
    def row_new = mapper.readTree(payload.body)
    def gqlResult = gql(\"""
    mutation {
        upsert(
            values: { 
                GoogleSheetData: [
                    { 
                        hypi: {
                            id: ${row_new.srNo}
                        }
                        name: ${row_new.name}
                        role: ${row_new.role}    
                        workDone: ${row_new.workDone.textValue().toBoolean()}
                        srNo: ${row_new.srNo.textValue().toInteger()}
                        rowNo: ${row_new.rowNo.textValue().toInteger()}
                        payment: ${row_new.payment.textValue().toFloat()}
                    }
                ] 
            }
        ) {
            id
        }
    }\"""
    )    
   if(gqlResult.errors != null) return [status: 500, body: gqlResult]
    return [
      "status": 200,
      "headers": payload.headers,
      "body" : payload.body
      ]
  """)
}

We parse the payload and save the data in the GoogleSheetData object. We use an object mapper to parse the JSON payload and save it into Map format. The incoming payload has string values. We convert the strings into different data types like Integer, Float, and Boolean using groovy functions.

E.g. payment: ${row_new.payment.textValue().toFloat()}

Let’s create the webhook with name SheetRow and newWorkSheetRow is the query function that processes the incoming payload.

mutation Upsert($values: HypiUpsertInputUnion!) {
  upsert(values: $values) {
    id
  }
}

{
  "values": {
    "Webhook": [
             {
                         
             "name": "SheetRow",
            "query": {
            "type": "Query",
            "field": "newWorkSheetRow"
            }
          }
       ]
    }
}
{
  "data": {
    "upsert": [
      {
        "id": "01F9936P3D3DKDNQF9390YDK0Z"
      }
    ]
  }
}

The HTTP url to send a webhook request has the following format
https://api.hypi.app/webhook/{domain}/{webhookname}

Let’s say our domain is engulfs.apps.hypi.app. So, our webhook url is
https://api.hypi.app/webhook/engulfs.apps.hypi.app/SheetRow

Our Hypi webhook is ready to receive its first request!

Let’s proceed with Zapier customization now.

  1. Create a new zap on Zapier and choose the trigger event as ‘New or updated spreadsheet Row’

  1. Set up trigger now. Choose spreadsheet and worksheet from Google Sheets for transferring the data.

  1. Create columns on the chosen worksheet: (SrNo, Name, Role, WorkDone, Payment). Add the first row with some data.
  2. Now test the trigger and you can see that inserted data gets retrieved.

  1. Choose the Zapier action as webhook POST.

  1. Set up the action by entering the information like webhook url, JSON payload type, etc. Hypi Webhook url that we have already created. Enter Data from the google sheet. Write the column name and choose retrieved data for the corresponding column from the drop-down. Make sure to use the column names same as the fields of GoogleSheetData type. You may use different names, but make changes in the schema accordingly.

  1. Test the action. You can see that the action is successful.

  1. Verify that the GoogleSheetData object was created with 1st row from Google Sheet.
{
  find(type: GoogleSheetData, arcql: "*") {
    edges {
      node {
        ... on GoogleSheetData {
          hypi{
            id
          }
          name
          role
          srNo
          rowNo
          workDone
          payment
        }
      }
      cursor
    }
  }
}

#Result
{
  "data": {
    "find": {
      "edges": [
        {
          "node": {
            "hypi": {
              "id": "1"
            },
            "name": "ABC",
            "role": "SE",
            "srNo": 1,
            "rowNo": 2,
            "workDone": true,
            "payment": 3000.99
          },
          "cursor": "1"
        }
      ]
    }
  }
}
  1. Now turn on the zap. Update the google sheet with the second row.

    Within few minutes the data gets transferred to another GoogleSheetData object.
{
  "data": {
    "find": {
      "edges": [
        {
          "node": {
            "hypi": {
              "id": "1"
            },
            "name": "ABC",
            "role": "SE",
            "srNo": 1,
            "rowNo": 2,
            "workDone": true,
            "payment": 3000.99
          },
          "cursor": "1"
        },
        {
          "node": {
            "hypi": {
              "id": "2"
            },
            "name": "XYZ",
            "role": "SSE",
            "srNo": 2,
            "rowNo": 3,
            "workDone": false,
            "payment": 5000.99
          },
          "cursor": "2"
        }
      ]
    }
  }
}

Now, whenever you update the google worksheet the data gets transferred to Hypi objects. Isn’t it cool?

Hypi to Google Sheet

Now we will proceed with reverse data transfer- from Hypi object to Google sheet row.

We will send the payload to the webhook url of Zapier as a trigger. The Zapier will process the trigger and update the google sheet row as an action.

We use Hypi’s workflow here. Fetch the data from the GoogleSheetData object in the 1st step. In the next step, send an HTTP request to Zapier webhook with retrieved data as payload.

Here is our workflow schema.

type Query {
    step1(b:String): Json @tan(type:Groovy, inline: """
    def result =  gql(\"""
        query {
            get(type: GoogleSheetData, id: "$b"){
                ... on GoogleSheetData {
                    hypi {
                        id
                    }
                    name
                    role
                    workDone
                    payment
                    srNo
                    rowNo
            }
        }
    }\"""
    )    
    return result.data.get
    """)
    updateGoogleSheet(a: String,b: String): googleSheet @workflow(name: "updateGoogleSheet")
}  

type Mutation {
    step2(previous:Json):Json @http(
       method: POST,
       url: "https://hooks.zapier.com/hooks/catch/10433778/b37z4s7/",
       inline: {
         requestTemplate: """${vars.previous}""",
         responseTemplate: """{"success": #if( $!status == 200 || $!status == 201)true#{else}false#end, "code": #if( $!status == 200 || $!status == 201)$status#{else}-1#end}"""
       },        
    )
 }

updateGoogleSheet is the name of the workflow. The first step returns the JSON data from the hypi object GoogleSheetData. We pass on the hypi.id of the GoogleSheetData object and get the JSON data from it using the id.
In the second step, we use the returned data from the first step as ‘previous’ parameter. We pass on this data as ${vars.previous} in the inline requestTemplate. We get Zapier’s webhook url at the time of setting up trigger on Zapier.

Let’s create this workflow with the name updateGoogleSheet.

{
 "values": {
     "Workflow": [
         {
         "name": "updateGoogleSheet"
         "steps": [
             {
                 "name": "step1",
                 "order": 0,
                 "fn": {
                 "type": "Query",
                     "field": "step1"
                 }
             },
             {
                 "name": "step2",
                 "order": 1,
                 "fn": {
                     "type": "Mutation",
                     "field": "step2"
                 }
             }             
           ]
         }
      ]
   }
}

It is time to look at Zapier customization.

  1. Choose App event as Webhooks by Zapier. Choose the trigger event as ‘Catch Hook’.

  1. Copy the custom webhook url from set up trigger event and paste it into step 2 of the workflow as HTTP url

  1. Let’s test the trigger now. Execute the workflow as follows.
{
  updateGoogleSheet(a: "$a", b:"2"){
  a previous params
     session {
         data {
             stepName
             stepResult
         }
     }
  }
}
#result
{
  "data": {
    "updateGoogleSheet": {
      "a": null,
      "previous": {
        "success": true,
        "code": 200
      },
      "params": {
        "success": true,
        "code": 200
      },
      "session": null
    }
  }
}

You can see that request gets found on Zapier.

  1. Now we will send this data to Google Sheet as an action. Choose action event as ‘Create Spreadsheet row’

  1. Set up the action by choosing a spreadsheet and worksheet. Choose the data for respective columns.

  1. Test the action of sending the payload data to google sheet.

  1. You can see that the data gets updated in google sheet. Turn on the zap. Whenever you execute the workflow on
    Hypi the data from hypi object gets updated as a row in the spreadsheet.

Concluding Note

I hope you have found this stuff interesting. Data transfer between Hypi and another application is easy using Webhook. Zapier can be used as a tool to carry out the data transfer.
Do give it a try!