Help

Re: Can Airtable's own "Export CSV" be triggered via a script?

5644 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Markus_Wernig
8 - Airtable Astronomer
8 - Airtable Astronomer

Here’s my thinking. I like the simplicity of Airtable’s own “Export CSV” function. Data in a spreadsheet as a simple backup is all I am after.

If “Export CSV” could be triggered via a script, the entire process could be automated. A new record could be the trigger to run this script.

Does anyone know if this is even possible?
Thank you!

26 Replies 26

You cannot trigger the existing “Download CSV” feature in Airtable with a script. However, it is possible to use scripting (either in scripting app, or with an automation scripting action) to convert the existing data to CSV format and save that data to a website. The exact details would depend on where you want to save the data.

Thanks, Kuovonne.
Could the resulting CSV data be saved to a dropbox location?

What is the difference between the “scripting app” and a “scripting action” - the latter is what I am most interested in, since it can be used with the new “automation” feature.

Another question - in a “CSV export via script”, does everything show up as text, even records that are from linked fields? That’s the reason why I am on this search for a simple solution. There is at least one existing third party solution that does what I am looking for - automatically export a table as a CSV file to dropbox. But every third party service has to go through Airtable’s API, which has the following limitation: whatever isn’t text already, like linked records, doesn’t come across as text.

I am grateful for any hint or help. Thank you!

A script app is a script feature that you program and then run as needed. A script action is fundamentally the same, but embedded inside an automated action process. You can see each type in the UI by selecting Automations or Apps.

image

Yes. all text because - CSV requires all text and is therefore not the most ideal way to create a backup strategy.

Not entirely true.

You are free to build an API-driven backup system that uses links to access other content to pull it into a more comprehensive backup process and output. This is where your requirement that it be “simple” becomes “complex”. :winking_face:

And to make matters worse, the vast critical elements of an Airtable solution cannot be accessed by the API or the integrated CSV export. Business logic embedded in formulas - for example - cannot be backup up at all. This article covers a number of these backup and restoration issues.

Thanks, @Bill.French

I appreciate you walking me through the complexity of the task at hand.
I fully understand where you are coming from and yes, a CSV export will never be an actual backup solution for a complex Airtable base with formulas and everything.

In my case, Airtable’s own “Export CSV” is perfectly fine. I am just interested in having our company data (“inventory”, “projects”, “income”) backed up in good-old spreadsheets - and I’d like this to happen without me having to do it manually. I am not looking for a way to preserve the complexity of the entire base in a backup solution (however nice that would be).

If only Airtable would allow their own “Export CSV” function to be an automation “action” that could be “triggered”, I’d be perfectly happy. Well, I did suggest it to them. Let’s see what happens.

And to make matters worse, the vast critical elements of an Airtable solution cannot be accessed by the API or the integrated CSV export. Business logic embedded in formulas - for example - cannot be backup up at all. This article covers a number of these backup and restoration issues.

Not true anymore, I played around with the Airtable Apps, and built https://github.com/UnlyEd/airtable-app-bases-compare, I wanted to deep-compare bases using an app, but faced a limitation that those data are only accessible for the current base.

So, I changed my mind and eventually build a “print-base-structure” airtable app. Then, I use a diff tool to compare the generated output, and it allows me to see what structural differences there are between two bases.

It’s much better than the API, there are more insight about the base structure (type of fields, options, etc.).
There still isn’t everything I’d like (like, the actual formula being used), but it’s better than before. More use-cases are possible compared to the API.

Off topic, but I’m also currently working on a “fetch-airtable-dataset” project that I might open source eventually.
The goal being to fetch like 1-100 tables at once, sanitize the dataset, and then perform consolidation (resolving relationships) based on one own’s business needs. It’s a tool meant for developers.

So it is true – the API is [still] not able to expose the business logic of an Airtable solution (such as field formulas) and your approach doesn’t [apparently] expose the field formulas either. I’m curious why you believe my assertions are untrue. To be clear - I look forward to the day when I am wrong about this. :winking_face:

Lastly, please know that the article you are claiming is untrue was penned in 2019 - a lot has changed with the script environments and effortless access to the complete data schema through javascript.

Indeed, much can be done with React and deep introspection of bases. Is it practical for most users? Probably not.

In your Githib project I didn’t see any evidence of actual formulas in fields. Is that possible with your approach? If it is, you should build an actual backup/documentation service that creates content to help users capture the true details of their solutions.

Ahmed_Elagami
7 - App Architect
7 - App Architect

Hi Markus,

Well, I was just thinking exactly the same thing, I am using 2 approaches to achieve that
1- I randomly go to History and goes to take a snap of an existing base, what it does it creates another copy of your base to your workspace depending on your account subscription without affecting your current base.
image
image

so this way gives me the option to go back for 1 full year of history of my base, around 10 versions for the last 24 hours and at least 1 for each previous day.

2- I use open2air paid service to create a back totally automated.
so once you link your open2air account to Google drive or dropbox, then you can create a backup plan to backup your existing base every hour every week or every month. it creates a CSV file has all record Ids and all files, all attachments. you can download that anytime, and build up your base again if ever needed.
full info in this link

I hope that helps
Thanks
Ahmed

Thank you so much, @Ahmed_Elagami, for these great tips. I’ll start using snapshots as well. And On2Air Backups might indeed be exactly what I was looking for.

They seem expensive at first, but once you boil it down to what you actually need, it’s a no-brainer for business data security.

I’ll do a test run with them, but maybe you can answer one question first.

Like every Airtable user, I’ve got a ton of linked records and lookup fields. Do these fields come across as data in their CSV export - the same way as when I do a manual “Export CSV” through Airtable’s own menu?

Thanks - Markus

Ahmed_Elagami
7 - App Architect
7 - App Architect

Hi Markus
Glad that would help, I believe when you download a CSV from a linked data it will keep as text, it won’t be linked anymore, it’s exactly the same if you create a side column " Formula" Type and you just type the same linked column it will read it as plain text.
I guess that would be always the case if you uploaded a backed-up CSV file to a new Airtable base.

Thanks
Ahmed

Hi @Ahmed_Elagami, I just heard back from On2Air. It’s the same problem as with the another automated CSV backup solution through miniextensions.com (which offer the exact same thing and even more solutions for a flat rate per month, regardless of the amount of records):

When backed up as CSV through an outside service (on2air.com for example), a linked record field does not come across as a name (data) but as an ID. That’s how Airtable’s CSV API works.

WRITER (Airtable Linked Field) | Steven King - becomes
WRITER (CSV) | recgbyplnp4Qy7BOVQ (CSV) - when exported through Airtable’s CSV API (which on2air.com or Miniextensions.com use).

The “workaround” is to add an extra field next to every linked record field and configure it either as a “lookup” or as a “formula” field (pointing to the linked field). That way the additional field shows actual “text” that doesn’t turn into garbage text once exported.

I’ve had that setup with miniextensions.com. It’s just that my inventory table has so many fields (120+) that adding even more just to work around the “garbage text” problem makes it a nightmare to work with. (If only Airtable had a better way to re-arrange / rename tables with lots of fields…)

Airtable’s own “Export to CSV” function doesn’t have that “garbage text” problem. All they’d have to do is allow us to use this function as an action with their new automations.
And no, attachments would not get backed up that way, but miniextensions.com has a stand-alone automated solution for that too and it works really well.

To clarify, there is no CSV API for Airtable. They only have one API, which is their standard REST API. All third-party services that access Airtable data use this same API: on2Air, miniExtensions, Integromat, Zapier, etc.

Thanks for clarifying, @Justin_Barrett - I am not a coder, so please excuse my ignorance :winking_face: .
For us “regular users” Airtable can quickly turn into a pretty complex beast. The moment we want more than a simple base, we couldn’t do it without the help of all the experienced pros on this forum! So thank you!

One person’s garbage is another person’s gold. I don’t see this needing a workaround unless you intend to flatten (denormalize) the data.

Let’s be honest, the record IDs exist because you wanted the data model to support relational connectedness, right? Now you expect Airtable to ignore that and give you something different when exporting said data.

Any database expert charged with the responsibility of reinstantiating your data model in a recovery Airtable crisis or in a new relational database system will need the record IDs, not the text representations. As such, the purpose of the exports should be clearly defined - is it for disaster recovery? Reuse in a spreadsheet?

It does actually, and by “better” I assume you mean different. :winking_face: The way I manage to create alternate export formats is to:

  1. Define the export requirements in the context of very specific requirements.
  2. Use the API or Script Blocks to build precisely the export format required.

Indeed, because in that context, it assumes you want the data, not a representation of the linking relationships (which should not be regarded as “garbage text”). More precisely, it’s not helpful for the use case you may have in mind.

But, if that use case is for disaster or crisis recovery and restoration, that garbage text is going to turn out to be little golden eggs.

Thanks, @Bill.French for clarifying. Everything you say is of course correct, however - and maybe I should have been clearer as to why I need “normalized data” instead of record IDs. You are again correct that there are different scenarios for when record IDs and when text representations are needed.

I run a music library / publishing company. All of our music tracks come with a ton of metadata that has grown to a whopping 120 fields per record. This is not by choice. Various clients, sub publishers require these fields. We need to export data in various spreadsheet configurations depending on where our music is uploaded to.

So I am faced with two questions - what’s the easiest way to re-arrange this vast number of fields to fit various spreadsheet requirements? And how do I make sure that I am getting text-data on the other end and not record IDs?

That may be easy for you, but for every non-database expert / regular Airtable user, your “run everything through a script” way is an insurmountable task. We’re stuck with the manual “re-arrange 120 fields”, “export everything by-hand” way.

I have posted a job offer to write a CSV database export script - so far, no luck in finding somebody. :man_shrugging:t3:

Let’s not forget one thing - Airtable was created to be “the database for the rest of us”… :winking_face:

One more thing, @Bill.French, about arranging 120 fields in a table, so that I can export different CSVs for different scenarios.

Isn’t that exactly why Airtable created “views”? So that we, the regular users, do not have to be database-experts who can take care of these things via custom scripts.

I think it’s fair for me to think about user-friendly ways to wrangle 120 fields into a new order. Airtable’s “hidden fields” drop-down menu is pretty good already. Lots of ways for regular users to move things around and turn them on and off. It just wasn’t built with 120 fields in mind. :winking_face:

I am still open to outside help. I am always looking for ways to make things simpler, more user-friendly and more time-saving. And I am also not averse to getting into the habit of working with scripts.

I’m not sure I understand the connection to views - I was commenting solely on the two extraction behaviours - one with record IDs and another with field transformations.

  1. Export is typically considered the “consumerized” CSV version for people who just want the data to use elsewhere.
  2. Snapshots (or backups) are just that; they are intended to give you a chance to recover in the event of data loss and record IDs are crucial for that step. As such, I was simply making the distinction that it’s not “garbage text”.

Indeed. All conversations here are fair. I am only suggesting that it’s unfair to refer to record IDs as “garbage text” because in some [CSV] use cases, it is the opposite of garbage. ;-).

While all third party services use some form of API, the don’t necessarily use the same API. Evidence suggests that Zapier and some other services use a different, non-public API with more features.

And this is most evident (as an example of secret APIs) in this long-running attachment issue where we can see and replicate the failure in the public API, but not via Zapier.

image