Skip to main content

Pulling data to sheets in the correct format

  • July 20, 2020
  • 6 replies
  • 34 views
D
Forum|alt.badge.img+2

Hello,

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?

6 replies

B
Forum|alt.badge.img+19
  • Bill_French
  • Inspiring
  • 3263 replies
  • July 20, 2020

Hi David, and welcome to the community.

Not easily, so here’s how I do it:

  • You need to abstract your API ingested data away from the visualization-ready sheet.
  • First, consider importing all columns of data to Sheet (a). You really don’t need to be concered about the order or even the formatting in this stage.
  • Create another sheet (b) that organizes the data from Sheet (a) and places it in the format Data Studio expects.
D
Forum|alt.badge.img+2
  • David_Van_Eeckh
  • Author
  • New Participant
  • 2 replies
  • July 20, 2020

Hi David, and welcome to the community.

Not easily, so here’s how I do it:

  • You need to abstract your API ingested data away from the visualization-ready sheet.
  • First, consider importing all columns of data to Sheet (a). You really don’t need to be concered about the order or even the formatting in this stage.
  • Create another sheet (b) that organizes the data from Sheet (a) and places it in the format Data Studio expects.

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?

G
  • Garrett_Loughra
  • Participating Frequently
  • 5 replies
  • July 28, 2020

Double check in (File > Spreadsheet Settings) that you have the correct Locale selected.

B
Forum|alt.badge.img+19
  • Bill_French
  • Inspiring
  • 3263 replies
  • July 28, 2020

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?


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.

D
Forum|alt.badge.img+2
  • David_Van_Eeckh
  • Author
  • New Participant
  • 2 replies
  • July 28, 2020

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.


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.

B
Forum|alt.badge.img+19
  • Bill_French
  • Inspiring
  • 3263 replies
  • July 28, 2020

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.


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.


Terms & ConditionsAccessibility statement