Google Fusion Table to Google Spreadsheet - Update Spreadsheet by selected criteria

Question

I am a beginner in coding and I am running a script to update a Google Spreadsheet with data from a Google Fusion Table. The data of the Fusion Table contains round about 100 different columns.

I want to evaluate only selected columns, for example: Date, FirstName, LastName, Department. The searching criteria looks like:

Update on the Spreadsheet the following columns "Date, FirstName, LastName, Department where Department contains HR".

I am using the following script:

function runQuery() {
var sql = 'SELECT Date, FirstName, LastName, Department WHERE Department contains HR' + 'TableID';
var result = FusionTables.Query.sqlGet(sql, {
hdrs: false
});
if (result.rows) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();

     sheet.getRange(2, 1, result.rows.length, result.columns.length)
    .setValues(result.rows);
  }
}

It is no problem to select Columns and transfer the data from the Fusion Table to the Spreadsheet. The following error occurs, when I want to show only data with the above mentioned selection critera ("where Department contains HR"):

-Invalid query: Parse error near 'WHERE' (line 1, position 33). (Zeile 3, Datei "runQuery")-

It would be great, if someone can help me. Thank you in advanced.

Jens

UPDATE "Jens"!

This function works fine. Ther I receive selected data by the following criterias.

var tableName = '1sJjfOJ4Q.....'

function runQuery() {

var sql = 'SELECT Date, FirstName, LastName, Department, EMail from ' + 'tableName';

var result = FusionTables.Query.sqlGet(sql, {
hdrs: false
});
if (result.rows) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();


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

when I add the "where clause" an errors occures:

var tableName = '1sJjfOJ4Q.....'    

function runQuery() {
var sql = 'SELECT Date, FirstName, LastName, Department, EMail from ' + 'tableName' + 'WHERE Department contains HR';

var result = FusionTables.Query.sqlGet(sql, {
hdrs: false
});
if (result.rows) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();


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

The error look like this: Invalid query: Parse error near 'Department' (line 1, position 101). Invalid table id 1sJjfOJ4...-xLfYohPizswhere (line 7, file "Code")"

It seems, the "var result " in line 7 is not correct and I don`t know why.

Update "Rod"!

var tableName = '1sJjfOJ4Q.....'    

function runQuery() {
var sql = 'SELECT Date, FirstName, LastName, Department, EMail from ' + 'tableName' + "WHERE Department contains 'HR'";

var result = FusionTables.Query.sqlGet(sql, {
hdrs: false
});
if (result.rows) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();


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

The same error occures (see Update ME)--> It seems, the "var result " in line 7 is not correct and I don`t know why.

Update "Henrique"!

function runQuery() {
var sql = 'SELECT Date, FirstName, LastName, Department, EMail from 1sJjfOJ4Q..... WHERE Department contains HR';

var result = FusionTables.Query.sqlGet(sql, {
hdrs: false
});
if (result.rows) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();


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

The following error occures: Invalid query: Parse error near 'HR' (line 1, position 120). (line 7, file "Code") It seems, the "var result " in line 7 is not correct and I don`t know why.

Henrique G. Abreu Henrique G. Abreu · Accepted Answer · 2014-09-30T14:12:43

Your select needs a FROM before the WHERE. There should be a space between the tableID and the WHERE clause. Lastly, the string "HR" should be wrapped in quotes. Try this:

sql = "SELECT Date, FirstName, LastName, Department FROM "+tableID+" WHERE Department contains 'HR'";

Assuming you have a tableID variable set with the correct value before you call this function.

Google Fusion Table to Google Spreadsheet - Update Spreadsheet by selected criteria

Jens

1 Answers