public static async Task WriteAsync(List <IList <Object> > values, string spreadSheetId, string fileName)
{
var sheetService = GetSheetsService();
var valueRange = new ValueRange()
{
MajorDimension = "ROWS",
Values = values
};
// How the input data should be interpreted.
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED); // TODO: Update placeholder value.
// How the input data should be inserted.
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = (SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS); // TODO: Update placeholder value.
SpreadsheetsResource.ValuesResource.AppendRequest request = sheetService.Spreadsheets.Values.Append(valueRange, spreadSheetId, "A:A");
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
AppendValuesResponse response = await request.ExecuteAsync();
if (response?.Updates?.UpdatedRows != null)
{
Console.WriteLine($"{response.Updates.UpdatedRows} Rows Updated for Sheet: {fileName}");
}
else
{
Console.WriteLine($"Sheet : {fileName} is up to date");
}
}
public async void AddToDatabase(List <string> data, String spreadsheetId, string type, string Class)
{
// The A1 notation of a range to search for a logical table of data.
// Values will be appended after the last row of the table.
string range = type + "!A:G";
IList <IList <object> > val = new List <IList <object> >();
ValueRange requestBody = new ValueRange()
{
Values = val
};
requestBody.Values.Add(data.Select(x => (object)x).ToList());
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum) 2;
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = (SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum) 1;
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(requestBody, spreadsheetId, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
AppendValuesResponse response = await request.ExecuteAsync();
RefreshDatabase(type, Class);
}
/// <summary>
/// The save numbers to sheet.
/// </summary>
/// <param name="lotteryModels">
/// The lottery models.
/// </param>
/// <returns>
/// The <see cref="AppendValuesResponse"/>.
/// </returns>
public AppendValuesResponse SaveNumbersToSheet(List <LotteryModel> lotteryModels)
{
IList <IList <object> > values = new List <IList <object> >();
foreach (var lotteryModel in lotteryModels)
{
values.Add(GetLotteryModelAsStrList(lotteryModel).Cast <object>().ToList());
}
// How the input data should be interpreted.
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED; // TODO: Update placeholder value.
// How the input data should be inserted.
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.OVERWRITE; // TODO: Update placeholder value.
// TODO: Assign values to desired properties of `requestBody`:
var requestBody = new ValueRange()
{
Values = values
};
var request = service.Spreadsheets.Values.Append(requestBody, spreadsheetId, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
var response = request.Execute();
// TODO: Change code below to process the `response` object:
Console.WriteLine(JsonConvert.SerializeObject(response));
return(response);
}
public async Task Add(string name)
{
if (Utils.GetRowByDiscord(Program.context.User.ToString()) != 0)
{
await ReplyAsync("You have already added yourself."); return;
}
IList <IList <object> > data = new List <IList <object> >()
{
new List <object> {
Utils.UpperCaseIt(name.ToLower()), Program.context.User.ToString()
}
};
range = "B5";
requestbody = new Data.ValueRange();
requestbody.Values = data;
SpreadsheetsResource.ValuesResource.AppendRequest r = service.Spreadsheets.Values.Append(requestbody, sheetid, range);
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueinputoption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED);
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertdataoption = (SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS);
r.ValueInputOption = valueinputoption;
r.InsertDataOption = insertdataoption;
r.AccessToken = credential.Token.AccessToken;
Data.AppendValuesResponse response = await r.ExecuteAsync();
await ReplyAsync("Successfully added!");
}
// Button 1 "Confirm"
private void button1_Click(object sender, EventArgs e) //TODO: have this send input to spreadsheet
{
NotMain();
string player = txt_playerName.Text as string;
string name = txt_characterName.Text as string;
string race = txt_race.Text as string;
int str = Convert.ToInt32(txt_strength.Text);
int dex = Convert.ToInt32(txt_dexterity.Text);
int intellegence = Convert.ToInt32(txt_intelligence.Text);
int level = 1;
int exp = 0;
characters.Add(name, new Character(name, player, race, str, dex, intellegence, level, exp));
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum) 0; // TODO: Update placeholder value.
// How the input data should be inserted.
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = (SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum) 0; // TODO: Update placeholder value.
// TODO: Assign values to desired properties of `requestBody`:
Data.ValueRange requestBody = new Data.ValueRange();
var characterResponses = new List <IList <Object> >();
foreach (var character in characters)
{
var characterResponse = new List <object>();
characterResponse.Add(character.Value.charName);
characterResponse.Add(character.Value.plyrName);
characterResponse.Add(character.Value.strength.ToString());
characterResponse.Add(character.Value.intelligence.ToString());
characterResponse.Add(character.Value.dexterity.ToString());
characterResponse.Add(character.Value.level.ToString());
characterResponse.Add(character.Value.xp.ToString());
characterResponses.Add(characterResponse);
}
requestBody.Values = characterResponses;
SpreadsheetsResource.ValuesResource.AppendRequest request = sheetsService.Spreadsheets.Values.Append(requestBody, spreadsheetId, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Data.AppendValuesResponse response = request.Execute();
}
private async Task LogToTable(string listname, string message, string user = null, MessageType?messageType = null)
{
var service = await _sheetServiceProvider.GetService();
var range = $"{listname}!A:D";
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption
= SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption
= SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
ValueRange valueRange = new ValueRange();
var oblist = new List <object>();
if (messageType != null)
{
oblist.Add(
messageType == MessageType.Outgoing ? "Исходящее" :
messageType == MessageType.Incoming ? "Входящее"
: "Системное"
);
}
oblist.AddRange(new List <object>()
{
string.Format(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")), message, user
});
valueRange.Values = new List <IList <object> > {
oblist
};
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(valueRange, _configService.Config.SpreadsheetLog.Id, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
request.ResponseDateTimeRenderOption = SpreadsheetsResource.ValuesResource.AppendRequest.ResponseDateTimeRenderOptionEnum.FORMATTEDSTRING;
AppendValuesResponse response = await request.ExecuteAsync();
}
public static async Task <IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = null)] HttpRequest req,
ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a request.");
string email = req.Query["email"].ToString().ToLower();
string answer = req.Query["answer"].ToString().ToLower() == "yes" ? "Yes" : "No";
//some of the following code provided by Google APIs https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = GetCredential(),
ApplicationName = ApplicationName,
});
//Append our email and answer to the request body of values to add
ValueRange requestBody = new ValueRange();
var values = new[] { new { email, answer } };
requestBody.Values.Add(values);
//Create our request and set up the input options
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(requestBody, sheetId, range);
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
//Obtain our response
AppendValuesResponse response = await request.ExecuteAsync();
//If everything is good we should have a valid response
return(response != null
? (ActionResult) new OkObjectResult(response)
: new BadRequestObjectResult("Something went wrong."));
}
public async Task LogSendedList(IEnumerable <SendedMessage> messages)
{
try {
var service = await _sheetServiceProvider.GetService();
var range = $"{_configService.Config.SpreadsheetLog.Messages}!A:D";
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption
= SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption
= SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
ValueRange valueRange = new ValueRange();
var listOfObj = messages.Select(x => (IList <object>) new List <object>()
{
"Исходящее", string.Format(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss")), x.Message, x.To
}).ToList();
valueRange.Values = listOfObj;//new List<IList<object>> { listOfObj };
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(valueRange, _configService.Config.SpreadsheetLog.Id, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
request.ResponseDateTimeRenderOption = SpreadsheetsResource.ValuesResource.AppendRequest.ResponseDateTimeRenderOptionEnum.FORMATTEDSTRING;
AppendValuesResponse response = request.Execute();
}
catch (Exception err)
{
_toFileLogger.LogError($"CANNOT LOG TO GOOGLE TABLE SENDED LIST. ERROR: {err.Message}");
}
}
public static void AppendNewSongs(List <SpotifyInfo> listSongs, int countRows)
{
try
{
var service = UserCredential();
IList <SpotifyInfo> dataList = listSongs;
List <Google.Apis.Sheets.v4.Data.ValueRange> data = new List <Google.Apis.Sheets.v4.Data.ValueRange>();
ValueRange valueDataRange = new ValueRange()
{
MajorDimension = "ROWS"
};
valueDataRange.Values = new List <IList <object> >();
valueDataRange.Range = range;
for (int i = 0; i < dataList.Count; i++)
{
IList <object> list = new List <object> {
countRows + (i + 1), dataList[i].TrackTitle, dataList[i].Code,
dataList[i].Artists, dataList[i].LinkSpotify, dataList[i].Genres, dataList[i].Country, dataList[i].ReleaseDate, dataList[i].Popularity, dataList[i].StreamCount
};
valueDataRange.Values.Add(list);
}
data.Add(valueDataRange);
// How the input data should be interpreted.
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum) 1; // TODO: Update placeholder value.
// How the input data should be inserted.
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = (SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum) 1; // TODO: Update placeholder value.
// TODO: Assign values to desired properties of `requestBody`:
Google.Apis.Sheets.v4.Data.ValueRange requestBody = data[0];
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(requestBody, spreadsheetId, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Google.Apis.Sheets.v4.Data.AppendValuesResponse response = request.Execute();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public string EditData(List <Messages> msgList)
{
log.Info("Editing sheet data.");
string resp = "";
// Column Names
IList <Object> header = new List <Object>();
header.Add("Timestamp");
header.Add("Author");
header.Add("Message");
IList <IList <Object> > headerValues = new List <IList <Object> >();
headerValues.Add(header);
string range = _sheetname + "!A1:Y";
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum valueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum insertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
ValueRange requestBody = new ValueRange();
requestBody.Range = range;
requestBody.Values = headerValues;
SpreadsheetsResource.ValuesResource.AppendRequest request = _sheetsService.Spreadsheets.Values.Append(requestBody, _spreadsheetId, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
AppendValuesResponse response = request.Execute();
// Data.AppendValuesResponse response = await request.ExecuteAsync();
resp = JsonConvert.SerializeObject(response);
foreach (Messages m in msgList)
{
range = _sheetname + "!A2:Y";
IList <Object> obj = new List <Object>();
obj.Add(m.Timestamp);
obj.Add(m.Author);
obj.Add(m.MessageContent);
IList <IList <Object> > values = new List <IList <Object> >();
values.Add(obj);
valueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
insertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
requestBody = new ValueRange();
requestBody.Range = range;
requestBody.Values = values;
request = _sheetsService.Spreadsheets.Values.Append(requestBody, _spreadsheetId, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
response = request.Execute();
// Data.AppendValuesResponse response = await request.ExecuteAsync();
resp = JsonConvert.SerializeObject(response);
}
return(resp);
}
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();
}
