Skip to content

Instantly share code, notes, and snippets.

@aknik

aknik/README.md

Forked from chrislkeller/README.md
Created April 21, 2018 15:47
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save aknik/131e4252fc865789b082a812e3f67191 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save aknik/131e4252fc865789b082a812e3f67191 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @chrislkeller chrislkeller revised this gist Jan 22, 2016. 1 changed file with 0 additions and 1 deletion.
    1 change: 0 additions & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -39,7 +39,6 @@ Script to sync a Google SpreadSheet to a Fusion Table

    * Click Done

    ----

    ### Add the script to the spreadsheet

  2. @chrislkeller chrislkeller revised this gist Jan 22, 2016. 1 changed file with 23 additions and 3 deletions.
    26 changes: 23 additions & 3 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -23,6 +23,22 @@ Script to sync a Google SpreadSheet to a Fusion Table

    * First, head to the [Developer's Console](https://console.developers.google.com/) and log in with your Google account if prompted.

    * Click create a project and give it a name.

    * Choose the Enable and manage APIs under the Use Google APIs tile

    * Find Fusion Tables API and click the "Enable API button"

    * Click Go to Credentials. You will be "Calling Fusion Tables API from a web browser"

    * Create the OAuth 2.0 client ID

    * For Product name shown to users I put "Sync Spreadsheet To Fusion Tables"

    ![Credentials](http://archives.chrislkeller.com/blog-images/2012/01/credentials.png)

    * Click Done

    ----

    ### Add the script to the spreadsheet
    @@ -42,15 +58,19 @@ Script to sync a Google SpreadSheet to a Fusion Table

    * Click save. You will be prompted to give the project a name. "Update Fusion Tables" works. Click the save icon or go to File --> Save.

    * Click Resources --> Developer's Console Project. Enter the Project Number for the project you just created.

    ![](http://archives.chrislkeller.com/blog-images/2012/01/developer_console_project.png)

    * Reload the spreadsheet and you will see a new menu item next to help. Mine reads "Sync Spreadsheet To Fusion Table." Click the menu item and you will see an option to "Update Fusion Table."

    ![](http://archives.chrislkeller.com/blog-images/2012/01/37380960-2012-01-31_11h30_49.png)

    * Now just add some information to your spreadsheet and click "Update Fusion Table." Your spreadsheet data should be synced with your Fusion Table.
    * Now just add some new information to your spreadsheet and click "Update Fusion Table." The application will ask you to authenticate. Once you click OK, your spreadsheet data should be synced with your Fusion Table.

    ![](http://archives.chrislkeller.com/blog-images/2012/01/37380965-2012-01-31_11h40_29.png)

    * Sit back and enjoy this moment …
    * Sit back and enjoy this moment …

    This script can now be combined with [other script functions](http://blog.chrislkeller.com/automating-crowdsourced-maps-with-apps-scripts/) to make the integration between Google spreadsheets and Fusion Tables more powerful. Or perhaps you want to add a trigger to sync data between the spreadsheet and the table. Such a trigger can be added to run [minute by minute, or hourly](http://www.chrislkeller.com/so-you-want-a-timely-sync-between-your-google) and those changes will be reflected on the table.

    @@ -72,4 +92,4 @@ Which might just mean that your API key isn't active yet… Or you might receive

    **The following is likely caused by either not authenticating or a corrupt authentication token**

    Request failed for https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication Url=https://www.google.com/accounts/ContinueSignIn?sarp=1&scc=1&plt=AKgnsbutVGIXKvt9Nhj84zteA6Qk3RgCkgkQzOPygJE4aWgRrWXJ8_N... (use muteHttpExceptions option to examine full response
    Request failed for https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication Url=https://www.google.com/accounts/ContinueSignIn?sarp=1&scc=1&plt=AKgnsbutVGIXKvt9Nhj84zteA6Qk3RgCkgkQzOPygJE4aWgRrWXJ8_N... (use muteHttpExceptions option to examine full response
  3. @chrislkeller chrislkeller revised this gist Jan 22, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -9,7 +9,7 @@ Script to sync a Google SpreadSheet to a Fusion Table

    ### Create your spreadsheet and import it into Fusion Tables

    * Head to Google docs, create a spreadsheet and add some data to it. Simple enough right? Just make sure that Column A has data in it. It seems blank or null values will break the update function. Though if other columns are without data the script appears to work fine.
    * Head to Google docs, create a spreadsheet and add some data to it. Simple enough right?

    ![Google spreadsheet](http://archives.chrislkeller.com/blog-images/2012/01/37380956-2012-01-31_11h24_34.png)

  4. @chrislkeller chrislkeller revised this gist Jan 22, 2016. 1 changed file with 29 additions and 81 deletions.
    110 changes: 29 additions & 81 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,127 +1,75 @@
    ### Script to sync a Google SpreadSheet to a Fusion Table
    Script to sync a Google SpreadSheet to a Fusion Table
    =====================================================

    > I will admit that save for a few legacy projects that still use Fusion Tables I don't actively use this script anymore -- hence no real development or maintaining of it.
    > Save for a few legacy projects that still use Fusion Tables I don't actively use this script anymore. This update hopefully solves the OAuth issue that cropped up once Google depcricated the Client Login method used by the prior version.
    >
    >Something changed on the authentication end a while back it seems, and I can't vouch for whether this script continues to work or not. I know that once I enabled two-factor authentication for my Google account this script stopped working.
    >
    >I've added a couple potential solutions [here](https://gist.github.com/chrislkeller/3013360/#comment-1177733), but would welcome someone -- Google or otherwise -- to offer a long-term maintained solution.
    >As always, your mileage may vary, and I welcome someone -- [Google](https://htmlpreview.github.io/?https://github.com/fusiontable-gallery/fusion-tables-api-samples/blob/master/FusionTablesSheetSync/docs/reference.html#enabling_advanced_services) or otherwise -- to offer a long-term maintained solution.
    *The following is largely cribbed from a Google example [here](https://htmlpreview.github.io/?https://github.com/fusiontable-gallery/fusion-tables-api-samples/blob/master/FusionTablesSheetSync/docs/reference.html#enabling_advanced_services). I try to explain some of the API settings that must be enabled in the Developer's Console and elsewhere*

    **Thanks** goes out to [John McGrath](https://github.com/jomcgrath2/Update-Fusion) who did the bulk of the work in making this script happen.
    ### Create your spreadsheet and import it into Fusion Tables

    * Head to Google docs, create a spreadsheet and add some data to it. Simple enough right? Just make sure that Column A has data in it. It seems blank or null values will break the update function. Though if other columns are without data the script appears to work fine. At some point, I'll learn to add an error message if Column A is blank, but for now, Column A wants data.
    * Head to Google docs, create a spreadsheet and add some data to it. Simple enough right? Just make sure that Column A has data in it. It seems blank or null values will break the update function. Though if other columns are without data the script appears to work fine.

    ![Google spreadsheet](http://archives.chrislkeller.com/blog-images/2012/01/37380956-2012-01-31_11h24_34.png)

    * Now I'm going to create a Fusion Table based off my spreadsheet. I do this either by importing from Google Docs or downloading the spreadsheet as a csv and uploading it to Fusion Tables. **It is important to note** from the outset that the column names must match between the spreadsheet and the table. Remember, if you change a column name or add a column to the spreadsheet, be sure to change it/add it to the Fusion Table as well. For good measure I make sure any new columns are also in the same order.
    * Now I'm going to create a [Fusion Table](https://www.google.com/fusiontables) based off my spreadsheet. I do this either by importing from Google Docs or downloading the spreadsheet as a csv and uploading it to Fusion Tables. **It is important to note** it's best practice for the column names of the spreadsheet and the table to match. It's not a deal breaker, but why not be consistent? For good measure I make sure any new columns are also in the same order.

    ![Fusion Table Import](http://archives.chrislkeller.com/blog-images/2012/01/37380957-2012-01-31_11h25_24.png)

    * After my Fusion Table is created, I need to get the Encrypted Table ID in order to make sure the spreadsheet can access it. To find the Encrypted Table ID I click File --> About this table. I'll copy this somewhere in a text file until I need it.

    * I'm almost ready to make this happen, but I'm going to need one other piece of information - a Google Fusion Tables API key. I need to turn on access to the Fusion Tables API in Google's API console, and get an authentication key. Don't worry, this is much easier than it sounds.

    * First, head to the [API Console dashboard](https://code.google.com/apis/console/) and log in with your Google account if prompted. The first screen you see if an overview. If it's your first time here, you might not see a lot. On the left-side of the screen you'll see a dropdown where you can create a new project. Go ahead and create one and give it a name.
    * I'm almost ready to make this happen, but I'm going to need to enable the Google Fusion Tables API in two places.

    ![Fusion Table Import](http://archives.chrislkeller.com/blog-images/2013/apis-console.png)
    * First, head to the [Developer's Console](https://console.developers.google.com/) and log in with your Google account if prompted.

    * Next click on Services on the left-side of the screen and you will see a heck of a lot of toggles that can turn on various Google APIs. Scroll down a bit until you find Fusion Tables API, and flip to toggle to the 'On' position.
    ----

    ![Fusion Table Import](http://archives.chrislkeller.com/blog-images/2013/apis-services.png)
    ### Add the script to the spreadsheet

    * Finally, click on API Access on the left-side of the screen. You will see two main areas: Authorized API Access and Simple API Access. We're interested in the API Key shown under Simple API Access. I'll copy this somewhere in a text file next to my Encrypted Table ID.
    * Now we're ready to add our script to our spreadsheet. Back at your spreadsheet, go to Tools --> Script Editor and paste the [script code](https://gist.github.com/chrislkeller/3013360#file-spreadsheet_to_fusion_tables-js). On [Line 9](https://gist.github.com/chrislkeller/3013360#file-spreadsheet_to_fusion_tables-js-L9) I add my Fusion Table's Table ID...

    ![Fusion Table Import](http://archives.chrislkeller.com/blog-images/2013/api-console-key.png)
    // Add the encrypted table ID of the fusion table here
    var TABLE_ID = '17xnxY......';

    * Now we're ready to add our script to our spreadsheet. Back at your spreadsheet, go to Tools --> Script Editor and paste the [script code](https://gist.github.com/3013360). I add my Fusion Table's encrypted table ID to the top of the script...
    * **Optional**: if you have multiple header rows, put the row number of the first data row on this [line](https://gist.github.com/chrislkeller/3013360#file-spreadsheet_to_fusion_tables-js-L12):

    // Add the encrypted table ID of the fusion table here
    var tableIDFusion = '17xnxY......';
    var FIRST_DATA_ROW = 2;

    * Then I add my API key.
    * **Optional**: if you want to allow the spreadsheet to have different columns than the table, change the [line 15](https://gist.github.com/chrislkeller/3013360#file-spreadsheet_to_fusion_tables-js-L15) value to "false":

    // key needed for fusion tables api
    var fusionTablesAPIKey = '17xnxY......';
    var REQUIRE_SAME_COLUMNS = true;

    * Click save. You will be prompted to give the project a name. "Update Fusion Tables" works. Click the save icon or go to File --> Save.

    * Reload the spreadsheet and you will see a new menu item next to help. Mine says "Data Update Functions." Click the menu item and you will see three options, though the names may differ at this point: "Change Range of Data to be Sent (Include Headers)", "Update Fusion Table" & "Change Email Information."
    * Reload the spreadsheet and you will see a new menu item next to help. Mine reads "Sync Spreadsheet To Fusion Table." Click the menu item and you will see an option to "Update Fusion Table."

    ![](http://archives.chrislkeller.com/blog-images/2012/01/37380960-2012-01-31_11h30_49.png)

    9. First choose "Change Email Information." This will authenticate your gmail account to access the Fusion Table. **Note**: I HAVE NOT had success using this with a Google Apps account going to a private Gmail account. Click the couple of confirmation buttons that appear.

    10. Second you should select all of the data -- columns and rows -- you wish to sync and choose Change Range of Data to be Sent (Include Headers)". I usually just click the rectangle in the upper-left corner to sync everything. The beauty -- in my experience -- is that blank rows and columns can be synced but they won't be reflected on the Fusion Table. Click the two confirmation buttons that appear.

    ![](http://archives.chrislkeller.com/blog-images/2012/01/37380963-2012-01-31_11h35_44.png)

    11. Now just add some information to your spreadsheet and click "Update Fusion Table." Your spreadsheet data should be synced with your Fusion Table.
    * Now just add some information to your spreadsheet and click "Update Fusion Table." Your spreadsheet data should be synced with your Fusion Table.

    ![](http://archives.chrislkeller.com/blog-images/2012/01/37380965-2012-01-31_11h40_29.png)

    12. Sit back and enjoy this moment …

    <del>I've also put together this working demo that I can grant you access to in case it helps you spot where a difference might lie. You can add your API key and Table ID to test things out, and then make a copy and be off and running. Just be sure you delete your API and Table IDs...</del>

    This script can now be combined with [other script functions](http://blog.chrislkeller.com/automating-crowdsourced-maps-with-apps-scripts/) to make the integration between Google spreadsheets and Fusion Tables more powerful. Or perhaps you want to add a trigger to sync data between the spreadsheet and the table. Such a trigger can be added to run [minute by minute, or hourly](http://www.chrislkeller.com/so-you-want-a-timely-sync-between-your-google) and those changes will be reflected on the table.

    Of course, unless the script didn't work. Perhaps you received an error message like:
    * Sit back and enjoy this moment …

    Request failed for https://www.googleapis.com/fusiontables/v1/query?key=ajhdndna8282n29& returned code 403. Server response: { "error": { "errors": [ { "domain": "usageLimits", "reason": "accessNotConfigured", "message": "Access Not Configured" } ], "code": 403, "message": "Access Not Configured" } }
    This script can now be combined with [other script functions](http://blog.chrislkeller.com/automating-crowdsourced-maps-with-apps-scripts/) to make the integration between Google spreadsheets and Fusion Tables more powerful. Or perhaps you want to add a trigger to sync data between the spreadsheet and the table. Such a trigger can be added to run [minute by minute, or hourly](http://www.chrislkeller.com/so-you-want-a-timely-sync-between-your-google) and those changes will be reflected on the table.

    13. So let's try to decipher some error messages.
    Of course, unless the script didn't work... Mention me in the comments so I'm notified and we'll see what we can do...

    If you get stuck and things just aren't working, let me know and I'll see what I can do. There are a lot of moving parts and little details to pay attention to, and I'm sure I've overlooked something in this walkthrough.
    ----

    **Trying to log error messages and the possible cause. These are not confirmed. Just best guesses**

    **...**

    Request failed for https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyCBbVMnJwhD5xPYJpcvOT8vOUlLs9jYv5U& returned code 400. Truncated server response: { "error": { "errors": [ { "domain": "fusiontables", "reason": "badQueryCouldNotParse", "message": "Invalid query: Parse error ne... (use muteHttpExceptions option to examine full response)
    Request failed for https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyCBbVMnJwhD5xPYJpcvOT8vOUlLs9jYv5U& returned code 400. Truncated server response: { "error": { "errors": [ { "domain": "fusiontables", "reason": "badQueryCouldNotParse", "message": "Invalid query: Parse error ne... (use muteHttpExceptions option to examine full response)

    **The following is likely caused by your API key not being recognized.**

    Request failed for https://www.googleapis.com/fusiontables/v1/query?key=MY API CODE& returned code 401. Truncated server response: { "error": { "errors": [ { "domain": "global", "reason": "authError", "message": "Invalid Credentials", "locationType": "head... (use muteHttpExceptions option to examine full response) (line 79, file "Code")
    Request failed for https://www.googleapis.com/fusiontables/v1/query?key=MY API CODE& returned code 401. Truncated server response: { "error": { "errors": [ { "domain": "global", "reason": "authError", "message": "Invalid Credentials", "locationType": "head... (use muteHttpExceptions option to examine full response) (line 79, file "Code")

    Which might just mean that your API key isn't active yet… Or you might receive an error because you didn't authenticate with the application when prompted. Be sure you have activated the Fusion Tables API on the [Google API console](https://code.google.com/apis/console/) and added the key to the script in the proper place.

    **The following is likely caused by either not authenticating or a corrupt authentication token**

    Request failed for https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication Url=https://www.google.com/accounts/ContinueSignIn?sarp=1&scc=1&plt=AKgnsbutVGIXKvt9Nhj84zteA6Qk3RgCkgkQzOPygJE4aWgRrWXJ8_N... (use muteHttpExceptions option to examine full response
    To sync things, Google needs to know you have permission to access the spreadsheet and Fusion Table. This is usually done by signing in the first time you run Update Fusion. It seems that this may have changed since this script was first created. A quick test showed that if I cleared out my email and password each time I ran the updateFusion function I was able to sync the spreadsheet. While not ideal, it seems to work.

    To do this I added ```UserProperties.deleteAllProperties();``` to the top of the updateFusion function, which I think starts around line 26.

    The full function would looks like this:

    // main function
    function updateFusion() {
    UserProperties.deleteAllProperties();
    // gets the user property 'email' out of project properties
    var email = UserProperties.getProperty('email');
    // gets the user property 'password' out of project properties
    var password = UserProperties.getProperty('password');
    // if either email or password is not saved in project properties this will store them there
    if (email === null || password === null) {
    // browser box to input email
    email = Browser.inputBox('Enter email');
    password = Browser.inputBox('Enter password');
    UserProperties.setProperty('email', email);
    UserProperties.setProperty('password', password);
    } else {
    email = UserProperties.getProperty('email');
    password = UserProperties.getProperty('password');
    }
    var authToken = getGAauthenticationToken(email, password);
    deleteData(authToken, tableIDFusion);
    var updateMsg = updateData(authToken, tableIDFusion);
    var updatedRowsCount = updateMsg.split(/\n/).length - 2;
    SpreadsheetApp.getActiveSpreadsheet().toast("Updated " + updatedRowsCount + " rows in the Fusion Table", "Fusion Tables Update", 5)
    };
    Request failed for https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication Url=https://www.google.com/accounts/ContinueSignIn?sarp=1&scc=1&plt=AKgnsbutVGIXKvt9Nhj84zteA6Qk3RgCkgkQzOPygJE4aWgRrWXJ8_N... (use muteHttpExceptions option to examine full response
  5. @chrislkeller chrislkeller revised this gist Jan 22, 2016. 1 changed file with 63 additions and 143 deletions.
    206 changes: 63 additions & 143 deletions spreadsheet_to_fusion_tables.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,155 +1,75 @@
    // Add the table ID of the fusion table here
    var tableIDFusion = '17xnxY......';
    /**
    * appsscript script to run in a google spreadsheet that synchronizes its
    * contents with a fusion table by replacing all rows.
    * based on instructions here:
    * https://htmlpreview.github.io/?https://github.com/fusiontable-gallery/fusion-tables-api-samples/blob/master/FusionTablesSheetSync/docs/reference.html#enabling_advanced_services
    */

    // key needed for fusion tables api
    var fusionTablesAPIKey = 'A2crr4Y......';
    // replace with your fusion table's id (from File > About this table)
    var TABLE_ID = '17xnxY......';

    // the name of the range used in the program
    var rangeName = 'updateFusion';
    // first row that has data, as opposed to header information
    var FIRST_DATA_ROW = 2;

    // create menu buttons
    function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [{
    name: "Change Range of Data to be Sent (Include Headers)",
    functionName: "setRangeFusion"
    }, {
    name: "Update Fusion Table",
    functionName: "updateFusion"
    }, {
    name: "Change Email Information",
    functionName: "fixEmail"
    }];
    ss.addMenu("Data Update Functions", menuEntries);
    };
    // true means the spreadsheet and table must have the same column count
    var REQUIRE_SAME_COLUMNS = true;

    // main function
    function updateFusion() {

    UserProperties.deleteAllProperties();

    // gets the user property 'email' out of project properties
    var email = UserProperties.getProperty('email');

    // gets the user property 'password' out of project properties
    var password = UserProperties.getProperty('password');

    // if either email or password is not saved in project properties this will store them there

    if (email === null || password === null) {

    // browser box to input email
    email = Browser.inputBox('Enter email');
    password = Browser.inputBox('Enter password');
    UserProperties.setProperty('email', email);
    UserProperties.setProperty('password', password);
    /**
    * replaces all rows in the fusion table identified by TABLE_ID with the
    * current sheet's data, starting at FIRST_DATA_ROW.
    */
    function sync() {
    var tasks = FusionTables.Task.list(TABLE_ID);
    // Only run if there are no outstanding deletions or schema changes.
    if (tasks.totalItems === 0) {
    var sheet = SpreadsheetApp.getActiveSheet();
    var wholeSheet = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
    var values = wholeSheet.getValues();
    if (values.length > 1) {
    var csvBlob = Utilities.newBlob(convertToCsv_(values), 'application/octet-stream');
    FusionTables.Table.replaceRows(TABLE_ID, csvBlob, { isStrict: REQUIRE_SAME_COLUMNS, startLine: FIRST_DATA_ROW - 1 });
    Browser.msgBox('Replaced ' + values.length + ' rows in your Fusion Table', Browser.Buttons.OK);
    }
    } else {
    email = UserProperties.getProperty('email');
    password = UserProperties.getProperty('password');
    Logger.log('Skipping row replacement because of ' + tasks.totalItems + ' active background task(s)');
    }

    var authToken = getGAauthenticationToken(email, password);
    deleteData(authToken, tableIDFusion);
    var updateMsg = updateData(authToken, tableIDFusion);
    var updatedRowsCount = updateMsg.split(/\n/).length - 2;
    SpreadsheetApp.getActiveSpreadsheet().toast("Updated " + updatedRowsCount + " rows in the Fusion Table", "Fusion Tables Update", 5)
    };

    // Google Authentication API this is taken directly from the google fusion api website
    function getGAauthenticationToken(email, password) {
    password = encodeURIComponent(password);
    var response = UrlFetchApp.fetch("https://www.google.com/accounts/ClientLogin", {
    method: "post",
    payload: "accountType=GOOGLE&Email=" + email + "&Passwd=" + password + "&service=fusiontables&Source=testing"
    });

    var responseStr = response.getContentText();
    responseStr = responseStr.slice(responseStr.search("Auth=") + 5, responseStr.length);
    responseStr = responseStr.replace(/\n/g, "");
    return responseStr;
    };

    // query fusion API post
    function queryFusionTables(authToken, query) {

    // location to send the infomation to
    var prefix = "https://www.googleapis.com/fusiontables/v1/query?key=";
    var suffix = fusionTablesAPIKey + '&';
    var URL = prefix + suffix;

    // sends the the authentication and the query in url format
    var response = UrlFetchApp.fetch(URL, {
    method: "post",
    headers: {
    "Authorization": "GoogleLogin auth=" + authToken,
    },
    payload: "sql=" + query
    });

    return response.getContentText();
    };

    // delete old data in fusion table
    function deleteData(authToken, tableID) {
    var query = encodeURIComponent("DELETE FROM " + tableID);
    return queryFusionTables(authToken, query);
    };

    // puts all the current information in the spreadsheet into a query
    function updateData(authToken, tableID) {

    //find sheets with ranges that will be sent
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var range = ss.getRangeByName(rangeName);
    var data = range.getValues();

    // format data
    for (var i in data) {
    for (var j in data[i]) {
    if (isNaN(data[i][j])) {
    data[i][j] = data[i][j].replace(/'/g, "\\'");
    /**
    * converts the spreadsheet values to a csv string.
    * @param {array} data the spreadsheet values.
    * @return {string} the csv string.
    */
    function convertToCsv_(data) {
    // See https://developers.google.com/apps-script/articles/docslist_tutorial#section3
    var csv = '';
    for (var row = 0; row < data.length; row++) {
    for (var col = 0; col < data[row].length; col++) {
    var value = data[row][col].toString();
    if (value.indexOf(',') != -1 ||
    value.indexOf('\n') != -1 ||
    value.indexOf('"') != -1) {
    // Double-quote values with commas, double quotes, or newlines
    value = '"' + value.replace(/"/g, '""') + '"';
    data[row][col] = value;
    }
    }
    }

    var headers = data[0];
    //var queryPrepend = "INSERT INTO " + tableID + " (" + "\'" + headers.join("\',\'") + "\'" + ") VALUES ('";
    var queryPrepend = "INSERT INTO " + tableID + " (" + headers.join(",") + ") VALUES ('";
    var query = "";

    for (var i = 1; i < data.length; ++i) {
    if (typeof (data[i][0]) == "string" && data[i][0] == "") {
    continue;
    }

    query += queryPrepend + data[i].join("','") + "'); ";

    }
    return queryFusionTables(authToken, encodeURIComponent(query));
    };
    // Join each row's columns and add a carriage return to end of each row except the last
    if (row < data.length - 1) {
    csv += data[row].join(',') + '\r\n';
    } else {
    csv += data[row];
    };
    };
    return csv;
    };

    // change email if needed
    function fixEmail() {
    var decision = Browser.msgBox("WARNING", "Are you sure you want to change your email?", Browser.Buttons.YES_NO);
    if (decision == 'yes') {
    var email = Browser.inputBox('Enter email');
    var password = Browser.inputBox('Enter password');
    UserProperties.setProperty('email', email);
    UserProperties.setProperty('password', password);
    }
    };

    // set range
    function setRangeFusion() {
    var decision = Browser.msgBox("WARNING", "Are you sure you want to change the Update Fusion Range?", Browser.Buttons.YES_NO);
    if (decision == 'yes') {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var check = ss.getRangeByName(rangeName)
    if (check != null) {
    ss.removeNamedRange(rangeName);
    }
    var range = SpreadsheetApp.getActiveRange()
    ss.setNamedRange(rangeName, range);
    Browser.msgBox("WARNING", "The range \'" + rangeName + "\' used to send data to Fusion has been changed.", Browser.Buttons.OK);
    }
    // create menu buttons
    function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [{
    name: "Update Fusion Table",
    functionName: "sync"
    }];
    ss.addMenu("Sync Spreadsheet To Fusion Table", menuEntries);
    };
  6. @chrislkeller chrislkeller revised this gist Feb 22, 2014. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -4,7 +4,7 @@
    >
    >Something changed on the authentication end a while back it seems, and I can't vouch for whether this script continues to work or not. I know that once I enabled two-factor authentication for my Google account this script stopped working.
    >
    >I've added a couple potential solutions [here](https://gist.github.com/chrislkeller/3013360/#comment-1177733), but would welcome someone -- Google or otherwise.
    >I've added a couple potential solutions [here](https://gist.github.com/chrislkeller/3013360/#comment-1177733), but would welcome someone -- Google or otherwise -- to offer a long-term maintained solution.
    **Thanks** goes out to [John McGrath](https://github.com/jomcgrath2/Update-Fusion) who did the bulk of the work in making this script happen.

  7. @chrislkeller chrislkeller revised this gist Feb 22, 2014. 1 changed file with 6 additions and 0 deletions.
    6 changes: 6 additions & 0 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,11 @@
    ### Script to sync a Google SpreadSheet to a Fusion Table

    > I will admit that save for a few legacy projects that still use Fusion Tables I don't actively use this script anymore -- hence no real development or maintaining of it.
    >
    >Something changed on the authentication end a while back it seems, and I can't vouch for whether this script continues to work or not. I know that once I enabled two-factor authentication for my Google account this script stopped working.
    >
    >I've added a couple potential solutions [here](https://gist.github.com/chrislkeller/3013360/#comment-1177733), but would welcome someone -- Google or otherwise.
    **Thanks** goes out to [John McGrath](https://github.com/jomcgrath2/Update-Fusion) who did the bulk of the work in making this script happen.

    * Head to Google docs, create a spreadsheet and add some data to it. Simple enough right? Just make sure that Column A has data in it. It seems blank or null values will break the update function. Though if other columns are without data the script appears to work fine. At some point, I'll learn to add an error message if Column A is blank, but for now, Column A wants data.
  8. @chrislkeller chrislkeller revised this gist Feb 22, 2014. No changes.
  9. @chrislkeller chrislkeller revised this gist Feb 22, 2014. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -54,7 +54,7 @@

    12. Sit back and enjoy this moment …

    I've also put together [this working demo](https://drive.google.com/folderview?id=0B38W63YKWOsxOXgtOUhHZFh3NEk&usp=sharing) that I can grant you access to in case it helps you spot where a difference might lie. You can add your API key and Table ID to test things out, and then make a copy and be off and running. Just be sure you delete your API and Table IDs...
    <del>I've also put together this working demo that I can grant you access to in case it helps you spot where a difference might lie. You can add your API key and Table ID to test things out, and then make a copy and be off and running. Just be sure you delete your API and Table IDs...</del>

    This script can now be combined with [other script functions](http://blog.chrislkeller.com/automating-crowdsourced-maps-with-apps-scripts/) to make the integration between Google spreadsheets and Fusion Tables more powerful. Or perhaps you want to add a trigger to sync data between the spreadsheet and the table. Such a trigger can be added to run [minute by minute, or hourly](http://www.chrislkeller.com/so-you-want-a-timely-sync-between-your-google) and those changes will be reflected on the table.

  10. @chrislkeller chrislkeller revised this gist Nov 5, 2013. 1 changed file with 0 additions and 32 deletions.
    32 changes: 0 additions & 32 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -2,38 +2,6 @@

    **Thanks** goes out to [John McGrath](https://github.com/jomcgrath2/Update-Fusion) who did the bulk of the work in making this script happen.

    ---
    layout: post
    title: Google Group user John M. posts script that brings Google spreadsheets and
    Fusion Tables closer together
    published: true
    date: 2012-01-31
    categories:
    - data
    - fusion tables
    - learning-library
    posterous_url: http://chrislkeller.posterous.com/google-group-user-john-m-posts-script-that-br
    posterous_slug: google-group-user-john-m-posts-script-that-br
    ---

    >**tl;dr**: I've edited this post to better clarify how to use [a script](https://gist.github.com/chrislkeller/3013360) based on one written by John McGrath that will allow you to update a Fusion Table from a Google spreadsheet.
    >
    >I've modified John's original script for [my needs](https://gist.github.com/chrislkeller/3013360), added a variable for an [API key](https://developers.google.com/fusiontables/docs/v1/migration_guide#ownClient) and adjusted it to use a [new Fusion Tables API endpoint](https://developers.google.com/fusiontables/docs/v1/migration_guide) as the original version used the SQL API endpoint, which was phased out.
    Fusion Tables -- at least in the circles I pay attention to on the internet -- has become an ubiquitous method that beginners and advances users alike can use to map & visual information.

    Though now part of Google Drive -- and certainly more advanced than it was just a year ago -- Fusion Tables still isn't as [tightly integrated with Google spreadsheets](https://code.google.com/p/fusion-tables/issues/detail?id=126) as many would like to see.

    In my work I have found two workaround methods that will allow me to add information to a Google spreadsheet and then POST it to a Fusion Table:

    * In early 2012, [Kathryn Hurley](https://twitter.com/#!/Kathryn_Hurley) -- then a member of the Google Fusion Tables team -- offered up a [script](http://kh-samples.googlecode.com/svn/trunk/code/instructions.html) for Google Apps that allowed a user to submit data to a Fusion Table from a form, using a Google spreadsheet as a conduit.

    * Then, the aforementioned John posted an Apps script to GitHub that authenticates against a Google account and updates a Fusion Table from a spreadsheet.

    Both of these methods are more efficient than updating spreadsheet, downloading a csv of the spreadsheet, deleting Fusion Tables rows and then importing the csv to the Fusion Table. But the two methods do operate in different ways.

    I prefer John's script, and here's an updated attempt to help you get the script up and running, and troubleshoot potential issues.

    * Head to Google docs, create a spreadsheet and add some data to it. Simple enough right? Just make sure that Column A has data in it. It seems blank or null values will break the update function. Though if other columns are without data the script appears to work fine. At some point, I'll learn to add an error message if Column A is blank, but for now, Column A wants data.

    ![Google spreadsheet](http://archives.chrislkeller.com/blog-images/2012/01/37380956-2012-01-31_11h24_34.png)
  11. @chrislkeller chrislkeller revised this gist Nov 5, 2013. 1 changed file with 39 additions and 1 deletion.
    40 changes: 39 additions & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -2,6 +2,38 @@

    **Thanks** goes out to [John McGrath](https://github.com/jomcgrath2/Update-Fusion) who did the bulk of the work in making this script happen.

    ---
    layout: post
    title: Google Group user John M. posts script that brings Google spreadsheets and
    Fusion Tables closer together
    published: true
    date: 2012-01-31
    categories:
    - data
    - fusion tables
    - learning-library
    posterous_url: http://chrislkeller.posterous.com/google-group-user-john-m-posts-script-that-br
    posterous_slug: google-group-user-john-m-posts-script-that-br
    ---

    >**tl;dr**: I've edited this post to better clarify how to use [a script](https://gist.github.com/chrislkeller/3013360) based on one written by John McGrath that will allow you to update a Fusion Table from a Google spreadsheet.
    >
    >I've modified John's original script for [my needs](https://gist.github.com/chrislkeller/3013360), added a variable for an [API key](https://developers.google.com/fusiontables/docs/v1/migration_guide#ownClient) and adjusted it to use a [new Fusion Tables API endpoint](https://developers.google.com/fusiontables/docs/v1/migration_guide) as the original version used the SQL API endpoint, which was phased out.
    Fusion Tables -- at least in the circles I pay attention to on the internet -- has become an ubiquitous method that beginners and advances users alike can use to map & visual information.

    Though now part of Google Drive -- and certainly more advanced than it was just a year ago -- Fusion Tables still isn't as [tightly integrated with Google spreadsheets](https://code.google.com/p/fusion-tables/issues/detail?id=126) as many would like to see.

    In my work I have found two workaround methods that will allow me to add information to a Google spreadsheet and then POST it to a Fusion Table:

    * In early 2012, [Kathryn Hurley](https://twitter.com/#!/Kathryn_Hurley) -- then a member of the Google Fusion Tables team -- offered up a [script](http://kh-samples.googlecode.com/svn/trunk/code/instructions.html) for Google Apps that allowed a user to submit data to a Fusion Table from a form, using a Google spreadsheet as a conduit.

    * Then, the aforementioned John posted an Apps script to GitHub that authenticates against a Google account and updates a Fusion Table from a spreadsheet.

    Both of these methods are more efficient than updating spreadsheet, downloading a csv of the spreadsheet, deleting Fusion Tables rows and then importing the csv to the Fusion Table. But the two methods do operate in different ways.

    I prefer John's script, and here's an updated attempt to help you get the script up and running, and troubleshoot potential issues.

    * Head to Google docs, create a spreadsheet and add some data to it. Simple enough right? Just make sure that Column A has data in it. It seems blank or null values will break the update function. Though if other columns are without data the script appears to work fine. At some point, I'll learn to add an error message if Column A is blank, but for now, Column A wants data.

    ![Google spreadsheet](http://archives.chrislkeller.com/blog-images/2012/01/37380956-2012-01-31_11h24_34.png)
    @@ -52,7 +84,13 @@

    ![](http://archives.chrislkeller.com/blog-images/2012/01/37380965-2012-01-31_11h40_29.png)

    12. Sit back and enjoy this moment … unless it didn't work. Perhaps you received an error message like:
    12. Sit back and enjoy this moment …

    I've also put together [this working demo](https://drive.google.com/folderview?id=0B38W63YKWOsxOXgtOUhHZFh3NEk&usp=sharing) that I can grant you access to in case it helps you spot where a difference might lie. You can add your API key and Table ID to test things out, and then make a copy and be off and running. Just be sure you delete your API and Table IDs...

    This script can now be combined with [other script functions](http://blog.chrislkeller.com/automating-crowdsourced-maps-with-apps-scripts/) to make the integration between Google spreadsheets and Fusion Tables more powerful. Or perhaps you want to add a trigger to sync data between the spreadsheet and the table. Such a trigger can be added to run [minute by minute, or hourly](http://www.chrislkeller.com/so-you-want-a-timely-sync-between-your-google) and those changes will be reflected on the table.

    Of course, unless the script didn't work. Perhaps you received an error message like:

    Request failed for https://www.googleapis.com/fusiontables/v1/query?key=ajhdndna8282n29& returned code 403. Server response: { "error": { "errors": [ { "domain": "usageLimits", "reason": "accessNotConfigured", "message": "Access Not Configured" } ], "code": 403, "message": "Access Not Configured" } }

  12. @chrislkeller chrislkeller revised this gist Nov 5, 2013. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -76,7 +76,7 @@ Which might just mean that your API key isn't active yet… Or you might receive

    Request failed for https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication Url=https://www.google.com/accounts/ContinueSignIn?sarp=1&scc=1&plt=AKgnsbutVGIXKvt9Nhj84zteA6Qk3RgCkgkQzOPygJE4aWgRrWXJ8_N... (use muteHttpExceptions option to examine full response
    To sync things, Google needs to know you have permission to acces the spreadsheet and Fusion Table. This is usually done by signing in the first time you run Update Fusion. It seems that this may have changed since this script was first created. A quick test showed that if I cleared out my email and password each time I ran the updateFusion function I was able to sync the spreadsheet. While not ideal, it seems to work.
    To sync things, Google needs to know you have permission to access the spreadsheet and Fusion Table. This is usually done by signing in the first time you run Update Fusion. It seems that this may have changed since this script was first created. A quick test showed that if I cleared out my email and password each time I ran the updateFusion function I was able to sync the spreadsheet. While not ideal, it seems to work.

    To do this I added ```UserProperties.deleteAllProperties();``` to the top of the updateFusion function, which I think starts around line 26.

  13. @chrislkeller chrislkeller revised this gist Nov 5, 2013. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions spreadsheet_to_fusion_tables.js
    View file
    Original file line number Diff line number Diff line change
    @@ -25,6 +25,8 @@ function onOpen() {

    // main function
    function updateFusion() {

    UserProperties.deleteAllProperties();

    // gets the user property 'email' out of project properties
    var email = UserProperties.getProperty('email');
  14. @chrislkeller chrislkeller revised this gist Nov 5, 2013. 1 changed file with 4 additions and 2 deletions.
    6 changes: 4 additions & 2 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -76,9 +76,11 @@ Which might just mean that your API key isn't active yet… Or you might receive

    Request failed for https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication Url=https://www.google.com/accounts/ContinueSignIn?sarp=1&scc=1&plt=AKgnsbutVGIXKvt9Nhj84zteA6Qk3RgCkgkQzOPygJE4aWgRrWXJ8_N... (use muteHttpExceptions option to examine full response
    To sync things, Google needs to know you have permission to acces the spreadsheet and Fusion Table. This is usually done by signing in the first time you run Update Fusion. I found that by deleting the UserProperties from a spreadsheet I was able to get the sync working again. To do this I added ```UserProperties.deleteAllProperties();``` to the updateFusion function, which I think starts around [line 26](https://gist.github.com/chrislkeller/3013360#file-spreadsheet_to_fusion_tables-js-L26).
    To sync things, Google needs to know you have permission to acces the spreadsheet and Fusion Table. This is usually done by signing in the first time you run Update Fusion. It seems that this may have changed since this script was first created. A quick test showed that if I cleared out my email and password each time I ran the updateFusion function I was able to sync the spreadsheet. While not ideal, it seems to work.

    The full function looks like this:
    To do this I added ```UserProperties.deleteAllProperties();``` to the top of the updateFusion function, which I think starts around line 26.

    The full function would looks like this:

    // main function
    function updateFusion() {
  15. @chrislkeller chrislkeller revised this gist Nov 5, 2013. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -76,7 +76,7 @@ Which might just mean that your API key isn't active yet… Or you might receive

    Request failed for https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication Url=https://www.google.com/accounts/ContinueSignIn?sarp=1&scc=1&plt=AKgnsbutVGIXKvt9Nhj84zteA6Qk3RgCkgkQzOPygJE4aWgRrWXJ8_N... (use muteHttpExceptions option to examine full response
    To sync things, Google needs to know you have permission to acces the spreadsheet and Fusion Table. This is usually done by signing in the first time you run Update Fusion. I found that by deleting the UserProperties from a spreadsheet I was able to get the sync working again. To do this I added ```UserProperties.deleteAllProperties();``` to the updateFusion function, which I think starts around line 26.
    To sync things, Google needs to know you have permission to acces the spreadsheet and Fusion Table. This is usually done by signing in the first time you run Update Fusion. I found that by deleting the UserProperties from a spreadsheet I was able to get the sync working again. To do this I added ```UserProperties.deleteAllProperties();``` to the updateFusion function, which I think starts around [line 26](https://gist.github.com/chrislkeller/3013360#file-spreadsheet_to_fusion_tables-js-L26).

    The full function looks like this:

  16. @chrislkeller chrislkeller revised this gist Nov 5, 2013. 1 changed file with 37 additions and 5 deletions.
    42 changes: 37 additions & 5 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -72,10 +72,42 @@ If you get stuck and things just aren't working, let me know and I'll see what I

    Which might just mean that your API key isn't active yet… Or you might receive an error because you didn't authenticate with the application when prompted. Be sure you have activated the Fusion Tables API on the [Google API console](https://code.google.com/apis/console/) and added the key to the script in the proper place.

    **The following is likely caused by...**
    **The following is likely caused by either not authenticating or a corrupt authentication token**

    Request failed for https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication Url=https://www.google.com/accounts/ContinueSignIn?sarp=1&scc=1&plt=AKgnsbutVGIXKvt9Nhj84zteA6Qk3RgCkgkQzOPygJE4aWgRrWXJ8_N... (use muteHttpExceptions option to examine full response

    **The following is likely caused by...**

    Request failed for https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication Url=https://www.google.com/accounts/ContinueSignIn?sarp=1&scc=1&plt=AKgnsbuxBXbcxmpYifEqDDgyv9RNMHVqos-u7nz2EtXDH4YaDo_EWdY... (use muteHttpExceptions option to examine full response)
    To sync things, Google needs to know you have permission to acces the spreadsheet and Fusion Table. This is usually done by signing in the first time you run Update Fusion. I found that by deleting the UserProperties from a spreadsheet I was able to get the sync working again. To do this I added ```UserProperties.deleteAllProperties();``` to the updateFusion function, which I think starts around line 26.

    The full function looks like this:

    // main function
    function updateFusion() {
    UserProperties.deleteAllProperties();
    // gets the user property 'email' out of project properties
    var email = UserProperties.getProperty('email');
    // gets the user property 'password' out of project properties
    var password = UserProperties.getProperty('password');
    // if either email or password is not saved in project properties this will store them there
    if (email === null || password === null) {
    // browser box to input email
    email = Browser.inputBox('Enter email');
    password = Browser.inputBox('Enter password');
    UserProperties.setProperty('email', email);
    UserProperties.setProperty('password', password);
    } else {
    email = UserProperties.getProperty('email');
    password = UserProperties.getProperty('password');
    }
    var authToken = getGAauthenticationToken(email, password);
    deleteData(authToken, tableIDFusion);
    var updateMsg = updateData(authToken, tableIDFusion);
    var updatedRowsCount = updateMsg.split(/\n/).length - 2;
    SpreadsheetApp.getActiveSpreadsheet().toast("Updated " + updatedRowsCount + " rows in the Fusion Table", "Fusion Tables Update", 5)
    };
  17. @chrislkeller chrislkeller revised this gist Nov 5, 2013. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -64,7 +64,7 @@ If you get stuck and things just aren't working, let me know and I'll see what I

    **...**

    ```Request failed for https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyCBbVMnJwhD5xPYJpcvOT8vOUlLs9jYv5U& returned code 400. Truncated server response: { "error": { "errors": [ { "domain": "fusiontables", "reason": "badQueryCouldNotParse", "message": "Invalid query: Parse error ne... (use muteHttpExceptions option to examine full response)```
    Request failed for https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyCBbVMnJwhD5xPYJpcvOT8vOUlLs9jYv5U& returned code 400. Truncated server response: { "error": { "errors": [ { "domain": "fusiontables", "reason": "badQueryCouldNotParse", "message": "Invalid query: Parse error ne... (use muteHttpExceptions option to examine full response)

    **The following is likely caused by your API key not being recognized.**

  18. @chrislkeller chrislkeller revised this gist Nov 5, 2013. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -64,7 +64,7 @@ If you get stuck and things just aren't working, let me know and I'll see what I

    **...**

    Request failed for https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyCBbVMnJwhD5xPYJpcvOT8vOUlLs9jYv5U& returned code 400. Truncated server response: { "error": { "errors": [ { "domain": "fusiontables", "reason": "badQueryCouldNotParse", "message": "Invalid query: Parse error ne... (use muteHttpExceptions option to examine full response)
    ```Request failed for https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyCBbVMnJwhD5xPYJpcvOT8vOUlLs9jYv5U& returned code 400. Truncated server response: { "error": { "errors": [ { "domain": "fusiontables", "reason": "badQueryCouldNotParse", "message": "Invalid query: Parse error ne... (use muteHttpExceptions option to examine full response)```

    **The following is likely caused by your API key not being recognized.**

  19. @chrislkeller chrislkeller revised this gist Nov 5, 2013. 1 changed file with 5 additions and 3 deletions.
    8 changes: 5 additions & 3 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -70,10 +70,12 @@ If you get stuck and things just aren't working, let me know and I'll see what I

    Request failed for https://www.googleapis.com/fusiontables/v1/query?key=MY API CODE& returned code 401. Truncated server response: { "error": { "errors": [ { "domain": "global", "reason": "authError", "message": "Invalid Credentials", "locationType": "head... (use muteHttpExceptions option to examine full response) (line 79, file "Code")

    Which might just mean that your API key isn't active yet… Or you might receive an error because you didn't authenticate with the application when prompted. Be sure you have activated the Fusion Tables API on the Google API console and added the key to the script in the proper place.
    Which might just mean that your API key isn't active yet… Or you might receive an error because you didn't authenticate with the application when prompted. Be sure you have activated the Fusion Tables API on the [Google API console](https://code.google.com/apis/console/) and added the key to the script in the proper place.

    **The following is likely caused by...**
    Request failed for https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication Url=https://www.google.com/accounts/ContinueSignIn?sarp=1&scc=1&plt=AKgnsbutVGIXKvt9Nhj84zteA6Qk3RgCkgkQzOPygJE4aWgRrWXJ8_N... (use muteHttpExceptions option to examine full response

    Request failed for https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication Url=https://www.google.com/accounts/ContinueSignIn?sarp=1&scc=1&plt=AKgnsbutVGIXKvt9Nhj84zteA6Qk3RgCkgkQzOPygJE4aWgRrWXJ8_N... (use muteHttpExceptions option to examine full response

    **The following is likely caused by...**
    Request failed for https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication Url=https://www.google.com/accounts/ContinueSignIn?sarp=1&scc=1&plt=AKgnsbuxBXbcxmpYifEqDDgyv9RNMHVqos-u7nz2EtXDH4YaDo_EWdY... (use muteHttpExceptions option to examine full response)

    Request failed for https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication Url=https://www.google.com/accounts/ContinueSignIn?sarp=1&scc=1&plt=AKgnsbuxBXbcxmpYifEqDDgyv9RNMHVqos-u7nz2EtXDH4YaDo_EWdY... (use muteHttpExceptions option to examine full response)
  20. @chrislkeller chrislkeller revised this gist Nov 5, 2013. 1 changed file with 17 additions and 5 deletions.
    22 changes: 17 additions & 5 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -56,12 +56,24 @@

    Request failed for https://www.googleapis.com/fusiontables/v1/query?key=ajhdndna8282n29& returned code 403. Server response: { "error": { "errors": [ { "domain": "usageLimits", "reason": "accessNotConfigured", "message": "Access Not Configured" } ], "code": 403, "message": "Access Not Configured" } }

    Which might just mean that your API key isn't active yet… Or you might receive an error because you didn't authenticate with the application when prompted. There are a lot of moving parts and little details to pay attention to, and I'm sure I've overlooked something in this walkthrough.
    13. So let's try to decipher some error messages.

    If you get stuck and things just aren't working, let me know and I'll see what I can do.
    If you get stuck and things just aren't working, let me know and I'll see what I can do. There are a lot of moving parts and little details to pay attention to, and I'm sure I've overlooked something in this walkthrough.

    **Trying to log error messages and the possible cause**
    **Trying to log error messages and the possible cause. These are not confirmed. Just best guesses**

    **...**

    Request failed for https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyCBbVMnJwhD5xPYJpcvOT8vOUlLs9jYv5U& returned code 400. Truncated server response: { "error": { "errors": [ { "domain": "fusiontables", "reason": "badQueryCouldNotParse", "message": "Invalid query: Parse error ne... (use muteHttpExceptions option to examine full response)
    Request failed for https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication (use muteHttpExceptions option to examine full response)

    **The following is likely caused by your API key not being recognized.**

    Request failed for https://www.googleapis.com/fusiontables/v1/query?key=MY API CODE& returned code 401. Truncated server response: { "error": { "errors": [ { "domain": "global", "reason": "authError", "message": "Invalid Credentials", "locationType": "head... (use muteHttpExceptions option to examine full response) (line 79, file "Code")

    Which might just mean that your API key isn't active yet… Or you might receive an error because you didn't authenticate with the application when prompted. Be sure you have activated the Fusion Tables API on the Google API console and added the key to the script in the proper place.

    **The following is likely caused by...**
    Request failed for https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication Url=https://www.google.com/accounts/ContinueSignIn?sarp=1&scc=1&plt=AKgnsbutVGIXKvt9Nhj84zteA6Qk3RgCkgkQzOPygJE4aWgRrWXJ8_N... (use muteHttpExceptions option to examine full response

    **The following is likely caused by...**
    Request failed for https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication Url=https://www.google.com/accounts/ContinueSignIn?sarp=1&scc=1&plt=AKgnsbuxBXbcxmpYifEqDDgyv9RNMHVqos-u7nz2EtXDH4YaDo_EWdY... (use muteHttpExceptions option to examine full response)
  21. @chrislkeller chrislkeller revised this gist Nov 5, 2013. 1 changed file with 7 additions and 1 deletion.
    8 changes: 7 additions & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -58,4 +58,10 @@

    Which might just mean that your API key isn't active yet… Or you might receive an error because you didn't authenticate with the application when prompted. There are a lot of moving parts and little details to pay attention to, and I'm sure I've overlooked something in this walkthrough.

    If you get stuck and things just aren't working, let me know and I'll see what I can do.
    If you get stuck and things just aren't working, let me know and I'll see what I can do.

    **Trying to log error messages and the possible cause**

    Request failed for https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyCBbVMnJwhD5xPYJpcvOT8vOUlLs9jYv5U& returned code 400. Truncated server response: { "error": { "errors": [ { "domain": "fusiontables", "reason": "badQueryCouldNotParse", "message": "Invalid query: Parse error ne... (use muteHttpExceptions option to examine full response)
    Request failed for https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication (use muteHttpExceptions option to examine full response)
  22. @chrislkeller chrislkeller revised this gist Sep 23, 2013. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion spreadsheet_to_fusion_tables.js
    View file
    Original file line number Diff line number Diff line change
    @@ -111,7 +111,8 @@ function updateData(authToken, tableID) {
    }

    var headers = data[0];
    var queryPrepend = "INSERT INTO " + tableID + " (" + "\'" + headers.join("\',\'") + "\'" + ") VALUES ('";
    //var queryPrepend = "INSERT INTO " + tableID + " (" + "\'" + headers.join("\',\'") + "\'" + ") VALUES ('";
    var queryPrepend = "INSERT INTO " + tableID + " (" + headers.join(",") + ") VALUES ('";
    var query = "";

    for (var i = 1; i < data.length; ++i) {
  23. @chrislkeller chrislkeller revised this gist Sep 18, 2013. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -16,7 +16,7 @@

    * First, head to the [API Console dashboard](https://code.google.com/apis/console/) and log in with your Google account if prompted. The first screen you see if an overview. If it's your first time here, you might not see a lot. On the left-side of the screen you'll see a dropdown where you can create a new project. Go ahead and create one and give it a name.

    ![Fusion Table Import]()
    ![Fusion Table Import](http://archives.chrislkeller.com/blog-images/2013/apis-console.png)

    * Next click on Services on the left-side of the screen and you will see a heck of a lot of toggles that can turn on various Google APIs. Scroll down a bit until you find Fusion Tables API, and flip to toggle to the 'On' position.

  24. @chrislkeller chrislkeller revised this gist Sep 18, 2013. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -16,15 +16,15 @@

    * First, head to the [API Console dashboard](https://code.google.com/apis/console/) and log in with your Google account if prompted. The first screen you see if an overview. If it's your first time here, you might not see a lot. On the left-side of the screen you'll see a dropdown where you can create a new project. Go ahead and create one and give it a name.

    ![Fusion Table Import](http://archives.chrislkeller.com/blog-images/2013/apis-console.png)
    ![Fusion Table Import]()

    * Next click on Services on the left-side of the screen and you will see a heck of a lot of toggles that can turn on various Google APIs. Scroll down a bit until you find Fusion Tables API, and flip to toggle to the 'On' position.

    ![Fusion Table Import](http://archives.chrislkeller.com/blog-images/2013/apis-services.png)

    * Finally, click on API Access on the left-side of the screen. You will see two main areas: Authorized API Access and Simple API Access. We're interested in the API Key shown under Simple API Access. I'll copy this somewhere in a text file next to my Encrypted Table ID.

    ![Fusion Table Import]()
    ![Fusion Table Import](http://archives.chrislkeller.com/blog-images/2013/api-console-key.png)

    * Now we're ready to add our script to our spreadsheet. Back at your spreadsheet, go to Tools --&gt; Script Editor and paste the [script code](https://gist.github.com/3013360). I add my Fusion Table's encrypted table ID to the top of the script...

  25. @chrislkeller chrislkeller revised this gist Sep 18, 2013. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -24,7 +24,7 @@

    * Finally, click on API Access on the left-side of the screen. You will see two main areas: Authorized API Access and Simple API Access. We're interested in the API Key shown under Simple API Access. I'll copy this somewhere in a text file next to my Encrypted Table ID.

    ![Fusion Table Import](http://archives.chrislkeller.com/blog-images/2013/api-console-key.png)
    ![Fusion Table Import]()

    * Now we're ready to add our script to our spreadsheet. Back at your spreadsheet, go to Tools --&gt; Script Editor and paste the [script code](https://gist.github.com/3013360). I add my Fusion Table's encrypted table ID to the top of the script...

  26. @chrislkeller chrislkeller revised this gist Sep 18, 2013. No changes.
  27. @chrislkeller chrislkeller revised this gist Sep 11, 2013. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,7 @@
    ### Script to sync a Google SpreadSheet to a Fusion Table

    **Thanks** goes out to [John McGrath](https://github.com/jomcgrath2/Update-Fusion) who did the bulk of the work in making this script happen.

    * Head to Google docs, create a spreadsheet and add some data to it. Simple enough right? Just make sure that Column A has data in it. It seems blank or null values will break the update function. Though if other columns are without data the script appears to work fine. At some point, I'll learn to add an error message if Column A is blank, but for now, Column A wants data.

    ![Google spreadsheet](http://archives.chrislkeller.com/blog-images/2012/01/37380956-2012-01-31_11h24_34.png)
  28. @chrislkeller chrislkeller revised this gist Jun 15, 2013. 2 changed files with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,5 @@
    ### Script to sync a Google SpreadSheet to a Fusion Table

    * Head to Google docs, create a spreadsheet and add some data to it. Simple enough right? Just make sure that Column A has data in it. It seems blank or null values will break the update function. Though if other columns are without data the script appears to work fine. At some point, I'll learn to add an error message if Column A is blank, but for now, Column A wants data.

    ![Google spreadsheet](http://archives.chrislkeller.com/blog-images/2012/01/37380956-2012-01-31_11h24_34.png)
    0 SpreadSheet To Fusion Tables → spreadsheet_to_fusion_tables.js
    View file
    File renamed without changes.
  29. @chrislkeller chrislkeller revised this gist Jun 15, 2013. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -27,12 +27,12 @@
    // Add the encrypted table ID of the fusion table here
    var tableIDFusion = '17xnxY......';

    Then I add my API key.
    * Then I add my API key.

    // key needed for fusion tables api
    var fusionTablesAPIKey = '17xnxY......';

    and click save. You will be prompted to give the project a name. "Update Fusion Tables" works. Click the save icon or go to File --&gt; Save.
    * Click save. You will be prompted to give the project a name. "Update Fusion Tables" works. Click the save icon or go to File --&gt; Save.

    * Reload the spreadsheet and you will see a new menu item next to help. Mine says "Data Update Functions." Click the menu item and you will see three options, though the names may differ at this point: "Change Range of Data to be Sent (Include Headers)", "Update Fusion Table" & "Change Email Information."

  30. @chrislkeller chrislkeller revised this gist Jun 15, 2013. 1 changed file with 49 additions and 15 deletions.
    64 changes: 49 additions & 15 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,23 +1,57 @@
    ### SpreadSheet to Fusion Tables
    * Head to Google docs, create a spreadsheet and add some data to it. Simple enough right? Just make sure that Column A has data in it. It seems blank or null values will break the update function. Though if other columns are without data the script appears to work fine. At some point, I'll learn to add an error message if Column A is blank, but for now, Column A wants data.

    This is a version of a script that John McGrath via the Google Fusion Tables group authored to create a manual "sync" between a Google spreadsheet and a Google Fusion Table.

    John's GitHub repo is [here](https://github.com/jomcgrath2/Update-Fusion).
    ![Google spreadsheet](http://archives.chrislkeller.com/blog-images/2012/01/37380956-2012-01-31_11h24_34.png)

    I've modified the script a bit for my needs and have added use of an [API key](https://developers.google.com/fusiontables/docs/v1/migration_guide#ownClient) and the [new Fusion Tables API endpoint](https://developers.google.com/fusiontables/docs/v1/migration_guide) as the original version used the SQL API endpoint, which is being phased out.
    * Now I'm going to create a Fusion Table based off my spreadsheet. I do this either by importing from Google Docs or downloading the spreadsheet as a csv and uploading it to Fusion Tables. **It is important to note** from the outset that the column names must match between the spreadsheet and the table. Remember, if you change a column name or add a column to the spreadsheet, be sure to change it/add it to the Fusion Table as well. For good measure I make sure any new columns are also in the same order.

    To use, simply add your Fusion Table's encrypted table ID to the top of the script...
    ![Fusion Table Import](http://archives.chrislkeller.com/blog-images/2012/01/37380957-2012-01-31_11h25_24.png)

    // Add the encrypted table ID of the fusion table here
    var tableIDFusion = '17xnxY......';

    And add your [api key](https://code.google.com/apis/console/)...
    * After my Fusion Table is created, I need to get the Encrypted Table ID in order to make sure the spreadsheet can access it. To find the Encrypted Table ID I click File --> About this table. I'll copy this somewhere in a text file until I need it.

    // key needed for fusion tables api
    var fusionTablesAPIKey = '17xnxY......';
    * I'm almost ready to make this happen, but I'm going to need one other piece of information - a Google Fusion Tables API key. I need to turn on access to the Fusion Tables API in Google's API console, and get an authentication key. Don't worry, this is much easier than it sounds.

    I attempted to write a how-to/walkthrough on using it [here](http://blog.chrislkeller.com/google-group-user-john-m-posts-script-that-br/).
    * First, head to the [API Console dashboard](https://code.google.com/apis/console/) and log in with your Google account if prompted. The first screen you see if an overview. If it's your first time here, you might not see a lot. On the left-side of the screen you'll see a dropdown where you can create a new project. Go ahead and create one and give it a name.

    This script needs to be triggered manually, but there are [some methods](http://www.chrislkeller.com/so-you-want-a-timely-sync-between-your-google) that will allow for automated triggers to be set that I've used with some success to dynamically update the table.
    ![Fusion Table Import](http://archives.chrislkeller.com/blog-images/2013/apis-console.png)

    There is also a method that will allow data entered into Google Form to be POSTed to a table. You can read about that [here](http://kh-samples.googlecode.com/svn/trunk/code/instructions.html).
    * Next click on Services on the left-side of the screen and you will see a heck of a lot of toggles that can turn on various Google APIs. Scroll down a bit until you find Fusion Tables API, and flip to toggle to the 'On' position.

    ![Fusion Table Import](http://archives.chrislkeller.com/blog-images/2013/apis-services.png)

    * Finally, click on API Access on the left-side of the screen. You will see two main areas: Authorized API Access and Simple API Access. We're interested in the API Key shown under Simple API Access. I'll copy this somewhere in a text file next to my Encrypted Table ID.

    ![Fusion Table Import](http://archives.chrislkeller.com/blog-images/2013/api-console-key.png)

    * Now we're ready to add our script to our spreadsheet. Back at your spreadsheet, go to Tools --&gt; Script Editor and paste the [script code](https://gist.github.com/3013360). I add my Fusion Table's encrypted table ID to the top of the script...

    // Add the encrypted table ID of the fusion table here
    var tableIDFusion = '17xnxY......';

    Then I add my API key.

    // key needed for fusion tables api
    var fusionTablesAPIKey = '17xnxY......';

    and click save. You will be prompted to give the project a name. "Update Fusion Tables" works. Click the save icon or go to File --&gt; Save.

    * Reload the spreadsheet and you will see a new menu item next to help. Mine says "Data Update Functions." Click the menu item and you will see three options, though the names may differ at this point: "Change Range of Data to be Sent (Include Headers)", "Update Fusion Table" & "Change Email Information."

    ![](http://archives.chrislkeller.com/blog-images/2012/01/37380960-2012-01-31_11h30_49.png)

    9. First choose "Change Email Information." This will authenticate your gmail account to access the Fusion Table. **Note**: I HAVE NOT had success using this with a Google Apps account going to a private Gmail account. Click the couple of confirmation buttons that appear.

    10. Second you should select all of the data -- columns and rows -- you wish to sync and choose Change Range of Data to be Sent (Include Headers)". I usually just click the rectangle in the upper-left corner to sync everything. The beauty -- in my experience -- is that blank rows and columns can be synced but they won't be reflected on the Fusion Table. Click the two confirmation buttons that appear.

    ![](http://archives.chrislkeller.com/blog-images/2012/01/37380963-2012-01-31_11h35_44.png)

    11. Now just add some information to your spreadsheet and click "Update Fusion Table." Your spreadsheet data should be synced with your Fusion Table.

    ![](http://archives.chrislkeller.com/blog-images/2012/01/37380965-2012-01-31_11h40_29.png)

    12. Sit back and enjoy this moment … unless it didn't work. Perhaps you received an error message like:

    Request failed for https://www.googleapis.com/fusiontables/v1/query?key=ajhdndna8282n29& returned code 403. Server response: { "error": { "errors": [ { "domain": "usageLimits", "reason": "accessNotConfigured", "message": "Access Not Configured" } ], "code": 403, "message": "Access Not Configured" } }

    Which might just mean that your API key isn't active yet… Or you might receive an error because you didn't authenticate with the application when prompted. There are a lot of moving parts and little details to pay attention to, and I'm sure I've overlooked something in this walkthrough.

    If you get stuck and things just aren't working, let me know and I'll see what I can do.