Automated reports

Getting Started
1

Hi,
I want look into automated report pulls through the API into google sheets, specifically the shipment report. I would like to set a weekly pull on Friday 5pm EST of the weeks shipments. I would like to expand upon these automated pulls in the future but this would be a start. Looking for insight on the process to get this started; developer recommendations, price per pull, complexity etc.

2

Hello @hrc0311!
Welcome to the ShipHero Public API Community!

Public API will not allow you to pull responses into Google Sheets, nor will it let you trigger the call at a given time or date. You will need to build this functionality in your backend.

This is an example of how you could query the API for shipments:

query{
  shipments(date_from: "ISODateTime", date_to: "ISODateTime"){
    request_id
    complexity
    data(first:9 after:"string"){
      pageInfo{
        hasNextPage
        hasPreviousPage
        startCursor
        endCursor
      }
      edges{
        node{
          id
          order_id
          shipping_labels{
            id
            tracking_number
          }
          warehouse_id
          shipped_off_shiphero
          line_items(first:3){
            edges{
              node{
                line_item_id
                line_item{
                  sku
                  quantity_shipped
                }
              }
            }
          }
        }
      }
    }
  }
}
  • This is a basic shipments query that will return shipments between the dates sent as parameters. You could also use only date_from if you need to query all up to the moment you send the query.
  • You have a max of 2002 credits to spend. As you consume them, you will begin recovering 30 credits per second. I recommend pagination with 9 nodes for shipments and 3 for line items, so you are consistently below the recovery rate, meaning you will never run out of credits. (That is if you send this query once every second).
  • You can use and GraphQL IDE like Altair to consult the schema and find out any further field within this query that you might like to use

However, I believe this could get too time-consuming or credit-heavy in the long run. My recommendation would be for you to register our Shipment Update Webhook and save them as they come by until you are ready to send the report.

You can read more about it at the following link: Webhooks – Developer Resources | ShipHero

Please let me know if you need further help.

Have a nice day!
TomasFD

4

If Thomas doesn’t mind, I have 3 other potential solutions.

First solution requires what I consider as moderate programming knowledge with understanding the APIs and how they work. Use a python script to acquire the data your looking for. You would be using the code Thomas provided. There is a way, to connect an API and upload to google sheets Google Sheets API 개요  |  Google for Developers. I have successfully uploaded information to a google sheet. So it is do-able.

Second Solution is simpler in concept, but more difficult in finding the information on how to do it. In fact, at this time I don’t have a link, however I have successfully pulled information from shiphero. Below is a snippit of my google sheets code. In the google sheets scripting, you can write a connection shiphero directly. In my code, I am just taking the order number and retrieving the shopify order link. Another potential problem is the timing and waiting for the data.

Last solution may be the least amount of programming needed. You still have to understand your webhooks and or APIs. You will need 2 paid data companies. One I called stitch I have never used, but they claim they can connect to Shiphero, the second it Zapier which has many integrations and is well known. I’ve dabbled in thier software, but I’m cheap, so I have never done anything paid with them. anyways, you can use stitch to connect to Zapier and Zapier to connect to google sheet.s You may be able to go from stitch to google sheets, but I didnt see it.

Anyways, Good luck! ~Justin

Link for Stitch: https://www.stitchdata.com/
Link for Zapier : www.zapier.com
Link for google API in solution #1 Google Sheets API 개요  |  Google for Developers

Code in Google Sheets for solution #2
Code.gs:

function myFunction(ordID) {
var strI =“{"query":"query {\n\t\n orders(order_number: \"#” + ordID
var str2 = ‘\" shop_name: \"valley-beasts.myshopify.com\") {\n request_id\n complexity\n \n data(first: 1) {\n\n edges {\n node {\n id\n\t\t\t\t\ttags\n\t\t\t\t\tfulfillment_status\n\t\t\t\t\t\n\t\t\t\t\tshipping_address{\n\t\t\t\t\t\tcountry\n\t\t\t\t\t}\n }\n }\n }\n }\n}"}’

var options = {
“muteHttpExceptions”: true,
“method”: “POST”,
“headers”: {
“Authorization”: “Bearer = YOUR TOKEN”,
“Content-Type”: “application/json”},

“payload”: strI+str2}

Logger.log(strI+str2)
var response = UrlFetchApp.fetch(“https://public-api.shiphero.com/graphql”,options)
Logger.log(response.getContentText())
var jdata = JSON.parse(response.getContentText())
Logger.log(jdata)

if (jdata.data.orders.data.edges[0] != null) {
Logger.log(jdata.data.orders.data.edges[0].node.id)
var banka=Utilities.base64Decode(jdata.data.orders.data.edges[0].node.id,Utilities.Charset.UTF_8)
var blob = Utilities.newBlob(banka)
var name = blob.getDataAsString(‘Windows-1252’)
var oNum = name.split(‘:’)
Logger.log(oNum[1])
return oNum[1]

}
if (jdata.data.orders.data.edges[0] == null){
Logger.log(“Why did we get here?”)
return “bad”
}

}

2 Likes