Fusion table to be updated from google spreadsheet but not sync. and should avoid duplicates

Question

Have a Google spreadsheet which is regularly updated with daily sales data we need only current two months data maximum at any give point of time in spread sheet rest needs to be backed up in fusion table and removed from spread sheet. Can any one suggest how this can be accomplish. the Google spread sheet to fusion table script is not working due to authorisation issue. I am not script writer. ( kind of incremental backup)

What is the exact error you get? Did you followed the documentation to authorize "Advanced Google Services"? https://developers.google.com/apps-script/guides/services/advanced — Gerardo, Apr 03 '15 at 15:57
Thanks for taking interest. I had used this script https://gist.github.com/chrislkeller/3013360 and it says Invalid Credentials. And more over I am not looking for sync. I need to update the fusion table with certain range, incremental backup kind. — Kanted, Apr 04 '15 at 06:59

score 0 · Answer 1 · answered Apr 06 '15 at 21:33

After looking into the code I noticed you need to provide the email and password which is not very secure, so I made some changes to the code that may help you with this.

The main function "updateFusion" I reduce it like this:

function updateFusion()
{

 var query = '';
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var lastRow = ss.getLastRow();
 var lastCol = ss.getLastColumn();
 var range = ss.getActiveSheet().getRange(1,1,lastRow, lastCol);
 query = updateData(range, tableIDFusion) ;
 //FusionTables.Table
 var fusion = FusionTables.Query.sql(query);

 ss.deleteRows(2, lastRow-1);
}

Basically i got rid of the authentication process since appscript handles this. now I select all the information available instead of a predefined range. then I call the function "updateData" passing the range and the table ID I also modified this function, I just got rid of the part that selects the range, adjusted the code to create the right query and returned the query after concatenating it.

function updateData(range, tableID) {


    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, "\\'");
            }
        }
    }

    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 query;   
};

After creating the query to insert the new data i call the FusionTable service and then

var fusion = FusionTables.Query.sql(query);

This service takes care of the authentication. after that the script will delete the rows used to insert the values to the table, I would recommend that you test this script first.

In order for this script to work, you will have to share the sheet and the fusion table with the people that will be working with it. Also you will need to activate the FusionTable Service as mention in this documentation.

I know there are many things to do, but i hope this helps.

Fusion table to be updated from google spreadsheet but not sync. and should avoid duplicates

1 Answers1