import data from Google fusion table into Google spreadsheet

Question

I'm making a decent IT-inventory system using Google Drive. I wrote an Android app (using app inventor) that allows me to scan the barcode of a pc and add some comments to it. That data gets stored in a Google Fusion Table.

(Date | PC-code | Task)

Now I want to import the fusion table into a Google Spreadsheet. That way, I can create a list and see what computers I have seen/installed software on and what computers I still have to see.

I have tried using

 =importxml()
 =importurl()
 =importrange()

But as I'm not at all good in mysql-queries and as I can't seem to find a decent solution on the net, I figured asking here...

Thanks in advance!

PS:

Here is the sample fusion table I use in my android app.

Here is a sample (empty) spreadsheet.

An answer to this problem would be very helpfull and save me a lot of "typing" time.

Answer 1

You will need to enable the Advanced Google Service for Fusion Tables:

Google Documentation - Advanced Google Services

In the Apps Script code editor, choose RESOURCES, ADVANCED GOOGLE SERVICES.

You will get a pop up window that looks like this:

Turn Fusion Tables ON, and then click the link at the bottom of the window to go to your Developers Console

Scroll to Fusion Tables, and click OFF to turn it ON. The item will be removed from the list and go to the top of the page:

Take a look at the sample code here:

Fusion Tables API

I copied the sample code, and changed it a bit. I tested it, and it puts all my Fusion table data into the current spreadsheet and the active sheet.

function runQuery(tableId) {
  tableId = "yourTableID";

  var sql = 'SELECT * FROM ' + tableId + ' LIMIT 100';
  var result = FusionTables.Query.sqlGet(sql, {
    hdrs: false
  });
  if (result.rows) {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = spreadsheet.getActiveSheet();

    // Append the headers.
    sheet.appendRow(result.columns);

    // Append the results.
    sheet.getRange(2, 1, result.rows.length, result.columns.length)
        .setValues(result.rows);

    Logger.log('Query results spreadsheet created: %s',
        spreadsheet.getUrl());
  } else {
    Logger.log('No rows returned.');
  }
}

Open up the fusion table that you want to get data from, and get the file ID out of the URL:

google.com/fusiontables/DataSource?docid=1LDLJFU398UFOjB4-BS89IOHV89TYHTOHC&pli=1#rows:id=1