private static void pullSiteData(string siteName, String sheetKey, Queue <string> sheets)
{
SpreadsheetsResource.GetRequest request = service.Spreadsheets.Get(sheetKey);
request.IncludeGridData = true;
try
{
Google.Apis.Sheets.v4.Data.Spreadsheet response = request.Execute();
foreach (var sheetName in sheets)
{
try
{
Sheet sheet = response.Sheets.FirstOrDefault <Sheet>(s => s.Properties.Title == sheetName);
if (sheet == null)
{
Console.WriteLine("Couldn't find sheet {0} in spreadsheet {1} {2}", sheetName, siteName, sheetKey);
continue;
}
foreach (var row in sheet.Data.First <GridData>().RowData)
{
Console.Write("{0}\t{1}\t", siteName, sheetName);
foreach (var cell in row.Values)
{
Console.Write("{0}\t", (cell.FormattedValue ?? "").Trim());
}
Console.WriteLine();
}
}
catch (Exception e)
{
Console.WriteLine("Error processing sheet data for {0}:{1}:{2}={3}", siteName, sheetKey, sheetName, e.Message);
};
}
}
catch (Exception e)
{
Console.WriteLine("Couldn't get sheet data for {0} on {1} = {2}", siteName, sheetKey, e.GetType());
};
}
//Creates a new sheet with sheetname
static void CreateNewSheet(string SheetName)
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = GetCredential(),
ApplicationName = ApplicationName,
});
// TODO: Assign values to desired properties of `requestBody`:
Data.Spreadsheet requestBody = new Data.Spreadsheet();
requestBody.Properties = new SpreadsheetProperties();
requestBody.Properties.Title = SheetName;
SpreadsheetsResource.CreateRequest request = sheetsService.Spreadsheets.Create(requestBody);
Data.Spreadsheet response = request.Execute();
string ok = response.SpreadsheetId;
//1xt6CqlJgpxW2W8rNZvpT9nVRm--xzyo7dVSNY4qQE5w
// TODO: Change code below to process the `response` object:
//Console.WriteLine(JsonConvert.SerializeObject(response));
}
private static SheetsService AuthorizeGoogleAppForSheetsService()
{
UserCredential credential;
using (var stream =
new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
{
string credPath = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);
}
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
//Creating the sheet now in Google Sheets after being authorised.
Guid obj = Guid.NewGuid();
string sheetName = string.Format("{0} - {1}", "Google Sheets Testing", obj.ToString());
var myNewSheet = new Google.Apis.Sheets.v4.Data.Spreadsheet();
myNewSheet.Properties = new SpreadsheetProperties();
myNewSheet.Properties.Title = sheetName;
var newSheet = service.Spreadsheets.Create(myNewSheet).Execute();
sheetId = newSheet.SpreadsheetId;
return(service);
}
//Creates a new Sheet with tabs
static void CreateNewSheet(string SheetTitle, IList <Sheet> Sheets)
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = GetCredential(),
ApplicationName = ApplicationName,
});
// TODO: Assign values to desired properties of `requestBody`:
Data.Spreadsheet requestBody = new Data.Spreadsheet();
requestBody.Properties = new SpreadsheetProperties();
requestBody.Properties.Title = SheetTitle;
requestBody.Sheets = Sheets;
SpreadsheetsResource.CreateRequest request = sheetsService.Spreadsheets.Create(requestBody);
Data.Spreadsheet response = request.Execute();
// TODO: Change code below to process the `response` object:
//Console.WriteLine(JsonConvert.SerializeObject(response));
}
/// <summary>
/// this will get the index of the sheet's location to be used for GridRange usages,returns -1 if not found
/// </summary>
/// <param name="SheetName"></param>
/// <returns></returns>
public static int?GetSheetIdBySheetName(string spreadsheetId, string SheetName)
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = GetCredential(),
ApplicationName = ApplicationName,
});
SpreadsheetsResource.GetRequest request = sheetsService.Spreadsheets.Get(spreadsheetId);
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Data.Spreadsheet response = request.Execute();
for (int i = 0; i < response.Sheets.Count; i++)
{
Sheet tempSheet = response.Sheets[i];
if (tempSheet.Properties.Title == SheetName)
{
return(tempSheet.Properties.SheetId);
}
}
return(-1);
}
static void CreateNewSheet()
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = GetCredential(),
ApplicationName = ApplicationName,
});
// TODO: Assign values to desired properties of `requestBody`:
Data.Spreadsheet requestBody = new Data.Spreadsheet();
//IDs
requestBody.SpreadsheetId = "thebakerid";
Sheet sheet = new Sheet();
Sheet sheet2 = new Sheet();
sheet.Properties = new SheetProperties();
sheet2.Properties = new SheetProperties();
sheet.Properties.Title = "test";
sheet.Properties.SheetId = 1;
sheet2.Properties.SheetId = 2;
sheet.Properties.Title = "test2";
requestBody.Sheets = new List <Sheet>();
requestBody.Sheets.Add(sheet);
requestBody.Sheets.Add(sheet2);
//
SpreadsheetsResource.CreateRequest request = sheetsService.Spreadsheets.Create(requestBody);
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Data.Spreadsheet response = request.Execute();
// Data.Spreadsheet response = await request.ExecuteAsync();
// TODO: Change code below to process the `response` object:
Console.WriteLine(JsonConvert.SerializeObject(response));
}
// set servers data in list
public void SetDataInTable(Data.Spreadsheet response, SheetsService service, List <Server> servers)
{
string spreadSheetId = response.SpreadsheetId;
List <int?> sheetsId = new List <int?>();
string currentDate = DateTime.Now.ToString();
foreach (var item in response.Sheets)
{
sheetsId.Add(item.Properties.SheetId);
}
for (int i = 0; i < servers.Count; i++)
{
Server server = servers[i];
int? sheetId = sheetsId[i];
string[,] data = new string[server.dbData.Count, 4];
foreach (string key in server.dbData.Keys)
{
data.SetValue(server.name, 0, 0);
data.SetValue(key, 0, 1);
data.SetValue(server.dbData[key], 0, 2);
data.SetValue(currentDate, 0, 3);
server.dbData.Remove(key);
break;
}
int count = 1;
foreach (string key in server.dbData.Keys)
{
data.SetValue(" ", count, 0);
data.SetValue(key, count, 1);
data.SetValue(server.dbData[key], count, 2);
data.SetValue(" ", count, 3);
++count;
}
List <Data.Request> requests = new List <Data.Request>();
for (int j = 0; j < data.GetLength(0); j++)
{
List <Data.CellData> values = new List <Data.CellData>();
for (int k = 0; k < data.GetLength(1); k++)
{
values.Add(new Data.CellData()
{
UserEnteredValue = new Data.ExtendedValue()
{
StringValue = data[j, k]
}
});
}
requests.Add(
new Data.Request
{
UpdateCells = new Data.UpdateCellsRequest()
{
Start = new Data.GridCoordinate()
{
SheetId = sheetId,
RowIndex = j + 1,
ColumnIndex = 0
},
Rows = new List <Data.RowData> {
new Data.RowData {
Values = values
}
},
Fields = "userEnteredValue"
}
});
Data.BatchUpdateSpreadsheetRequest bUpd = new Data.BatchUpdateSpreadsheetRequest
{
Requests = requests
};
service.Spreadsheets.BatchUpdate(bUpd, spreadSheetId).Execute();
}
string[] dataBasement = { server.name, "Volume", server.size, currentDate };
List <Data.CellData> valuesB = new List <Data.CellData>();
foreach (string dataB in dataBasement)
{
valuesB.Add(new Data.CellData()
{
UserEnteredValue = new Data.ExtendedValue()
{
StringValue = dataB
}
});
}
requests.Add(
new Data.Request
{
UpdateCells = new Data.UpdateCellsRequest()
{
Start = new Data.GridCoordinate()
{
SheetId = sheetId,
RowIndex = server.dbData.Count + 1,
ColumnIndex = 0
},
Rows = new List <Data.RowData> {
new Data.RowData {
Values = valuesB
}
},
Fields = "userEnteredValue"
}
});
Data.BatchUpdateSpreadsheetRequest bUpsRequest = new Data.BatchUpdateSpreadsheetRequest
{
Requests = requests
};
service.Spreadsheets.BatchUpdate(bUpsRequest, spreadSheetId).Execute();
}
}
void CreateTable(string tableName)
{
if (spreadSheetsService == null)
{
spreadSheetsService = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = applicationName,
});
}
try
{
// TODO: Assign values to desired properties of `requestBody`:
Google.Apis.Sheets.v4.Data.Spreadsheet requestBody = new Google.Apis.Sheets.v4.Data.Spreadsheet();
requestBody.Properties = new SpreadsheetProperties();
requestBody.Properties.Title = tableName;
List <Sheet> sheets = new List <Sheet>();
Sheet mainSheet = new Sheet();
mainSheet.Properties = new SheetProperties();
mainSheet.Properties.Title = "Notesieve";
sheets.Add(mainSheet);
requestBody.Sheets = sheets;
SpreadsheetsResource.CreateRequest request = spreadSheetsService.Spreadsheets.Create(requestBody);
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Google.Apis.Sheets.v4.Data.Spreadsheet response = request.Execute();
// Data.Spreadsheet response = await request.ExecuteAsync();
// TODO: Change code below to process the `response` object:
// Console.WriteLine();
if (driveService == null)
{
driveService = new DriveService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = applicationName,
});
}
var fileMetadata = new Google.Apis.Drive.v3.Data.File()
{
Name = jsonFileName,
Parents = new List <string>()
{
"appDataFolder"
}
};
using (StreamWriter sw = new StreamWriter(jsonFileLocalPath, false, System.Text.Encoding.Default))
{
if (gSyncSettings == null)
{
gSyncSettings = new GSyncSettings();
}
gSyncSettings.spreadshetrID = response.SpreadsheetId;
gSyncSettings.dataVersion = 0;
sw.WriteLine(JsonConvert.SerializeObject(gSyncSettings));
}
FilesResource.CreateMediaUpload newRequest;
using (var stream = new System.IO.FileStream(jsonFileLocalPath, System.IO.FileMode.Open))
{
newRequest = driveService.Files.Create(fileMetadata, stream, "application/json");
newRequest.Fields = "id";
newRequest.Upload();
gSyncSettingFileId = newRequest.ResponseBody.Id;
}
File.Delete(jsonFileLocalPath);
}
catch (System.Net.Http.HttpRequestException e)
{
OnSyncStateChanged?.Invoke(gSyncState.eLostConnection);
}
}
/// <summary>
/// Create spreadsheet Top-10 of populations county
/// Title contains datetime of creation in format yyyy-MM-ddTHH.mm.ss.fff
/// </summary>
/// <param name="spsheedid"></param>
/// <param name="spsheeturl"></param>
public static void creategooglesheettop10(Icensusapi xco, ref string spsheedid, ref string spsheeturl)
{
//define exist spreadsheet
spreadsheetId = spsheedid;
spreadsheetUrl = spsheeturl;
//get the top 10 most populous county in the united states
List <population> top10 = null;
string xfs = xco.getpopulations();
List <population> xpop = helper.getpoplistfromjson(xfs);
if (xpop != null)
{
top10 = xpop.OrderByDescending(x => x.POP).Take(10).ToList(); //10
}
if (top10 != null)
{
foreach (var item in top10)
{
Console.WriteLine("Get data for " + item.GEONAME);
// Estimated Components of Resident Population Change
string statbd = xco.getstats_birth_death(item.state, item.county);
item.statsbd = helper.getstatbdfromjson(statbd);
// Detailed Language Spoken (LANG7)
string langs = xco.getstats_language(item.state, item.county);
item.languages = helper.getlanglistfromjson(langs);
// population estimate by agegroup per State
// agegroup "18 years and over"
string pop18 = xco.getpopulations18(item.state);
population18 p18 = helper.getpop18fromjson(pop18);
if (p18 != null)
{
item.POP18 = p18.POP;
}
// population estimate by agegroup per State
// agegroup 0 - all population
string pop00 = xco.getpopulations00(item.state);
population18 p00 = helper.getpop18fromjson(pop00);
if (p00 != null)
{
item.POP00 = p00.POP;
}
}
// Columns for general information
string[] colNames = new[] { "Name", "Population",
"State's population",
"State's population\nwith age older\nthan 18 years",
"Last update" };
// Columns for Population Change
string[] colStatsBD = new[] { "Births in period",
"Deaths in period", "Natural increase\nin period", "Birth rate\nin period", "Death rate\nin period", "Period" };
// Columns for Detailed Language
string[] colLangs = new[] { "Description", "Population" };
// Define range template
string range1 = "{0}!A1:E1";
string range2 = "{0}!A2:E2";
string range3 = "{0}!A{1}";
string range2_2 = "{0}!A{1}:F{2}";
string range4 = "{0}!A{1}:B{2}";
// Google oauth credentials
setcredentials();
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
//HttpClientInitializer = sacredential,
//ApiKey = apikey,
ApplicationName = ApplicationName,
});
// Create Spreadsgeet
#region
if (spreadsheetId == "")
{
Data.Spreadsheet requestBody = new Data.Spreadsheet();
requestBody.Properties = new SpreadsheetProperties();
requestBody.Properties.Title = "Top-10 of populations county " +
DateTime.Now.ToString("yyyy-MM-ddTHH.mm.ss.fff");
requestBody.Sheets = new List <Sheet>();
// Create sheets
foreach (var item in top10)
{
Console.WriteLine("Create sheet for " + item.GEONAME);
Sheet x = new Sheet();
x.Properties = new SheetProperties();
x.Properties.Title = item.GEONAME;
requestBody.Sheets.Add(x);
}
// Execute
SpreadsheetsResource.CreateRequest request =
service.Spreadsheets.Create(requestBody);
for (int xy = 0; xy < 3; xy++)
{
try
{
Data.Spreadsheet response = request.Execute();
spreadsheetId = response.SpreadsheetId;
spreadsheetUrl = response.SpreadsheetUrl;
spsheedid = spreadsheetId;
spsheeturl = spreadsheetUrl;
break;
}
catch (Exception ex)
{
if (ex.Message.IndexOf("USER-100s") >= 0)
{
Thread.Sleep(50000); // Sleep 50 * 3 seconds while clear limit 100s
}
else
{
Console.WriteLine("An error occurred: " + ex.ToString());
break;
}
}
}
}
#endregion
// Insert data into spreadsheet
if (spreadsheetId != "")
{
foreach (var item in top10)
{
Console.WriteLine("Add data row to sheet " + item.GEONAME);
// Add general information
#region
if (range1 != "")
{
string range = String.Format(range1, item.GEONAME);
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";
valueRange.Values = new List <IList <object> >();
for (int ix = 0; ix < colNames.Length; ix++)
{
var oblist = new List <object>()
{
colNames[ix]
};
valueRange.Values.Add(oblist);
}
SpreadsheetsResource.ValuesResource.UpdateRequest update =
service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
for (int xy = 0; xy < 3; xy++)
{
try
{
UpdateValuesResponse result2 = update.Execute();
break;
}
catch (Exception ex)
{
if (ex.Message.IndexOf("USER-100s") >= 0)
{
Thread.Sleep(50000); // Sleep 50 * 3 seconds while clear limit 100s
}
else
{
break;
}
}
}
}
if (range2 != "")
{
string range = String.Format(range2, item.GEONAME);
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";
valueRange.Values = new List <IList <object> >();
string[] po = item.ptoa;
for (int ix = 0; ix < po.Length; ix++)
{
var oblist = new List <object>()
{
po[ix]
};
valueRange.Values.Add(oblist);
}
SpreadsheetsResource.ValuesResource.UpdateRequest update =
service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
for (int xy = 0; xy < 3; xy++)
{
try
{
UpdateValuesResponse result2 = update.Execute();
break;
}
catch (Exception ex)
{
if (ex.Message.IndexOf("USER-100s") >= 0)
{
Thread.Sleep(50000); // Sleep 50 * 3 seconds while clear limit 100s
}
else
{
break;
}
}
}
}
#endregion
// Add Population Change information
#region
int r = 4;
if (range3 != "")
{
string range = String.Format(range3, item.GEONAME, r);
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";
valueRange.Values = new List <IList <object> > {
new List <object>()
{
"Estimated Resident Population Change, and Rates of Resident Population Change"
}
};
SpreadsheetsResource.ValuesResource.UpdateRequest update =
service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
for (int xy = 0; xy < 3; xy++)
{
try
{
UpdateValuesResponse result2 = update.Execute();
break;
}
catch (Exception ex)
{
if (ex.Message.IndexOf("USER-100s") >= 0)
{
Thread.Sleep(50000); // Sleep 50 * 3 seconds while clear limit 100s
}
else
{
break;
}
}
}
r++;
}
if (range2_2 != "")
{
string range = String.Format(range2_2, item.GEONAME, r, r);
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";
valueRange.Values = new List <IList <object> >();
string[] po = colStatsBD;
for (int ix = 0; ix < po.Length; ix++)
{
var oblist = new List <object>()
{
po[ix]
};
valueRange.Values.Add(oblist);
}
SpreadsheetsResource.ValuesResource.UpdateRequest update =
service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
for (int xy = 0; xy < 3; xy++)
{
try
{
UpdateValuesResponse result2 = update.Execute();
break;
}
catch (Exception ex)
{
if (ex.Message.IndexOf("USER-100s") >= 0)
{
Thread.Sleep(50000); // Sleep 50 * 3 seconds while clear limit 100s
}
else
{
break;
}
}
}
r++;
}
if (range2_2 != "")
{
string range = String.Format(range2_2, item.GEONAME, r, r);
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";
valueRange.Values = new List <IList <object> >();
string[] po = item.statsbd.ptoa;
for (int ix = 0; ix < po.Length; ix++)
{
var oblist = new List <object>()
{
po[ix]
};
valueRange.Values.Add(oblist);
}
SpreadsheetsResource.ValuesResource.UpdateRequest update =
service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
r += 2;
for (int xy = 0; xy < 3; xy++)
{
try
{
UpdateValuesResponse result2 = update.Execute();
break;
}
catch (Exception ex)
{
if (ex.Message.IndexOf("USER-100s") >= 0)
{
Thread.Sleep(50000); // Sleep 50 * 3 seconds while clear limit 100s
}
else
{
break;
}
}
}
}
#endregion
// Add Language Spoken information
#region
if (range3 != "")
{
string range = String.Format(range3, item.GEONAME, r);
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";
valueRange.Values = new List <IList <object> > {
new List <object>()
{
"Detailed Language Spoken"
}
};
SpreadsheetsResource.ValuesResource.UpdateRequest update =
service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
for (int xy = 0; xy < 3; xy++)
{
try
{
UpdateValuesResponse result2 = update.Execute();
break;
}
catch (Exception ex)
{
if (ex.Message.IndexOf("USER-100s") >= 0)
{
Thread.Sleep(50000); // Sleep 50 * 3 seconds while clear limit 100s
}
else
{
break;
}
}
}
r++;
}
if (range4 != "")
{
string range = String.Format(range4, item.GEONAME, r, r);
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";
valueRange.Values = new List <IList <object> >();
string[] po = colLangs;
for (int ix = 0; ix < po.Length; ix++)
{
var oblist = new List <object>()
{
po[ix]
};
valueRange.Values.Add(oblist);
}
SpreadsheetsResource.ValuesResource.UpdateRequest update =
service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
for (int xy = 0; xy < 3; xy++)
{
try
{
UpdateValuesResponse result2 = update.Execute();
break;
}
catch (Exception ex)
{
if (ex.Message.IndexOf("USER-100s") >= 0)
{
Thread.Sleep(50000); // Sleep 50 * 3 seconds while clear limit 100s
}
else
{
break;
}
}
}
r++;
}
if (item.languages != null && item.languages.Count > 0)
{
for (int xi = 0; xi < item.languages.Count; xi++)
{
string range = String.Format(range4, item.GEONAME, r, r);
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";
valueRange.Values = new List <IList <object> >();
string[] po = item.languages[xi].ptoa;
for (int ix = 0; ix < po.Length; ix++)
{
var oblist = new List <object>()
{
po[ix]
};
valueRange.Values.Add(oblist);
}
SpreadsheetsResource.ValuesResource.UpdateRequest update =
service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
for (int xy = 0; xy < 3; xy++)
{
try
{
UpdateValuesResponse result2 = update.Execute();
break;
}
catch (Exception ex)
{
if (ex.Message.IndexOf("USER-100s") >= 0)
{
Thread.Sleep(50000); // Sleep 50 * 3 seconds while clear limit 100s
}
else
{
break;
}
}
}
r++;
}
}
#endregion
}
}
}
if (spreadsheetId != "")
{
var service = new DriveService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
InsertPermission(service, spreadsheetId, null, "anyone", "reader");
}
}
static void Main(string[] args)
{
/*
* Set up API services
*/
UserCredential credential;
using (var stream =
new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
{
string credPath = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
credPath = Path.Combine(credPath, ".credentials/drive-dotnet-quickstart.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Google API credential file saved to: " + credPath);
}
// Create Google Sheets API service.
var sheetsService = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
// Create Drive API service.
var driveService = new DriveService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
/*
* USB Port Config
*/
var port = new SerialPort();
//Get user input
Console.Write("Enter port number: ");
String portNum = Console.ReadLine().Substring(0, 1);
try
{
port.PortName = "COM" + portNum;
port.BaudRate = 9600;
port.ReadTimeout = 500;
port.WriteTimeout = 500;
port.Open();
port.DiscardInBuffer();
Console.WriteLine("Opened serial communication on COM" + portNum);
}
catch (Exception)
{
Console.WriteLine("ERROR: Failed to open COM" + portNum);
}
var propertiesFileLocation = @"C:\Users\danie\Desktop\VFproperties.txt";
String machineSN = "";
String spreadsheetID = "";
String folderID = "1YDdzdk8XuOHjplu_OcLPgwEl9Gnjk9LX";
try
{
System.IO.StreamReader propertiesFile = new System.IO.StreamReader(propertiesFileLocation);
String propertiesLine1 = propertiesFile.ReadLine(); //Machine SN
String propertiesLine2 = propertiesFile.ReadLine(); //Current Spreadsheet ID
String propertiesLine3 = propertiesFile.ReadLine(); //Folder ID
propertiesFile.Close();
machineSN = propertiesLine1.Substring(propertiesLine1.IndexOf(':') + 2);
spreadsheetID = propertiesLine2.Substring(propertiesLine2.IndexOf(':') + 2);
folderID = propertiesLine3.Substring(propertiesLine2.IndexOf(':') + 2);
Console.WriteLine("Machine SN: " + machineSN);
Console.WriteLine("Spreadsheet ID: " + spreadsheetID);
Console.WriteLine("Folder ID: " + folderID);
}
catch (Exception)
{
Console.WriteLine("ERROR: Failed to locate or read VFproperties.txt, ensure this is the correct directory: " + propertiesFileLocation);
}
while (true)
{
if (port.IsOpen)
{
if (port.BytesToRead > 0)
{
String SN = "";
String tempA = "";
String tempW = "";
String humidity = "";
String RSC = "";
String LSC = "";
String watered = "";
String errorCode = "";
String dateTime = DateTime.Now.ToString();
String input = port.ReadLine();
if (input.Contains("Clear") || input.Contains("Start"))
{
Console.WriteLine(dateTime + " " + input);
}
else if (input.Contains("ERROR"))
{
errorCode = input.Substring(input.IndexOf(':') + 2);
/*
* Add data to spreadsheet
*/
String range = "Sheet1!A:I";
// How the input data should be interpreted.
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
// How the input data should be inserted.
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
Data.ValueRange requestBody = new Data.ValueRange();
requestBody.Range = range;
requestBody.MajorDimension = "ROWS";
var oblist = new List <object>()
{
dateTime, SN, tempA, tempW, humidity, RSC, LSC, watered, errorCode
};
requestBody.Values = new List <IList <object> > {
oblist
};
SpreadsheetsResource.ValuesResource.AppendRequest appendRequest = sheetsService.Spreadsheets.Values.Append(requestBody, spreadsheetID, range);
appendRequest.ValueInputOption = valueInputOption;
appendRequest.InsertDataOption = insertDataOption;
Data.AppendValuesResponse updateResponse = appendRequest.Execute();
String rangeReturn = JsonConvert.SerializeObject(updateResponse.Updates.UpdatedRange);
rangeReturn = rangeReturn.Substring(rangeReturn.IndexOf("!A") + 2);
rangeReturn = rangeReturn.Substring(0, rangeReturn.IndexOf(':'));
int i = Convert.ToInt32(rangeReturn); //row it was put into
/*
* Print data to console
*/
Console.WriteLine(dateTime + ", SN: " + SN + ", Temp A (c): " + tempA + ", Temp W (c): " + tempW + ", Humidity (%): " + humidity + ", RSC: " + RSC + ", LSC: " + LSC + ", Watered: " + watered + ", Error: " + errorCode);
if (i >= 10000)
{
/*
* Create new spreadsheet
*/
Data.Spreadsheet requestBody1 = new Data.Spreadsheet();
requestBody1.Properties = new SpreadsheetProperties();
requestBody1.Properties.Title = dateTime;
SpreadsheetsResource.CreateRequest request = sheetsService.Spreadsheets.Create(requestBody1);
Data.Spreadsheet response = request.Execute();
spreadsheetID = JsonConvert.SerializeObject(response.SpreadsheetId);
spreadsheetID = spreadsheetID.Substring(1, spreadsheetID.Length - 2);
Console.WriteLine(spreadsheetID);
/*
* Move spreadsheet to correct folder
*/
// Retrieve the existing parents to remove
var getRequest = driveService.Files.Get(spreadsheetID);
getRequest.Fields = "parents";
var file = getRequest.Execute();
var previousParents = String.Join(",", file.Parents);
// Move the file to the new folder
var updateRequest = driveService.Files.Update(new Google.Apis.Drive.v3.Data.File(), spreadsheetID);
updateRequest.Fields = "id, parents";
updateRequest.AddParents = folderID;
updateRequest.RemoveParents = previousParents;
file = updateRequest.Execute();
/*
* Add headers to spreadsheet
*/
oblist = new List <object>()
{
"Date/Time", "Serial Number", "Air Temp (c)", "Water Temp (c)", "Humidity (%)", "RSC", "LSC", "Watered", "Error"
};
requestBody.Values = new List <IList <object> > {
oblist
};
appendRequest = sheetsService.Spreadsheets.Values.Append(requestBody, spreadsheetID, range);
updateResponse = appendRequest.Execute();
/*
* Update VFproperties.txt with new ID's
*/
String[] lines = { "Machine Serial Number: " + machineSN, "Current Spreadsheet ID: " + spreadsheetID, "Google Drive Folder ID: " + folderID };
System.IO.File.WriteAllLines(propertiesFileLocation, lines);
}
}
else
{
SN = input.Substring(0, input.IndexOf(','));
input = input.Substring(input.IndexOf(',') + 1);
tempA = input.Substring(0, input.IndexOf(','));
input = input.Substring(input.IndexOf(',') + 1);
tempW = input.Substring(0, input.IndexOf(','));
input = input.Substring(input.IndexOf(',') + 1);
humidity = input.Substring(0, input.IndexOf(','));
input = input.Substring(input.IndexOf(',') + 1);
RSC = input.Substring(0, input.IndexOf(','));
input = input.Substring(input.IndexOf(',') + 1);
LSC = input.Substring(0, input.IndexOf(','));
input = input.Substring(input.IndexOf(',') + 1);
watered = input.Substring(0, 1);
/*
* Add data to spreadsheet
*/
String range = "Sheet1!A:I";
// How the input data should be interpreted.
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
// How the input data should be inserted.
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
Data.ValueRange requestBody = new Data.ValueRange();
requestBody.Range = range;
requestBody.MajorDimension = "ROWS";
var oblist = new List <object>()
{
dateTime, SN, tempA, tempW, humidity, RSC, LSC, watered, errorCode
};
requestBody.Values = new List <IList <object> > {
oblist
};
SpreadsheetsResource.ValuesResource.AppendRequest appendRequest = sheetsService.Spreadsheets.Values.Append(requestBody, spreadsheetID, range);
appendRequest.ValueInputOption = valueInputOption;
appendRequest.InsertDataOption = insertDataOption;
Data.AppendValuesResponse updateResponse = appendRequest.Execute();
String rangeReturn = JsonConvert.SerializeObject(updateResponse.Updates.UpdatedRange);
rangeReturn = rangeReturn.Substring(rangeReturn.IndexOf("!A") + 2);
rangeReturn = rangeReturn.Substring(0, rangeReturn.IndexOf(':'));
int i = Convert.ToInt32(rangeReturn); //row it was put into
/*
* Print data to console
*/
Console.WriteLine(dateTime + ", SN: " + SN + ", Temp A (c): " + tempA + ", Temp W (c): " + tempW + ", Humidity (%): " + humidity + ", RSC: " + RSC + ", LSC: " + LSC + ", Watered: " + watered + ", Error: " + errorCode);
if (i >= 10000)
{
/*
* Create new spreadsheet
*/
Data.Spreadsheet requestBody1 = new Data.Spreadsheet();
requestBody1.Properties = new SpreadsheetProperties();
requestBody1.Properties.Title = dateTime;
SpreadsheetsResource.CreateRequest request = sheetsService.Spreadsheets.Create(requestBody1);
Data.Spreadsheet response = request.Execute();
spreadsheetID = JsonConvert.SerializeObject(response.SpreadsheetId);
spreadsheetID = spreadsheetID.Substring(1, spreadsheetID.Length - 2);
Console.WriteLine(spreadsheetID);
/*
* Move spreadsheet to correct folder
*/
// Retrieve the existing parents to remove
var getRequest = driveService.Files.Get(spreadsheetID);
getRequest.Fields = "parents";
var file = getRequest.Execute();
var previousParents = String.Join(",", file.Parents);
// Move the file to the new folder
var updateRequest = driveService.Files.Update(new Google.Apis.Drive.v3.Data.File(), spreadsheetID);
updateRequest.Fields = "id, parents";
updateRequest.AddParents = folderID;
updateRequest.RemoveParents = previousParents;
file = updateRequest.Execute();
/*
* Add headers to spreadsheet
*/
oblist = new List <object>()
{
"Date/Time", "Serial Number", "Air Temp (c)", "Water Temp (c)", "Humidity (%)", "RSC", "LSC", "Watered", "Error"
};
requestBody.Values = new List <IList <object> > {
oblist
};
appendRequest = sheetsService.Spreadsheets.Values.Append(requestBody, spreadsheetID, range);
updateResponse = appendRequest.Execute();
/*
* Update VFproperties.txt with new ID's
*/
String[] lines = { "Machine Serial Number: " + machineSN, "Current Spreadsheet ID: " + spreadsheetID, "Google Drive Folder ID: " + folderID };
System.IO.File.WriteAllLines(propertiesFileLocation, lines);
}
}
}
}
}
port.Close();
}
