CampSoftware Logo       FileMaker Certified Have a question? Email Us


Home | Products | Contact & About
Blog | Forums | Wiki | Twitter | RSS Feed

Import Data from a Google Sheet into FileMaker or Xojo


2/4/2016
Hal Gumbert

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:


Google Sheet

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.


Sharing Link

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.


TSV File

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.


Evernote Is Nice, But I Need To Do's


12/28/2015
Hal Gumbert

My friend Tim Dietrich and I work on a bunch of projects together. Although we live hundreds of miles apart, we talk during the day quite a bit and today we were talking about an interesting article: Transfer Your Notes from Evernote to Apple Notes

Satisfied?

The thing is that Tim and I love Evernote, but we're both not 'satisfied' with it. It works great for notes of course. In my case, I use Evernote to store notes and web pages that I might also want to find later.

Sharing Notes could be made easier though. I've tried sharing Notes and it's just not a clear process. If someone shares a Note with me, it should just be in my list of notes which I could place in ANY of my Notebooks. I'd also want to easily see a list of all my shared notes no matter if I shared it or was shared with me.

How I Use Evernote

The Evernote Notes I touch daily tend to be Notes that I treat as To Do's. I have a 'Stack' which is just a folder of Notebooks called 'To Do'. I have Notebooks for Work, Ideas, Our Website, Blog, Home, and our RV. In each Notebook, I create a Note for each To Do. Stacks are nice as you can click on the Stack and see ALL the Note for EACH Notebook or just click on a Notebook and see those Notes.

Having Stacks is huge for me so I can see more than one Notebook at a time. I could really live without Stacks though if I could have hierarchical Notes. This would mean that I could create a Note for a Project and then create sub Notes for my To Do's and optionally share those Notes with others.

To Do Notes

In my world, each of these note is REALLY a To Do, but I don't have a way to mark it like that. Evernote does have the ability to add checkboxes inside Notes. It's cool, but a bit weird feeling. I'd like those to remain in Evernote so I could create a Note as a To Do and have Tasks to tick off as completed.

What I really, really want.

  • Add Checkbox to a Note in the header to signify that that Note is a To Do.
  • Add Note fields to be exposed if a Note is a To Do for Date Due, Date Completed, Priority. These should be able to be seen in lists.
  • Assigned To ( another evernote user ) would be for assigning a To Do to another person which would be like Sharing a Note.
  • Hierarchical Notes as I've described above.
That's pretty much it. I'm getting back to work!

Trees for Representing Referrals and Marketing Sources in Xojo and FileMaker


11/28/2015
Hal Gumbert

Fuzzy Vision

For some time, we struggled with how to visualize word of mouth referrals and marketing sources for one our favorite clients. We knew that both word of mouth and the advertising were doing something, but it was hard to see the which was stronger with standard reporting tools.

Our goal was to show how much each customer spent and a total of how much each referral spent. Knowing this can tell us if a particular marketing source is working or not. For example, is it better to spend money on Groupons or Radio Ads? So, I quickly sketched this to represent showing referrals from a radio staton advertisement:


Label Before

Organizational Trees

Last week, I had an idea to show this information as an organizational chart. I messaged my good friend Tim Dietrich if he ever used or knew of a good org chart and he found Google Charts Org Chart. The example source was very simple. There's some javascript, a bit of html, and one row of data for each item on the tree.

Upside Down Trees

In this database, we have a field for the Referral Source. When people are asked, 'How did you hear about us?', that information is entered. It could be a referral from a friend, radio ad, groupon, or one of many other options. If it's from a friend, we store the friend's name and ID which allows us to show a list of all the people that a particular person referred. Since Tom can refer Sue and Sue can refer Joe, the org chart is a perfect way to show the relationships.

Adding Nutrients

So we created two methods / scripts so we could build the chart from the perspective of a particular customer or a marketing source. The fun part was creating the recursive code. Org charts are represented by a data structure called a "tree" where each node is a "parent" of their "children". This means that a customer / parent can have one or many referrals / children. It's recursive since the code simply adds the customer to the chart and then the code is called again for each of the childrens and yet again for each of their children until someone has no referrals.

Once we had the chart drawing correctly with the customer name, we added a dollar amount for the total of each invoice. Since we knew the total of all the invoices for a particular customer was, it was easy to add total the invoices for each customers direct referrals. Finally, we added a total for all the every one below on the same branch of the tree.

Strong Roots

Below is what one of our Marketing Source Referral Trees look like. The top row represents handing out flyers at the Cantina. The second row are the customers who came in and said they heard about it from the Cantina Flyer. Any customers below the second row are people referred by someone above them.

The first dollar amount is a total of the invoices for that customer. The second amount is the total for their direct referrals. The third amount is the total of everyone below them.

As an extra bonus, we added a Tooltip and a URL to take us to the FileMaker database and find the clicked customer. That same method will work in Xojo too!

With this new tool, we can create documents for any marketing source and see what is working and what isn't rather than guessing!


Xojo For FileMaker Developers Course: Survey


11/12/2015
Hal Gumbert

Way Back

I've been using Xojo since 1998 and FileMaker since 1991. For years, I've used the both FileMaker and Xojo separately, using FileMaker for database solutions and Xojo for building apps. Internally, we'd mix it up and use FileMaker to track our sales of our Xojo apps, but never build a hybrid solution.

Fast Forward

Last last year, Tim Dietrich told me that he was considering moving away from developing FileMaker solutions in favor of building apps especially iPhone apps that could be sold on the App Store. I was also interested in building iPhone apps as I've tried many methods to avoid coding in C like FMTouch, PhoneGap, LiveCode, and others that I can't recall. It was different this time though. Xojo was working to build iPhone apps with their superb object oriented 'BASIC like' language. I was already developing desktop apps for Mac and Windows so being able to build iPhone apps with the same language was a no brainier!

Xojo iOS App on the App Store

Once Xojo announced that we could build iOS Apps, we were all over it. Reference for FileMaker one of the first Xojo iPhone apps on the App Store and has been updated to include the latest changes in FileMaker 14. While Reference for FileMaker includes an internal SQLite database, Tim has been working on iOS apps that utilize FM EasyAPI so his iOS app can access data from a FileMaker Server.

Xojo Web Apps for FileMaker Deprecated Runtimes

FileMaker has placed Runtimes on their to be deprecated list earlier this year. You can continue to build Runtimes in FileMaker 14. After an outcry from developers, FileMaker said that it'll be in the next version too, but no promises after than and nothing has been suggest what may replace Runtimes. If you haven't heard, we've been using Xojo Web Apps as a replacement for Runtimes. We have much more on our blog where we discuss Xojo Xanadu and EasyData.

Tell Us What You Really, Really Want

This is your call to action! Tim and I have the same short survey below and also on Tim's Blog where we'd like to find out what interest you about Xojo. If there's a significant number of people wanting to learn more, we'll move forward with the idea!




Xojo Xanadu - SQLite Audit Log using Triggers


11/2/2015
Hal Gumbert

Introducing Xojo Xanadu

Xanadu is the name for our Xojo Web App which we'll use to start our Web App projects for folks we work with. It's still in its infancy but it's quickly evolving into a fantastic foundation for database solutions. We're in love with the Xanadu logo that Ntstuan on Fiverr created for us. If you need an icon created, try out his services!

If you're a database user, you'll want to read this to learn more about what we can do for you. If you're a developer, you'll be interested in the Audit Log and the open source code!

Sometime in the near future, we'll add a web page specifically for Xanadu like we have for our FileMaker Template: FMSBFoundation. We wish we could make Xanadu Open Source. It's really amazing so far, but there are just too many bits of open source and purchased code from other developers. The great news is that everything we use in Xanadu can be included with the apps we develop. That means that if you need an app built, we can build it and you won't need to pay for all the add ons!

Xojo SQLite Audit Log

That said, we can make some things available as open source. We're incredibly appreciative of the Xojo community and love to give back when we can. So, we're making our Audit Log code that uses SQLite Triggers away!

The idea is simple. If you have a SQLite database all you need to do is add one table and one method to add SQLite Triggers to automatically create a log entry for each INSERT, UPDATE, and DELETE for the tables you care about. Check out the video below and the source code below. You'll see that it's really easy to implement.

Once you have it up and running, keep an eye on how much storage the LogAudit takes up. From the start, we wanted to store the Audit Log records in a separate but attached SQLite database file, but that didn't work as SQLite records that are inserted from triggers must be in the same file as the trigger. So, we plan to create a 'dbAuditLogTransporter' method to move the Log records to an attached SQLite database file. We'll make that available sometime, but we need to write it first!

The 'dbAuditLogTriggers' method is close to magical! The method will create three triggers for every table in your database file except for the tables you don't want to log! Our 'People_Insert_TG' looks like this: