We recently had an organization come to us with a unique challenge: a group of public broadcast stations needed a way to see what other stations were working on. They wanted an easy way to spark collaboration, avoid any programming duplication, and see opportunities for cross-promotion.

We had to figure out the best way to go about creating a private, easily-accessible, and friction-less shared inventory (what we were also calling a pipeline) of programs for TV and radio stations scattered across the state.

Here’s how we did it:

First, we started by identifying the basic needs of the programmers and those who would be maintaining the inventory/pipeline. Remember, we wanted to keep it as friction-less as easy to use as possible! Then, we focused on finding existing tools which would offer the necessary features for the workflow while being easy to adopt and use. Below you’ll find an overview of the workflow we built. We hope that sharing this with you will spark ideas for ways to improve your own workflows - whether it be a shared pipeline or another use case.

As always, feel free to reach out to us if you have a unique challenge that we can help you solve!

Our workflow uses a private (password protected) page in WordPress, Google Forms, Google Sheets, a script for said spreadsheet, and Google Drive. Here’s how it all comes together:

Step 1: Limit access to the shared inventory/pipeline

A WordPress page became the hub for the private access to all the shared information. We created a password-protected page which has direct access to the form (more below), as well as a direct link to the spreadsheet (again, more below) which contains all the shared information.

Here’s how to create a password protected page in WordPress and how it looked to users:

The workflow we created relies heavily on Google tools, so a Google login for each contributor to the inventory/pipeline is necessary.

For example, the Google Form that's embedded in the private page requires you to log in to submit your responses. A login is also required to access the spreadsheet. This allows for another layer of restricting access and creates a history of track changes for each user.

Next, you’ll see how we created the form.

Step 2: Build a form to collect information for the shared inventory

We built our form using Google Forms. They’re easy to create, allow for collaborators, and seamlessly connect with other Google Tools, which in our case are Google Sheets, Google Drive and of course, the Google login for restricting access.

The fields we decided to use are the following:

Step 3: Funnel the form submissions to a spreadsheet

Once responses to the form are submitted, the user has an option to edit their submission or submit an additional form response. The submissions are automatically sent to a connected Google Sheet. Each form question has its own column in the Google Sheet and the responses are added as cells under each column. This is the case for all columns except two. The exceptions:

Program Upload

If a program is uploaded as a .pdf file, for example, then the file is saved in a Google Drive folder and a link to the file is automatically added to the spreadsheet.

Link to Edit the Form Submission

Since every form submission has a unique URL, we needed to find a way had to populate the special link to edit the submission alongside its corresponding set of responses. We were able to do that by using Google Apps Script.

Check out next week's blog post ("Fun with Google Apps Script!") for more information on how we did this.

Want to know more about how we did this? Get in touch!