Import Data from a Google Sheet into FileMaker or Xojo
Feb 04, 2016 08:01 PM
Google Sheets
If you haven't used Google Sheets, you're really missing out. Google has something called Google Docs that allows you to create files via your web browser that mirror Microsoft Office. Google Docs has Docs, Sheets, and Slides which are like MS Office Word, Excel, and PowerPoint.
Not only that, but you can collaborate on docs. More than one person at a time can be editing the same doc at the same time. It's a bit freaky seeing another person edit your Sheet. Here's what a Sheet looks like:
Get
Looking at the image below, you can click on the blue "Share" button in the upper right corner, the "Share with others" dialog will appear. From here, you can get a URL to the document. You can also choose who can access the document by clicking the area with the red retangle.
Examine
The link to your Sheet will be unique. My link looks like this, but your link should be similar:
https://docs.google.com/a/campsoftware.com/spreadsheets/d/1jsUUmH7vfA-QSxHZs5WzMFyat-5yhUWMjFW2OqkZmUA/edit?usp=sharing
Try clicking your link in another window or another browser and it will take you to that Sheet so you can edit it. If you look closely, you can see a pattern to the URL. First is the base path, then just after "/d/", you'll see the unique ID to your document. After the ID and a slash, you'll see "edit?usp=sharing". That bit of text on the end of the URL determines the action to the Sheet, in this case, edit it.
Hack
This isn't really hacking, but you can modify the URL so you can do other things with the Sheet. For instance:
https://docs.google.com/a/campsoftware.com/spreadsheets/d/1jsUUmH7vfA-QSxHZs5WzMFyat-5yhUWMjFW2OqkZmUA/export?&format=tsv
We changed "edit?usp=sharing" to "export?&format=tsv" which as the effect exporting the file in "tsv" format which means "tab separated values".
Change your URL and open it in your browser. It should download a file that looks something like the following when you open it. I opened my file with TextWrangler.
Use
While downloading the file as text is nice, our goal was it import it into either FileMaker or Xojo. This is the easy part.
FileMaker: Use the "Insert From URL" script step.
Xojo: Use "theSheet = http.Get("YOUR_URL_HERE", 30)".
With either FileMaker or Xojo, you'll now have the Google Sheet data where you can parse the text and do what you need with it. We needed to import the data into a table, so we looped through each row of text and then looped through each field so we could add the field data to the newly created records.
But wait...
There are many other formats you can export Google Docs as, like these...
Sheets: xlsx, pdf, tsv, csv, ods, and zip (a folder with the sheet as html).
Docs: docx, pdf, rtf, txt, odt, and zip (a folder with the sheet as html).
Slides: pptx, pdf, txt, png, jpg, and svg.
Get
Looking at the image below, you can click on the blue "Share" button in the upper right corner, the "Share with others" dialog will appear. From here, you can get a URL to the document. You can also choose who can access the document by clicking the area with the red retangle.
Examine
The link to your Sheet will be unique. My link looks like this, but your link should be similar:
https://docs.google.com/a/campsoftware.com/spreadsheets/d/1jsUUmH7vfA-QSxHZs5WzMFyat-5yhUWMjFW2OqkZmUA/edit?usp=sharing
Try clicking your link in another window or another browser and it will take you to that Sheet so you can edit it. If you look closely, you can see a pattern to the URL. First is the base path, then just after "/d/", you'll see the unique ID to your document. After the ID and a slash, you'll see "edit?usp=sharing". That bit of text on the end of the URL determines the action to the Sheet, in this case, edit it.
Hack
This isn't really hacking, but you can modify the URL so you can do other things with the Sheet. For instance:
https://docs.google.com/a/campsoftware.com/spreadsheets/d/1jsUUmH7vfA-QSxHZs5WzMFyat-5yhUWMjFW2OqkZmUA/export?&format=tsv
We changed "edit?usp=sharing" to "export?&format=tsv" which as the effect exporting the file in "tsv" format which means "tab separated values".
Change your URL and open it in your browser. It should download a file that looks something like the following when you open it. I opened my file with TextWrangler.
Use
While downloading the file as text is nice, our goal was it import it into either FileMaker or Xojo. This is the easy part.
FileMaker: Use the "Insert From URL" script step.
Xojo: Use "theSheet = http.Get("YOUR_URL_HERE", 30)".
With either FileMaker or Xojo, you'll now have the Google Sheet data where you can parse the text and do what you need with it. We needed to import the data into a table, so we looped through each row of text and then looped through each field so we could add the field data to the newly created records.
But wait...
There are many other formats you can export Google Docs as, like these...
Sheets: xlsx, pdf, tsv, csv, ods, and zip (a folder with the sheet as html).
Docs: docx, pdf, rtf, txt, odt, and zip (a folder with the sheet as html).
Slides: pptx, pdf, txt, png, jpg, and svg.