6 Ways to Automate Your Spreadsheets and Make Them Work for You

Gone are those days when spreadsheets were used to organise business operations. Upgrade your spreadsheets with Stackby and convert them into a database that is much simpler and easier to use.

6 Ways to Automate Your Spreadsheets and Make Them Work for You

Gone are those days when spreadsheets were used to organise business operations. Upgrade your spreadsheets with Stackby and convert them into a database that is much simpler and easier to use.

Spreadsheets Aren't Databases!

Here’s one thing that we all need to stop doing right now! Using spreadsheets as databases when both have a purpose of their own. Many people are hesitant to shift to using a database because they think that it would require technical skills to set up or be hard to manage. But modern databases like Stackby are much easier to handle. Let us see how.

How spreadsheets are different from databases 

Visualizing Data

Spreadsheets are just tables. There is no other way of visualizing data in spreadsheets. Yes, you can create charts but they can be quite confusing at times. Databases are more than just tables. They help you create multiple views to focus on particular elements of the database.

Data Retrieval Issues

A spreadsheet shows everything all at once. A database on the other hand keeps the data in the backend and shows you what you need at the current time. You cannot run queries and filter all kinds of data easily on spreadsheets. But, database! It gives you a whole lot of options to retrieve data using a variety of queries.

Read more: Spreadsheets vs Databases - Everything you need to know

Integrity Matters

If you enter data that does not make any sense, your database would alert you. But, spreadsheets would not (unless you have applied data validation on all the columns, which is again an extra task). Databases simply would consider the data type of a column in the beginning and would maintain its integrity forever, without messing up any function.

See 25+ unique column types that are supported in Stackby

Spreadsheets are Static

If you have ten spreadsheets, they would still look like a separate entity to you. You may use all the functions and formulas in pulling the data out of them, yet they will remain different spreadsheets. Databases on the other hand are relational. You just have to link across tables in a database and then carry lookup or aggregations to bring data from one table to another. Moreover, with databases like Stackby - it happens in real-time.

Use free :  Business Expenses category Spreadsheet for your Small Business

Are spreadsheets a waste of time then? Well, no. Especially when you can automate spreadsheets and convert them into a dynamic online database. Spreadsheet automation is a real thing and Stackby is here to make working on spreadsheets much easier for you. So, let us get started with 6 ways you can automate your spreadsheets and make them work for you.

6 Ways to Automate Spreadsheets and Make Them Work for You

#1 Bring Form Submissions

#2 Bring Data From No-Code API Connectors

#3 Get Notifications For New and Updated Rows

#4 Stay in Sync with Spreadsheets and Databases

#5 Create Calendar Events From Databases

#6 Automate Via Link And Lookups in a Database

#1 Bring Form Submissions

Stackby offers you an easy way to automate spreadsheets by using forms. With Stackby, you can create forms in just a few clicks and streamline your workflow. Here’s how.

Create a Stack by adding all the necessary fields that you need. Here is a table Visitor Management System that collects information about visitors such as their name, contact number, the reason for a visit etc. See how to create a form and collect data.

Using forms to collect data 
  • To create a form Click on the View Name and select Add View. Choose Form Layout from the dropdown menu.
  • A new form will be created where you can add all the required fields by just dragging and dropping the required column names given on the left side of the page.
  • You can always change the form setting like the background colour, submit a message, and redirect to a URL after form submission.
  • Once you are done, preview and share your form. You may share it via a URL or embed it in your website or blog.
  • Now, whenever someone fills that form out, a new entry will be created in your stack.

#2 Bring Data From No-Code API Connectors

Take your spreadsheet data reporting  game to the next level with Stackby Automations. Stackby enables users to connect columns to popular no-code API connectors like Google Analytics, YouTube, Facebook Ads, Ahrefs and more. That too, without any code! So now you can pull information, use services or push information by linking Stackby columns to an API. Let us see how.

  • Connect columns to third party APIs to bring data in tables

First, you need to connect the API that you want to use with your workspace. The process is simple: Go to your Workspace -> Click Connect to an API -> Select an integration from the list -> Click Integrate -> Bring your API key -> Click Save

Once you are done, go to the Stack you want to work upon and assign a column in the Stack where you will be integrating your API function.

Click on Column Property -> Select API -> Configure the third party app -> Select the service you want to integrate -> Select the domain -> Click on Apply. Voila! You just connect your Stack column to a third-party API.

For instance, here is how we can connect a column of our spreadsheet with Google Analytics API.

Connecting a column to a third-party API
  • Use 100s of pre-built API functions

Stackby gives you an option to connect columns with different third party services and applications you might be using right now. For instance, Google Analytics, Clearbit, Facebook Ads, YouTube, Twitter and many more. There are many pre-built API functions to choose from in each of those services. For instance, when you connect to Google Analytics, Stackby lets you fetch a variety of data such as users, traffic, page views, Adwords data, e-commerce data and more.

  • Refresh cells to bring real-time data

To bring data to your Stack table in real-time, you need to use Data Explorer. In your API function column, click on the data and it will open the data explorer which will show you all the information (JSON) from that API.

Configure data from data explorer (JSON)

You can select the information that you want to include in your Stack and create columns. Once you do that, all your data will be added to those columns.

Now to see the latest information, just click the refresh button in your API function column and real-time data will be fetched in the rows.

Refresh cells to bring real-time data in Stack
  • Schedule your API automation

I know what you are thinking next. How can we automate this whole process of fetching real-time data in your Stack? Well, we have got you covered.

To automate an API column, go to that column and click on the clock icon. A pop-up will appear that will ask you to create an automation. Click on create automation and set the time and day when you want data to be automatically updated in the Stack. Click on Schedule and finalise your automation. See the example below of how we are scheduling YouTube API automation.

Scheduling API information

#3 Get Notifications For New and Updated Rows

Stackby also makes it possible for you to receive a notification every time anything changes in your Stack sheet. For that, you need to integrate your Stack with either Slack or Microsoft Teams.

Integrating Slack with Stackby

Integrating Slack with Stackby

To integrate Slack on Stackby. Go to Stackby -> Integrations -> Go to Slack -> Click Integrate

It will ask you to set the notification rule. So, click on Add a New Notification Rule. Then you have:

  1. Mention the name of the stack you would like to receive updates from.
  2. You have to select the table in that stack.
  3. Select a channel of Slack where you want to receive the notifications. To select a channel you first have to authorise Stackby by giving some permissions. Once you select a channel it will ask you to select an activity for which you want to receive updates.

You get four options here:

  1. Receive notifications for All Activities in Stack
  2. Receive notifications for Only Your Favourite Rows
  3. Receive notifications for Only Reminders
  4. Receive notifications Only From Responses

So now, just click on one of the activities and allow. A new notification rule will be created that will help you receive all the notifications on Slack for any update in your Stack!

A similar process is used to integrate Microsoft Teams with Stackby and allow receiving notifications in Microsoft team for all activities in Stack, activities in your favourite rows, reminders and responses.

#4 Stay in Sync with Spreadsheets and Databases

  • Bring Google Sheet rows to Stackby via Zapier

Moving further, Stackby allows you to sync your Google Sheet rows with your Stack. This means:

  • Whenever a new spreadsheet row is created, a new row is created in Stack.
  • Whenever a new row in a Stack view is created, a new row is added to the Google Spreadsheet.

This is no magic. It happens with an automation platform tool called Zapier! Here’s how.

First you must create a Zapier account and connect it with Stackby. To do that just follow these steps:

Login to your Zapier Account -> Navigate to My Apps -> Create a New Account -> Search Stackby -> Connect Zapier to Stackby

Creating an automation through Zapier is known as Zap and each Zap includes Triggers and Actions. You can choose different triggers and actions in Zapier based on your requirements.

A “trigger” is the starting point of a zap. So when you want to bring data to Stackby from a third-party app or move data from Stackby to a third-party app, you need to specify when this automation should start or what should “trigger” this automation. For example, in this case, our trigger will occur whenever a new row is created or modified in the Google spreadsheet.

Then, you have to choose an “action” that would take place once the trigger occurs. In this case our action would be creating a new row in Stackby.

Now to create an automation that creates a new row in Stack whenever a new row in Google sheets is created. Just do the following:

  1. Authenticate Google Sheets + Stackby
  2. Choose a Google Sheets service as a trigger from the list given in Zapier. In this case it would be “New Spreadsheet Row”.
  3. Choose an action that must happen in Stackby when the trigger occurs. In this case it would be “Create a New Row”.
  4. Select which data you want to send from Google Sheets to Stackby. And, it is done!

A similar process will be followed if you want to create a new row in your Google Sheet when a new row in your Stack is created.

Integrating Google Sheets with Stackby

#5 Create Calendar Events From Databases

Create Google Calendar events from Stackby

Want to manage your meetings on Stackby? You can sync your Google Calendar events and find every piece of information on Stackby. Again, we will use Zapier to make this automation happen. This means whenever a new event is created in your Google Calendar, a new row will be created in your Stackby table. This means an update in your Stack table when:

  • A new calendar is created
  • An event ends
  • An event is updated
  • Before an event starts and so on.
Sync Calendar Events With Stackby 
How to set Google Calendar Automation

  • Creating one to one, many to many relationships across tables

With Stackby you can bring the data or information from one table to another table and save yourself from the need to go to every table while working. This not only saves your time but helps you see the big picture clearly. A relationship between two or more tables can be defined in three ways: One-to-One, One-to-Many and Many-to-Many.

  • One-to-One: When you link one record of a table to only one record of another table, you create a one-to-one relationship.
  • One-to-Many: When you link one record of a table to more than one record of another table, you create a one-to-many relationship.
  • Many-to-Many: When you link more than one record of a table to more than one record of another table, you create a many-to-many relationship.
Read more: What is a Database?

To link one table to another, you have to use the Link column property in Stackby. See how we are connecting three tables: Graphics, Channels and Graphics and creatives together using Link.

Linking two tables together

Using Lookup to bring information from one table to another

There are times when we just need a small part of data out of a large data set. While using spreadsheets we use a lengthy function called Lookup in such a case.

But with Stackby, you can use the Lookup function in just a few clicks.

Stackby provides a Lookup Column Type that helps you find the data that you are looking for from one table and brings that data to another table.

For instance, if we have three tables Campaigns, Channels, Assets and we want to see the Status of the Campaigns for every Channel, we can use the Lookup data type.
We must first make sure that the Campaigns table is Linked to Channels table so that we can bring information from Campaigns table to Channel table.

Using Lookup to bring information 
  • Aggregate to summarise linked rows to build an automated KPI tracker

Here’s another way to access linked tables in Stackby using the Aggregate column type. The Aggregate column type makes it easier to summarise information in one table. For instance, we have three tables: Campaigns, Channels and Assets. You can see that the Campaigns table is linked to the Channels table.

How to use aggregate column type 
  • Now, we can use the Aggregate column type in the Channels table to find the total budget for each Channel type.
  • Just create a new column called Budget with a column type Aggregate.
  • Select the column that links the two tables and the column that you want to summarise.
  • Select the function (for example, sum, average etc) and a format for displaying the information (like decimal, integer etc).
  • It will then create aggregation of the linked records from other table

Using the aggregate column type is a great method to summarise linked rows and track your KPIs.

Using aggregation column type in a normal Campaign Tracker

Are you ready to upgrade your spreadsheets to Stackby?

Automating spreadsheets not only saves a lot of time but makes things a lot easier to manage. Stackby offers you a variety of ways to automate spreadsheets and make them work for you. This way you can focus on other important tasks rather than just fetching and organising data. Get started in automating your spreadsheet by signing up for free today and take your workflows to the next level. Also check out our pre-built templates to get some inspiration, while you do so.