Skip to main content

Image export workflow to google spreadsheets


Hi here’s a workflow I’ve been using for quickly exporting out an airtable base’s table into google spreadsheet.


There’s a number of reasons why you would want to do this:




  1. You have a file that needs to be analyzed by someone for a one time use. This could be your accountant, purchasing dept, etc.


  2. You need a better printout of your airtable data. Airtables printouts right now are okay, I have issues getting a nice printout sometimes. Google spreadsheets has lots of flexibility here


  3. You want someone to do data entry for your base but would rather have them enter the data, send it back, so you can double check it before throwing it into your airtable base


  4. You want your cells to do anything for a few niche cases - Airtable is great in that it doesn’t allow you to do certain things… but sometimes you might want to have a specialized formula pasted randomly throughout your google spreadsheet.


  5. You’d rather not use excel for a number of reasons, possibly because it doesn’t fit your use-cases compared to google spreadsheets


One issue you’ll run across when copy pasting your base into google spreadsheets is the images won’t transfer over right away.


Here’s the solution:



  1. CTRL+A select everything from your current view on your base’s table

  2. CTRL+C copy

  3. CTRL+V paste into a spreadsheet


For reference, I am using my doggo base that I used for my bulk image downloader example



Pasting this into google spreadsheets looks like so



NEXT STEPS


For your images, there should only be one image in that column. If you are using multiple images in a column this won’t work




Data should look clean now



NEXT NEXT STEP


Your going to want to do two things to make the image play nicely.



  • Use a formula for embedding image sizes

  • Adjust the row height


Adjusting the row height looks like so. Click the first row, hold SHIFT, click last row, → resize rows. Enter new value


Use =IMAGE(B1,1) to have the image resize into cell, and then drill down the image




You can go back and resize the row height as needed. Image should auto adjust. After that you can print off the file




TL-DR Too Long, Didn’t Read


If you want a quick way to export your airtable to googlespreadsheets, use the following approach.



  1. Have all the data you want to export out on your screen in one table inside your base. This means do your Lookups, linking fields, etc ahead of time

  2. CTRL+A select all data

  3. CTRL+C copy all data

  4. https://docs.google.com/spreadsheets/u/0/

  5. CTRL+V paste data

  6. Copy your image column data

  7. https://www.browserling.com/tools/regex-extract-matches


  8. /http[s?]://.*(.png|.jpg|.JPG|.jpeg)/g , dump your data, extract matches, copy and paste results


  9. =IMAGE(B1,1) in column C, filldown the rest

  10. Select first row, hold SHIFT, select last row → right click, resize rows → enter new value

  11. Do number 10 again until you like the size of your images


Now you can do whatever you want with data.


Print it off, let someone do data entry on it, do some one time analysis, use all the features in google spreadsheet, etc.


Take all the work done by the other person, check for quality, import it back into your airtable if needed.


Some nice features of google spreadsheets is seeing what the other person is clicking in real time. Also, now you have lots of disposable quick collaborative workspaces

8 replies

  • Author
  • Known Participant
  • 28 replies
  • April 10, 2018

other time saving tips




  • Use page notes chrome extension to save the regular expression. So you can access text notes on that specific page







  • Bookmark the tool page on chrome bookmarks




  • If you do this workflow often, you can cut out steps 9 to 11 with a premade formatted google spreadsheet.




Other useful tools for post-processing once its in google spreadsheet




  • I use http://txtformat.com/ often for many database/ airtable related tasks, its used to find/replace data in bulk. Useful if you need to format data in the google spreadsheet after. Sometimes referred to as an “ETL” extract transform load process.




  • https://www.browserling.com/tools/text-truncate is good to get the first few characters of data on cell. If you have long descriptions for items, but don’t want to see entire text printout




Notes on why to use google spreadsheet collaboration this way


Googlespreadsheet workflow I outlined is best used if your working with someone outside your team. Say you outsource data-entry tasks on fiverr.com or something along those lines. Or perhaps someone to write product descriptions. You haven’t vetted how reliable they are yet. You send them hard copies of what data they need to enter.


You don’t expect to receive that data until 2+ weeks out all at the same time. Its not for incremental deliveries. Its for milestone deliveries.


You also need to do a random spotcheck to ensure data reliability. Google spreadsheet has ways of catching data with errors easily. For instance, conditional formatting for “duplicate entries”. You might also need to post-format data entered from that person as well. Perhaps they might have entered phone numbers with different formatting conventions such as 444-444-4444 and also with (444)444-4444.


Because you do error checking this way, you ensure your airtable base is clean data. You also get access to airtable’s collaboration / errorchecking tools as well.


General things about google spreadsheet collaboration




  • You can print out some nice pricing sheets with this workflow. If you need to send an updating pricing list to clients this is a great way to do so.




  • you can protect columns of data from being edited in google spreadsheets. So if you send data to someone for bulk data entry, you can be sure your primaryKeys (first column of data) are the same, when its sent back to you




  • If you add new rows of data in your airtable database after, the row numbers won’t match the spreadsheet you sent out. You can have a temporary googlesheet do vlookups as a solution




  • Author
  • Known Participant
  • 28 replies
  • September 23, 2018

had to use this again, realized one of the formatting strings was incorrect


for browserling https://www.browserling.com/tools/regex-extract-matches, I had this


/http[s?]://.*(.png|.jpg|.JPG)/g


when it should really be this:


/http[s?]://.*(.png|.jpg|.jpeg|.JPG)/g


Is there a solution to export images so they are physical image files on a hard drive (with file names that match something in Airtable)? This would be VERY helpful and I have a need to use it today, so hoping someone may have found a solution. Thanks so much.


  • New Participant
  • 1 reply
  • June 29, 2020

@Vincent_Tang This was incredibly helpful! My only question: is there a reason why you don’t use Google Sheets REGEXEXTRACT formula? I tried using the same regex you provided and it doesn’t work, but I wanted to know if there’s any way to use it within google sheets?

I only ask, because not all of my issues have images, and the tool you linked removes empty lines, so it would be helpful to do it right within the sheet.


  • Inspiring
  • 368 replies
  • July 5, 2020
Brenda_Ginsberg wrote:

Is there a solution to export images so they are physical image files on a hard drive (with file names that match something in Airtable)? This would be VERY helpful and I have a need to use it today, so hoping someone may have found a solution. Thanks so much.



Yes, we built this solution.



Vincent_Tang wrote:

had to use this again, realized one of the formatting strings was incorrect


for browserling https://www.browserling.com/tools/regex-extract-matches, I had this


/http[s?]://.*(.png|.jpg|.JPG)/g


when it should really be this:


/http[s?]://.*(.png|.jpg|.jpeg|.JPG)/g


Vincent , Your solution is very useful and a big time saver . Now that Airtable has introduced 3 REGEX functions , do you think the extraction can be done inside Airtable by using the REGEX_EXTRACT(string, regex) function. I do not understand coding and it would be great if you can tell me if this can be done and how to do this .


Hari_Balasubram wrote:

Vincent , Your solution is very useful and a big time saver . Now that Airtable has introduced 3 REGEX functions , do you think the extraction can be done inside Airtable by using the REGEX_EXTRACT(string, regex) function. I do not understand coding and it would be great if you can tell me if this can be done and how to do this .


Vincent, I was able to figure it out, Thanks.

I just used REGEX_EXTRACT({Notes},“https?://.+[^)]”) .

With this I do not need to use browserling for downloading image files after renaming them.


Thanks a ton


ScottWorld
Forum|alt.badge.img+20
  • Brainy
  • 8713 replies
  • November 10, 2023

Exporting images is a little bit trickier these days because Airtable's attachment URLs expire after 2 hours of being accessed outside of Airtable, so I typically use Make's Airtable Automations to help my clients export their images out of Airtable and into other apps.

p.s. If you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld


Reply