Jul 20, 2020 10:16 AM
I am really new to google apps script and pulling data from Airtable, I’m using the code from this post on Medium. One problem I’m having is that every time it pulls from one of my 3 tables it brings in data in the wrong format, so that then Data Studio chokes on it. I added a couple formatting lines to the Apps Script, by hard coding which column to re-format, but then the columns aren’t consistent when they re-import. So, is there a way to pull the data from Airtable locked into the correct format, or am I better off using a column header in Apps Script to identify the formatting changes, and if so, how do I select column based on incoming header?
Jul 20, 2020 10:25 AM
Hi David, and welcome to the community.
Not easily, so here’s how I do it:
Jul 20, 2020 11:20 AM
Thanks Bill, can you give me an example of how I would connect the column in sheet (a) with the column in sheet (b)? Is there a scripting approach to it or do you use if/then statements in cell formulas?
Jul 28, 2020 01:28 AM
Double check in (File > Spreadsheet Settings) that you have the correct Locale selected.
Jul 28, 2020 06:28 AM
It’s a native capability in all spreadsheets - referencing data in col(x) in sheet (a) to col(y) in sheet (b) is very simple.
With this capability, it matters not how the data arrives in sheet (a); it’s sheet (b) that will possess the concise and consistent columnar format that your other analytics rely on. You just need to program that in spreadsheet formulas.
Jul 28, 2020 07:05 AM
Yeah, I guess I was thinking of a way to automate it with each new import, for example, if column C was the Address column on Sheet 2, have it find the address data imported in Sheet 1 with an if statement in Sheet 2 Cell C2: IF (Sheet1!A1=“Address”,Sheet1!A2, ((then continue with nested ‘if’ statements for each column looking for the Address data)). Seems clunky though.
But, I ended up taking the easy way out and used coupler.io which is working great, and we’re not going to have enough data to get to the paid level. Thanks again gentlemen.
Jul 28, 2020 09:37 AM
What’s not automated about the approach above? The data flows into a sheet; the sheet is then extracted into another format. There are no human actions required for this.
Glad you got it working.