Create weekly snapshots of totals from other tables

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.

Hi @James_Carrington and welcome to the forum!

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).