public string ParseGoogleDocsSpreadsheet(Hashtable State, string data_source_id, string use_spreadsheet_name, string username, string password, string url)
{
try
{
string connection_string = null;
DataSources DS = new DataSources();
if (password == null)
{ //get password from DB
State["DataSourceID"] = data_source_id;
connection_string = DS.GetDataSourceDatabaseConnection(State);
string[] splits = connection_string.Split(';');
foreach (string split in splits)
{
string[] parts = split.Split('=');
if (parts[0] == "password")
{
password = parts[1];
break;
}
}
if (password == null)
return null;
}
SpreadsheetsService service = new SpreadsheetsService(State["SelectedApp"].ToString());
service.setUserCredentials(username, password);
//get all spreadsheets
Google.GData.Spreadsheets.SpreadsheetQuery query = new Google.GData.Spreadsheets.SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
bool found_spreadsheet = false;
Hashtable tables = new Hashtable();
foreach (SpreadsheetEntry entry in feed.Entries)
{
string spreadsheet_name = entry.Title.Text;
if (spreadsheet_name.ToLower() == use_spreadsheet_name.ToLower())
{
//Use this spreadsheet
found_spreadsheet = true;
AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
//get all worksheets
WorksheetQuery wk_query = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed wk_feed = service.Query(wk_query);
foreach (WorksheetEntry worksheet in wk_feed.Entries)
{
string table_name = worksheet.Title.Text;
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery list_query = new ListQuery(listFeedLink.HRef.ToString());
ListFeed list_feed = service.Query(list_query);
//get field names
if (list_feed.Entries.Count == 0)
{
return "The worksheet " + table_name + " has no values.";
}
ListEntry fieldRow = (ListEntry)list_feed.Entries[0];
ArrayList field_list = new ArrayList();
foreach (ListEntry.Custom column in fieldRow.Elements)
{
Hashtable field = new Hashtable();
field["name"] = column.LocalName;
field_list.Add(field);
}
tables[table_name] = field_list;
}
break;
}
}
if (!found_spreadsheet)
{
return use_spreadsheet_name + " could not be found";
}
connection_string = "spreadsheet=" + use_spreadsheet_name + ";username="******";password="******";url='" + url + "'";
State["DBConnectionString"] = connection_string;
DS.SaveGoogleSpreadsheetDataSource(State, data_source_id, "GoogleDocs", connection_string, tables);
return "OK";
}
catch (Exception ex)
{
Util util = new Util();
util.LogError(State, ex);
if (ex.Message.Contains("Execution of request failed"))
return "Credentials to your Google Docs Account or spreadsheet name are not valid.";
else
return "There was an internal error with access to your Google Docs account.";
}
}
protected void Page_Load(object sender, EventArgs e)
{
Hashtable State = (Hashtable)HttpRuntime.Cache[Session.SessionID];
if (State == null || State.Count <= 2) { Page.ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "timeOut('../Default.aspx');", true); return; }
GetGoogleDocsInfoHelp.Attributes.Add("onclick", PopupHelper.GeneratePopupScript(
"../Help/PageData/GetGoogleDocsInfoHelp.htm", 350, 700, false, false, false, true));
GetGoogleDocsInfoHelp2.Attributes.Add("onclick", PopupHelper.GeneratePopupScript(
"../Help/PageData/GetGoogleDocsInfoHelp.htm", 350, 700, false, false, false, true));
DataSources DS = new DataSources();
if (!IsPostBack)
{
string id = Request.QueryString.Get("id");
if (id != null)
{
State["DataSourceID"] = DataSourceID.Text = id;
State["DBConnectionString"] = DS.GetDataSourceDatabaseConnection(State);
}
if (!State["AccountType"].ToString().Contains("google_apps"))
{
if ( State["DBConnectionString"] != null)
{
string[] parts = State["DBConnectionString"].ToString().Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
foreach (string part in parts)
{
string[] parms = part.Split("=".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
switch (parms[0])
{
case "username":
Username.Text = parms[1];
break;
case "password":
Password.Text = parms[1];
break;
case "spreadsheet":
AccountSpreadsheets.Items.Clear();
AccountSpreadsheets.Items.Add(new RadComboBoxItem("Select Spreadsheet ->", "->"));
AccountSpreadsheets.Items.Add(new RadComboBoxItem(parms[1], parms[1]));
AccountSpreadsheets.SelectedIndex = 1;
break;
}
}
}
}
else if ( State["AccountType"].ToString().Contains("google_apps"))
{
ContentMultiPage.SelectedIndex = 1;
GDocs gDocs = new GDocs();
string ret = gDocs.GetSpreadsheets(State, Spreadsheets);
if (ret != "OK")
{
SaveGoogleDocsInfoMessage.Text = "There was an error in getting spreadsheet info from your Google Apps. " + ret;
return;
}
if ( State["DBConnectionString"] != null)
{
string[] parts = State["DBConnectionString"].ToString().Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
foreach (string part in parts)
{
string[] parms = part.Split("=".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
switch (parms[0])
{
case "spreadsheet":
Spreadsheets.SelectedValue = parms[0];
break;
}
}
}
}
}
}
protected void TestQueryButton_Click(object sender, EventArgs e)
{
Util util = new Util();
Hashtable State = (Hashtable)HttpRuntime.Cache[Session.SessionID];
if (util.CheckSessionTimeout(State, Response, "../../Default.aspx")) return;
//default message
Message.Text = "Enter test values for empty device fields in the Query and click on "Test Spreadsheet Command"";
DataSources DS = new DataSources();
string DBConnectionString = DS.GetDataSourceDatabaseConnection(State);
bool doGoogleApps = false;
try
{
string[] parts = DBConnectionString.Split(";".ToCharArray());
string spreadsheet = null;
string username = null;
string password = null;
string consumer_key = null;
string consumer_secret = null;
string requestor_id = null;
foreach(string part in parts)
{
string[] parms = part.Split("=".ToCharArray());
switch(parms[0])
{
case "spreadsheet":
spreadsheet = parms[1];
break;
case "username":
username = parms[1];
break;
case "password":
password = parms[1];
break;
case "consumer_key":
consumer_key = parms[1];
break;
case "consumer_secret":
consumer_secret = parms[1];
break;
case "requestor_id":
requestor_id = parms[1];
break;
}
}
string initGoogleDocsParams = null;
if (password != null) {
initGoogleDocsParams = "?username="******"&password="******"&spreadsheet=" + HttpUtility.UrlEncode(spreadsheet);
doGoogleApps = false;
}
else {
initGoogleDocsParams = "?requestor_id=" + requestor_id + "&consumer_key=" + consumer_key + "&consumer_secret=" + consumer_secret + "&spreadsheet=" + HttpUtility.UrlEncode(spreadsheet);
doGoogleApps = true;
}
doSqlCommand(State,doGoogleApps,initGoogleDocsParams);
}
catch(Exception ex)
{
string x = ex.Message + ": " + ex.StackTrace;
return; //caused when use tries to build output before input is done
}
}
