Create weekly snapshots of totals from other tables

[James_Carringto]

Hi all,

Every week I manually copy and past sum total values from various tables and views in my base, to a Google Sheet. I do this to basically create a weekly snapshot of the totals in our Airtable sales forcecast.

For example, 1st of May the total sales was £100,000, then on 7th May the total sales was £110,000. I can then track a £10 increase week on week.

I’m essentially tacking the weekly value of a total of all records in a view, but to do this I have to manually copy and paste the values into a Google Sheet every Tuesday morning. It’s tedious as in practice I actually have to do this for around 30 fields every week.

Would it be possible to instead of copying/pasting into a Google Sheet, to have a script populate a separate table in my base with the snapshots?

For example the new snapshot table could look like this:
Snapshot Date || April Sales £ || May Sales £ || June Sales £ ||
1st May 2020 || £100,000 || £100,000 || £100,000
7th May 2020 || £110,000 || £100,000 || £100,000

So from the above I can see that between 1st and the 7th the sales forecast in april rose by £10,000.

[JonathanBowen]

Hi @James_Carrington - yes very possible to do this. The “how” depends upon the structure of your base, but to sum up some records and write the result is pretty straightforward.

If you want the result in Google Sheets I would tend to drive this from the Google side and have a Google script that pulls the data from Airtable. However, as you suggest, you could do a pure Airtable solution - have an Airtable script that writes the data to a summary table in your base.

[Bill_French]

Hi @James_Carrington and welcome to the forum!

James_Carrington:

Would it be possible to instead of copying/pasting into a Google Sheet, to have a script populate a separate table in my base with the snapshots?

Yep - much of my work in Airtable (which are really data science projects designed to shape information for better analytical objectives) does exactly this. The goal - in most cases is to overcome limitations in rollups that are typically – themselves – limited by the data models and other hastily-implemented design choices or those prescribed by legacy systems.

In any case, your quest is to make your analytical data sources - as I like to describe them - more report or viz-ready. This type of data is best described as analytical data drawn from operational data.

In both of these approaches, javascript is required and a healthy dose of Airtable API experience is typically helpful.

Script Block

This approach - which is essentially an integrated script/API solution - requires manual operation but seems to be quite useful in many situations. One benefit is that deployment to multiple bases and use across different tables is entirely possible if the app is well-designed. Script blocks also avoid the complexities of using external services, API keys, and the security hurdles that come with a more automated process. But one clear advantage is that it supports a clean way to cache-forward organized data sources for reports and other analytical uses into Airtable itself which is fundamental to your question.

Public API

This approach is ideal if the process must be fully automated and/or the target report and viz-ready data must exist external to Airtable. This is also advantageous if there are other systems and data being blended into your analytical data. I find that a number of companies gravitate toward Google Apps Script for this and for many reasons. And it’s not just small businesses; Yum Brands, Honda, and Netflix use Google Apps Script for many integration and reporting solutions.

As you can imagine - the possibilities are very broad.

Here’s an example of a lot of Airtable data being automatically analyzed and the results shaped into an automated report. The entire process is automated and uses Google Apps Script to compute the report data and then Google Docs to layout the report, convert it to a PDF, and distribute it to target stakeholders.

This is a map generated by a deep summation of Airtable data across three tables and then plotted by location, category, type, and area. It uses both Script Block (to trigger the summations), and Google Apps Script (to render the Mapbox results).