Airtable – Spreadsheet or Database?

Join Community: http://Community.AppyBuilder.com

Drag, Drop, Snap Blocks, Build app

Airtable is a non-visible component that is a spreadsheet-database hybrid, with the features of a database but applied to a spreadsheet. Airtable can store information in a spreadsheet that’s visually appealing and easy-to-use, but it’s also powerful enough to act as a database that businesses can use for customer-relationship management (CRM), task management, project planning, and tracking inventory.

AppyBuilder has component and blocks to access and manipulate your Airtable spreadsheets. To start using Airtable:

1. create an account HERE or simply log into Airtable. Next, create a spreadsheet using “Add a base” and then selecting “Start from scratch”

2. After assigning a name, you’ll be brought to a screen like below. I

• Item 1 is the name of your spreadsheet (also referred to as base-id).

• Item 2 is the name of your table (store away this table name)

• Item 3 is the name of your view (Store away this grid name)

• Click item 4 (+ sign) to add column.

• Click on each header (item 5) to either change default column name, or delete column or add a new column.

• Click on Account (item 6) to retrieve your account API Key (store away this API Key)

Generating API key is by going to your account (item 6 above) and then generating (or viewing) an API Key:

4. Finally, you’ll need to retrieve an id (BaseId) that is a unique id for the selected table. Goto https://airtable.com/api and then select the spreadsheet that was just created. When the page opens, scroll down to “AUTHENTICATION” and on right hand side select “node.js”. Take note of this BaseID.

To manipulate (Add, Read, Update, Delete) data, goto your AppyBuilder project and fro Storage category, select Airtable and drop onto layout:

The properties section of Airtable component will now have the properties shown below. Paste your Airtable API key and Base ID into properties above. Also change the TableName and ViewName to match your Airtable names (use defaults if you haven’t changed them)

In blocks-editor, you can find may blocks to retrieve rows or add, delete, update row / cell data:

There are also event-handler blocks that are associated with the blocks. For example, to retrieve row data, you’ll use Airtable.GetRow block. Once result is retrieved, it will trigger Airtable1.GotRow event-handler:

AppyBuilder contains may other blocks for handling single or multiple rows or columns or even single cell:

Join forum community for tutorials, discussions, technical questions

http://Community.AppyBuilder.com

Website: http://AppyBuilder.com

Share, Like us below 🙂

 

 

Reading Google FusionTable or Spreadsheet data into List

Google provides FusionTables and Spreadsheets web applications for maintaining tabular data. These Google applications allow users to create, update and modify data and share them with other users. Google also offers API for accessing these data using a subset of SQL statements sent over HTTP requests.

In this blog, I’ll show you to use App Inventor to issue SQL statements for accessing this data and how to convert data so that it can properly be displayed into such components as ListPicker or ListView.

Below are examples of Spreadsheet and FusionTable data that will be used for this blog.

Creating, providing sharing and getting the tableid or the key is beyond the scope of this post. However, if there are enough interest/comments, I will provide another post detailing the process.

As shown in figure below, our design will include 2 buttons to query Google Spreadsheet and FustionTable. We’ll use a Web component to send HTTP requests and receive results. We’ll use a label to display raw results and a ListView to display formatted list result.

 

 

Figure below shows the logic that is used to send query to FusionTable or Spreadsheet. It also shows the implementation that is used to convert the response from CSV Table to List. As seen in figure below, we are using URL endpoints to invoke RESTful API. The URL takes parameters such as table name, output format, SQL statement. Once the HTTP request is sent, a response will be sent back which will trigger Web GotText event handler. Please note that blocks below do not include any type of validations and in production, validation is required to ensure that response code of 200 is received back.

The responseConent that is received back is in form of CSV table like below (R = row, C = column):

• “R1C1″,”R1C2″,”R1C3”
• “R2C1″,”R2C2″,”R2C3”
• “R3C1″,”R3C2″,”R3C3”

We use “list from csv table” to convert responseContent to a list of list as shown below; keep in mind that parenthesis represent list:

((R1C1 R1C2 R1C3) (R2C1 R2C2 R2C3) (R3C1 R3C2 R3C3))

Data is now converted to list of lists and we now can set elements of our ListView to this list using ListView.Elements. However, since we are dealing with lists of lists, the elements in the ListView will include the inner list parenthesis and will look like below:

• (R1C1 R1C2 R1C3)
• (R2C1 R2C2 R2C3)
• (R3C1 R3C2 R3C3)

In order to remove the start and end parenthesis, I now use a procedure called stripParens that will loop through each list, strip the parenthesis and return result as a list:

Below shows the result querying the Google Spreadsheet

Source:

Download source HERE

Interested in Ai2LiveComplete? Please check features and link to demo site  HERE.  Alternatively, if you have problem accessing the doc, you can check document, HERE