Today, I’m going to give you a bit of a walk-through on using Airtable, a free online database–and show i’m using it to re-organize my existing blog. First, we’ll look at how to transfer data from Google Sheets to the Airtable–and how to set up an Airtable database for the very first time. Airtable is a great application–and it’s a wonder more people don’t know about it!
This post is in reference to the Daily Post Word of the Day: wonder.
What is Airtable?
Airtable is an online database–very easy to learn, flexible and powerful. It’s also free and fabulous. Airtable can be accessed from your phone, your computer, another computer–and you can share information and views of your data with other. Below is a link to the a view of the database of posts that I created — and you, dear reader, can take a look at all of my posts so far, grouped by the new categories and subcategories that I plan to put together in the new, re-organized blog!
Click on the link in the table above that reads “View larger version” to get a pop-up view of the final table. Pretty cool, yeah? And not at all hard to do. In the next two blog posts, I’m going to show you how to organize and track your blog using Airtable’s many different views and capabilities. The set up is a little bit tedious, but I’ll show you why it’s all worth the bother.
First: Google Sheets
As I showed you in yesterday’s post, I copy-pasted the list of posts and all of their information on the post pages to Google Sheets. Today, I tediously cut and pasted the views, likes and comment statistics into single lines like so:
Setting up a “Base”
After you create an Airtable account and log in, you’re taken to a large array of database templates to choose from. Airtable organizes them into “workspaces” — some you might share with other people, some only for yourself but whatever you choose, don’t worry–you can move it later. Start with a blank database (or “base” in the hipster Airtable argot)–click the plus sign.
When you click the + sign, you get options.
Importing a spreadsheet is really cutting and pasting from Google Sheet.
When we choose “Import a spreadsheet, ” we get this box above. We paste in our rows and columns from the Google spreadsheet.
Airtable expects the first row to be header data. If you didn’t add headings to the columns, (I sure forgot), then UNCHECK the first row header checkbox. If you did remember, leave it checked. Click the “import pasted data” button–and there it is. (Airtable allows you to present views of your database in your website. I’ll show you how this all works in tomorrow’s post).
Another method: Creating a database from “scratch”–or adding data from a spreadsheet into an existing database
You can also create a database from scratch (a slightly more difficult way) — then cut and paste directly from Google Sheet into the data base. Here’s what you’ll see if you choose this method. I already had an Airtable for my blog, so I created a blank table in that existing Airtable database. The initial blank table looked something like this before I put the data in:
I chose all of the rows and columns from the Google spreadsheet, copied them, went back to the new Airtable table, then choose the top, left (blank) field and pasted the rows and columns.
Airtable pops up a dialogue box to ask me if I wanted to expand the table to fit all the 82 rows and additional columns to fit the data into the table. I clicked okay and voila!
Adding Fields in Airtable
After you’ve either imported a database or added your data into a blank table on an existing database, we can click on the small downward triangle at the end of the field name to CUSTOMIZE the fields Here, I clicked on an existing field called NOTES. If you imported the data from the spreadsheet, this field will be called Field 2. You can choose the “Rename field” to set the names of the fields if you didn’t include a header row when you imported the spreadsheet.
Renaming and Inserting a new field to the left (or right) of an existing field
I inserted a field to the left by choosing the “Insert left” option.
Then I renamed the “Notes” field (in my table)–the Field 2 in a table that was imported–to “Pub Date.”
Next, I cut the fields that said “1 day ago”, 2 days ago, etc. and pasted them into the new, as yet unnamed field. Field 6 in the table I made using method 2. I did this because I want to change the format of the Pub Date field from “text” to an actual date with a time stamp. I moved the “text” (non-date) data to the side so it will be out of the way when I convert the other data into the date format.
Changing the type of field (field formats)
Airtable offers lots of field format types. Choose the “customize field” option and you’ll get a list of all these various formats
After choosing the “Customize field type” option, I had to click on the downward pointing triangle the far right of the “Single line text” format to call up all the available field types. The default data format setting is “Single Line text.” Clicking on the downward pointing arrow next to the blue “Single Line text button will fetch up all the options.
I chose the Date field. I also chose to include the time field by sliding the little slider so that it turned green.
Lastly, a bit belatedly, I renamed the Notes field with a new name: Pub Date.
Dealing with the Pub Dates for Posts with no post date
Now, when I double-click in the new Pub Date field, it will expect a date and time.
I have to choose the date from yesterday. Unless I also enter a time, Airtable will default to the CURRENT time.
Changing the Views, Likes and Comments from “text” to “numbers”
The views, like and comments need to be changed from a line of text into actual numbers. This pretty easy, but you’ll lose all the text when you convert the format from “Single Line of Text” to a Number format.
First, I change the name of the fields to “Views”, “Likes,” and Comments, respectively.
Then, I changed the format from Single Line Text to Number. This action will strip out the words (1 like, 8 likes, 4 views) and leave only the number. After changing the format to Number, I also changed the format of the number to “Integer.”
You’ll be asked if you want to convert the field type. Convert will strip out only the text portions.
At the bottom of the screen, you’ll see all the numbers added together:
Airtable will also provide you with other statistics for number fields:
With a simple click and I can discover that the average number of views for my posts is 8! (Okay. 8 is not that impressive. 😀 It’s a small blog. Small but growing!)
End of Part 1
Setting up the Airtable version of the Google spreadsheet may see a bit tedious. You might wonder why anyone would want to do this. Isn’t Google sheets enough? I’m going to show you why it’s worth the effort. Airtable is not just for blogs — it can help you keep track of inventory, costs–all kinds of information for running your blog.
In the next post, I’ll show you how I’m reorganizing my blog with categories, sub-categories and a new tagging strategy to improve audience engagement and SEO value. More soon! Warm regards, Lola.