// get table and list in google sheets
public Data.Spreadsheet CreateSpreadsheet(SheetsService service, string tableName, List <Server> servers)
{
Data.Spreadsheet requestBody = new Data.Spreadsheet();
List <Data.Sheet> list = new List <Data.Sheet>();
for (int i = 0; i < servers.Count; i++)
{
list.Add(new Data.Sheet
{
Properties = new Data.SheetProperties
{
Title = servers[0].name
}
});
}
requestBody.Sheets = list;
requestBody.Properties = new Data.SpreadsheetProperties
{
Title = tableName
};
SpreadsheetsResource.CreateRequest request = service.Spreadsheets.Create(requestBody);
Data.Spreadsheet response = request.Execute();
return(response);
}
static void AddSheetsToSheetTitle(string SheetTitle, IList <Sheet> Sheets, string CellRange)
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = GetCredential(),
ApplicationName = ApplicationName,
});
string SheetId = GetSheetIdByTitle(SheetTitle);
//Copys the current sheet data and adds to it
SpreadsheetsResource.GetRequest getRequest = sheetsService.Spreadsheets.Get(SheetId);
Spreadsheet requestBody = getRequest.Execute();
requestBody.SpreadsheetId = null;
//Update values in requestBody cells and sheets
List <SpreadsheetsResource.ValuesResource.GetRequest> valueRequests = new List <SpreadsheetsResource.ValuesResource.GetRequest>();
for (int i = 0; i < requestBody.Sheets.Count; i++)
{
valueRequests.Add(sheetsService.Spreadsheets.Values.Get(SheetId, requestBody.Sheets[i].Properties.Title + "!" + CellRange));
}
//execute all requests
List <ValueRange> responses = new List <ValueRange>();
for (int i = 0; i < valueRequests.Count; i++)
{
responses.Add(valueRequests[i].Execute());
}
//Add sheets to requestBody
for (int i = 0; i < Sheets.Count; i++)
{
requestBody.Sheets.Add(Sheets[i]);
}
//Overwrite current sheet, delete first sheet
SpreadsheetsResource.CreateRequest createRequest = sheetsService.Spreadsheets.Create(requestBody);
//delete sheet
DeleteFileByTitle(SheetTitle);
createRequest.Execute();
//Update sheets and cells
for (int i = 0; i < responses.Count; i++)
{
SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest =
sheetsService.Spreadsheets.Values.Update(responses[i], GetSheetIdByTitle(SheetTitle), requestBody.Sheets[i].Properties.Title + "!" + CellRange);
updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
updateRequest.Execute();
}
}
public void CreateSheet(string sheetName)
{
Spreadsheet spreadsheet = new Spreadsheet();
spreadsheet.Properties = new SpreadsheetProperties()
{
Title = sheetName
};
SpreadsheetsResource.CreateRequest createRequest = Service.Spreadsheets.Create(spreadsheet);
var response = createRequest.Execute();
if (response == null || response.SpreadsheetId == null)
{
throw new Exception("SpreadSheet creation failed");
}
}
//the main procedure
public void CreateSheet(int N)
{
//get credentials
service = cred.GetCredential();
//create a spreadsheet
Spreadsheet requestBody = new Spreadsheet();
SpreadsheetsResource.CreateRequest request = service.Spreadsheets.Create(requestBody);
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Spreadsheet response = request.Execute();
// Data.Spreadsheet response = await request.ExecuteAsync();
// TODO: Change code below to process the `response` object:
JsonConvert.SerializeObject(response);
//get spreadsheet id
sheetID = response.SpreadsheetId;
//get a hyperlink for user to use
url = response.SpreadsheetUrl;
}
/// <summary>
/// creates a new Google Sheet and returns the ID
/// </summary>
/// <param name="SheetName"></param>
/// <returns></returns>
public static String 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();
return(response.SpreadsheetId);
}
public SpreadsheetClass CreateSheet(SpreadsheetClass sheetEntities)
{
Spreadsheet spreadsheet = new Spreadsheet();
spreadsheet.Properties = new SpreadsheetProperties();
spreadsheet.Properties.Title = "Google Sheets Exm";
List <Sheet> sheets = new List <Sheet>();
foreach (GoogleSheet item in sheetEntities.Servers)
{
Sheet sheet = new Sheet();
sheet.Properties = new SheetProperties();
sheet.Properties.Title = item.serverName;
sheets.Add(sheet);
}
spreadsheet.Sheets = sheets;
SpreadsheetsResource.CreateRequest request = this.service.Spreadsheets.Create(spreadsheet);
var x = request.Execute();
sheetEntities.SpreadsheetId = x.SpreadsheetId;
return(sheetEntities);
}
//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));
}
//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));
}
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));
}
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();
}