Many of us take the data from Streamtime and export CSV to compose their own reports in Google Sheets. The benefit of course being that you have complete control of the data shown, the look and feel, the calculations and much more.

The trouble is that as soon as you're finished your data is already outdated, because Streamtime is continuously changing. So not only is it a tedious and time consuming process, it also increases the risk of errors based on outdated information.

wayahead offers custom development to create your own bespoke reports inside the wayahead app. These reports are completely tailor made and require no extra work from the end user. They are quick, easy, flexible and error free. But of course they also come at a price and you are dependent on wayahead to make changes. So some users prefer the flexibility of working with Google Sheets to construct and evolve their own applications.

But what if there was an inbetween way? Where we keep the flexibility of using Google Sheets, but with up-to-date data from Streamtime. That is possible.

Google Sheets offers a function IMPORTHTML() to make a HTML call to a website and expects a HTML response. API's generally return JSON or XML data, so we can't use IMPORTHTML to call the Streamtime API directly.

Instead, we call the wayahead API. wayahead retrieves the data from Streamtime, merges and processes the information and returns a HTML output for Google Sheets.

The wayahead API can take input from Google Sheets by adding extra information in the URL. E.g. what data to retrieve, which fields to return, what date range we want to fetch data for and more. This allows us to carefully define and return the HTML output.

Simultaneously, wayahead may also fetch data from several endpoints in Streamtime at once from a single call to the wayahead API. For example, for one job we could retrieve the job phases as well as the job items, merge them and return them as a single table back to Google.

Every time you open up your Google Sheet, the IMPORTHML() function runs automatically and data is refreshed. But in a similar way, as soon as we modify the url that is called for the IMPORTHML() function, the API is called again. Typically, I would add a small refresh toggle on the page somewhere and add the value of the toggle in the URL. "...&refresh=true&..." Whenever we change the toggle, the url changes and the data is refreshed.

To get started, you need an API code from wayahead. You can create this by going to the wayahead Preferences and press the refresh button 🔄. This will either generate a new code or create a new one. Use the copy button to copy the code and the trash button to delete the code.

Note that wayahead does not refresh this token on your behalf and does not make you refresh it. For security reasons it is advised to replace this code at regular intervals. Evidently you must not share this code with any third parties, as this could grant them unauthorised access to your Streamtime data.

You can find wayahead's API on the following path:

The rest of the URL is custom built for your company in a script that fetches exactly that data you require from Streamtime, processes it in the way desired and returns a table in the best possible layout for your Google Sheets report.

There is a downside to this type of reporting of course: as an end user, you are still required to manage the Google Sheet. You must build the interactivity to set up and initiate the reports, you are required to construct the correct URL for the API call and you must correctly handle the data returned from the API. While for certain people this is a walk in the park, it definitely requires a certain degree of skill in Google Sheets if you don't want the report to break.

Try it out for yourself.

We created a simple sample Google Sheet report that shows you how the intergration works. It imports the Streamtime WIP jobs in a table, that's all, but you'll understand the potential of this type of reporting.

The "Select a Job Sheet" has not API functionality, it just illustrates how we could use the jobs list to create a dropdown menu in Google Sheets and retrieve more information for individual jobs. The dropdown is based on the list of jobs. When you choose a job, Google Sheets calculates the corresponding job ID from the jobs list. That job ID would then be used to make another API call.

Similarly, we could start by fetching a list of Job Leads and create a dropdown to fetch only their jobs. Or fetch a list of clients and create a dropdown to select only their invoices...