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!");
}
public IActionResult SpreadAppend(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "sa_spread_append")] HttpRequest req, ILogger log)
{
_sheetsService = ConnectSpreadSheet();
string range = "シート1!B2";
var valueInputOption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum) 2;
var insertDataOption = (SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum) 1;
Data.ValueRange requestBody = new Data.ValueRange();
List <IList <object> > data = new List <IList <object> >();
data.Add(GetAppendList());
data.Add(GetAppendList());
data.Add(GetAppendList());
data.Add(GetAppendList());
data.Add(GetAppendList());
requestBody.Values = data;
SpreadsheetsResource.ValuesResource.AppendRequest request = _sheetsService.Spreadsheets.Values.Append(requestBody, _editSpreadsheetId, range);
request.ValueInputOption = valueInputOption;
request.InsertDataOption = insertDataOption;
Data.AppendValuesResponse response = request.Execute();
return(new ObjectResult(JsonConvert.SerializeObject(response)));
}
public void UpdateWholesalerQueriesSheet(WholesalersRequestModel request,
List <StateTaxRegistrationInternal> internals,
string feinfileurl)
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
var range = string.Empty;
range = @"WholesalerEnquiriesOne!A:CZ";
var valueRange = new ValueRange();
var oblist = new List <object>();
oblist.Add(DateTime.Now.ToShortDateString());
oblist.Add(String.IsNullOrEmpty(request.BusinessName) ? "": request.BusinessName);
oblist.Add(String.IsNullOrEmpty(request.ContactName) ? "": request.ContactName);
oblist.Add(String.IsNullOrEmpty(request.AddressLine1) ? "": request.AddressLine1);
oblist.Add(String.IsNullOrEmpty(request.AddressLine2) ? "": request.AddressLine2);
oblist.Add(String.IsNullOrEmpty(request.City) ? "": request.City);
oblist.Add(String.IsNullOrEmpty(request.State) ? "": request.State);
oblist.Add(String.IsNullOrEmpty(request.ZipCode) ? "": request.ZipCode);
oblist.Add(String.IsNullOrEmpty(request.BusinessPhoneNumber) ? "": request.BusinessPhoneNumber);
oblist.Add(String.IsNullOrEmpty(request.EmailAddress) ? "": request.EmailAddress);
oblist.Add(String.IsNullOrEmpty(request.ContactName) ? "": request.ContactName);
oblist.Add(String.IsNullOrEmpty(request.Subject) ? "": request.Subject);
oblist.Add(String.IsNullOrEmpty(request.Message) ? "": request.Message);
oblist.Add(String.IsNullOrEmpty(request.FederalBusinessTaxId) ? "": request.FederalBusinessTaxId);
oblist.Add(String.IsNullOrEmpty(feinfileurl) ? "": feinfileurl);
foreach (var item in internals)
{
oblist.Add(String.IsNullOrEmpty(item.StateSalesTaxId) ? "": item.StateSalesTaxId);
oblist.Add(String.IsNullOrEmpty(item.StateSelected) ? "": item.StateSelected);
oblist.Add(String.IsNullOrEmpty(item.UploadedUrl) ? "": item.UploadedUrl);
}
valueRange.Values = new List <IList <object> > {
oblist
};
Google.Apis.Sheets.v4.Data.ValueRange requestBody =
new Google.Apis.Sheets.v4.Data.ValueRange();
var appendRequest =
sheetsService.Spreadsheets.Values.Append(valueRange, _registrationsheetid, range);
appendRequest.ValueInputOption =
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Google.Apis.Sheets.v4.Data.AppendValuesResponse response = appendRequest.Execute();
}
private void PostToGoogle(IList <IList <object> > data)
{
// The ID of the spreadsheet to update.
string spreadsheetId = "14CQv-KsLjUweeZS9Am-dftJBlaGAiFGpwRJb8CK-tJQ";
// 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 = "GameData!A1:Y";
// How the input data should be interpreted.
GAppendRequest.ValueInputOptionEnum valueInputOption = GAppendRequest.ValueInputOptionEnum.USERENTERED;
// How the input data should be inserted.
GAppendRequest.InsertDataOptionEnum insertDataOption = GAppendRequest.InsertDataOptionEnum.INSERTROWS;
// TODO: Assign values to desired properties of `requestBody`:
GData.ValueRange requestBody = new GData.ValueRange();
requestBody.Values = data;
requestBody.MajorDimension = "COLUMNS";
GAppendRequest 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:
GData.AppendValuesResponse response = request.Execute();
// GData.AppendValuesResponse response = await request.ExecuteAsync();
// TODO: Change code below to process the `response` object:
Console.WriteLine(JsonConvert.SerializeObject(response));
}
public void UpdateQueriesSheet(string name,
string emailaddress,
string phonenumber,
string subject,
string message,
string requesttype)
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
var range = string.Empty;
if (requesttype.Equals("Wholesalers"))
{
range = @"WholesalerEnquiries!A:F";
}
else
{
range = @"CustomerQueries!A:F";
}
var valueRange = new ValueRange();
var oblist = new List <object>()
{
String.IsNullOrEmpty(name) ? "": name,
String.IsNullOrEmpty(emailaddress)? "" : emailaddress,
String.IsNullOrEmpty(phonenumber)? "" : phonenumber,
String.IsNullOrEmpty(subject)? "" : subject,
String.IsNullOrEmpty(message)? "" : message,
DateTime.Now.ToShortDateString()
};
valueRange.Values = new List <IList <object> > {
oblist
};
Google.Apis.Sheets.v4.Data.ValueRange requestBody =
new Google.Apis.Sheets.v4.Data.ValueRange();
var appendRequest =
sheetsService.Spreadsheets.Values.Append(valueRange, _registrationsheetid, range);
appendRequest.ValueInputOption =
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Google.Apis.Sheets.v4.Data.AppendValuesResponse response = appendRequest.Execute();
}
private void SendToGoogleSheets(object sender, EventArgs e)
{
if (_sheetsService == null)
{
// 종료
MessageBox.Show("Disconnect google sheets");
Close();
return;
}
try
{
// 구글 스프레드 시트에 쓰기
string appendRange = "Sheet1!A1:ZZ";
ValueRange appendValueRange = new ValueRange {
MajorDimension = "ROWS", Values = new List <IList <object> >()
};
SpreadsheetsResource.ValuesResource.AppendRequest appendRequest = _sheetsService.Spreadsheets.Values.Append(appendValueRange, _spreadSheetsId, appendRange);
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
appendRequest.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
appendValueRange.Values.Clear();
var dataList = new List <object>();
appendValueRange.Values.Add(dataList);
// 수신시간
dataList.Add(dateTimePicker1.Value.ToShortDateString() + " " + dateTimePicker1.Value.ToShortTimeString());
// 수신자
dataList.Add(textBox1.Text);
// 발신자
dataList.Add(textBox2.Text);
// 제목
dataList.Add(textBox3.Text);
// 내용
dataList.Add(textBox4.Text);
Google.Apis.Sheets.v4.Data.AppendValuesResponse appendResponse = appendRequest.Execute();
Console.WriteLine(JsonConvert.SerializeObject(appendResponse));
//MessageBox.Show("Success");
}
catch (Exception)
{
throw;
}
Close();
}
public static void Main(string[] args)
{
string x = "0";
while (x != "6")
{
Console.WriteLine("Press: \n1. Reading\n2. Appending\n3. Updating\n4. Clearing\n5. Add/Update Headers\n6. Exit");
x = Console.ReadLine();
switch (x)
{
case "1":
Data.ValueRange readResponse = ReadSpreadsheet(spreadsheetId, range, service);
if (readResponse.Values == null)
{
Console.WriteLine("Sorry! No data read from this range.");
break;
}
Console.WriteLine("Read " + readResponse.Values.Count + " rows.");
break;
case "2":
Data.AppendValuesResponse appendResponse = AppendSpreadsheet(spreadsheetId, range, service, GetDataAsObject());
Console.WriteLine("Appended! Updated " + appendResponse.Updates.UpdatedCells + " cells.");
break;
case "3":
Data.UpdateValuesResponse updateResponse = UpdateSpreadsheet(spreadsheetId, range, service, GetDataAsObject());
Console.WriteLine("Updated " + updateResponse.UpdatedRows + " rows and " + updateResponse.UpdatedColumns + " columns.");
break;
case "4":
Data.ClearValuesResponse clearResponse = Clearspreadsheet(spreadsheetId, range, service);
Console.WriteLine("Cleared range: " + clearResponse.ClearedRange);
break;
case "5":
UpdateHeadersInSpreadsheet(spreadsheetId, service, GetHeaders());
Console.WriteLine("Updated the headers!");
break;
default:
Console.WriteLine("Please enter a valid key.");
break;
}
;
}
}
// 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();
}
public bool Search(string productid)
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
var range = @"ProductIdLookup!A1";
var valueRange = new ValueRange();
string searchstring = "=MATCH(\"#-#$\",ProductIds!A:A, 0)";
searchstring = searchstring.Replace("#-#$", productid.Trim());
var oblist = new List <object>()
{
searchstring
};
valueRange.Values = new List <IList <object> > {
oblist
};
Google.Apis.Sheets.v4.Data.ValueRange requestBody =
new Google.Apis.Sheets.v4.Data.ValueRange();
var appendRequest =
sheetsService.Spreadsheets.Values.Append(valueRange, _registrationsheetid, range);
appendRequest.ValueInputOption =
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Google.Apis.Sheets.v4.Data.AppendValuesResponse response =
appendRequest.Execute();
string query = response.Updates.UpdatedRange;
SpreadsheetsResource.ValuesResource.GetRequest getrequest =
sheetsService.Spreadsheets.Values.Get(_registrationsheetid, query);
ValueRange r = getrequest.Execute();
object s = r.Values[0][0];
return(!(s as string).Contains("#"));
}
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 static Data.AppendValuesResponse AppendSpreadsheet(string spreadsheetId, string range, SheetsService service, MyData dataObject)
{
Data.ValueRange requestBody = new Data.ValueRange();
//Request Body consists of data to be appended
requestBody.Range = range;
requestBody.MajorDimension = "ROWS";
requestBody.Values = ConvertObjectToList(dataObject);
//Update Headers w.r.t the dataObject
UpdateHeadersInSpreadsheet(spreadsheetId, service, GetHeaders());
//Create append request
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(requestBody, spreadsheetId, range);
request.ValueInputOption = (SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum) 2;
Console.WriteLine("Appending to spreadsheet...");
//Execute the request
Data.AppendValuesResponse response = request.Execute();
return(response);
}
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();
}
public void UpdateWarrantyClaimSheet(string SerialNumber,
string PurchaseDate,
string FirstName,
string LastName,
string DateofBirth,
string EmailAddress,
string AddressLine1,
string AddressLine2,
string City,
string State,
string ZipCode,
string Country,
string ClaimReason,
string ReceiptLink,
string SerialNumberLink,
string DateSubmitted)
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
var range = @"WarrantyClaims!A:P";
var valueRange = new ValueRange();
var oblist = new List <object>()
{
String.IsNullOrEmpty(SerialNumber) ? "": SerialNumber,
String.IsNullOrEmpty(PurchaseDate)? "" : PurchaseDate,
String.IsNullOrEmpty(FirstName)? "" : FirstName,
String.IsNullOrEmpty(LastName)? "" : LastName,
String.IsNullOrEmpty(DateofBirth)? "" : DateofBirth,
String.IsNullOrEmpty(EmailAddress)? "" : EmailAddress,
String.IsNullOrEmpty(AddressLine1)? "" : AddressLine1,
String.IsNullOrEmpty(AddressLine2)? "" : AddressLine2,
String.IsNullOrEmpty(City)? "" : City,
String.IsNullOrEmpty(State)? "" : State,
String.IsNullOrEmpty(ZipCode)? "" : ZipCode,
String.IsNullOrEmpty(Country)? "" : Country,
ClaimReason,
ReceiptLink,
SerialNumberLink,
DateSubmitted
};
// { "1A3D5F",
// "12/31/2020",
// "Robert",
// "Jones",
// "01/01/1977",
// "*****@*****.**",
// "Rocket Man Lane",
// "Apt 1",
// "Timbuktu",
// "KY",
// "40521",
// "USA",
// "https://docs.google.com/spreadsheets/d/1FgKDsP4LCZDXviuGIYq4Wge0cTr-V8xQv1HIuEso8qQ"};
valueRange.Values = new List <IList <object> > {
oblist
};
Google.Apis.Sheets.v4.Data.ValueRange requestBody =
new Google.Apis.Sheets.v4.Data.ValueRange();
var appendRequest =
sheetsService.Spreadsheets.Values.Append(valueRange, _warrantyclaimsheetid, range);
appendRequest.ValueInputOption =
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Google.Apis.Sheets.v4.Data.AppendValuesResponse response = appendRequest.Execute();
}
public void UploadACHPaymentRequest(string companyname,
string contactname,
string bankname,
string accounttype,
string bankroutingnumber,
string routingnumberverified,
string bankaccountnumber,
string emailaddress,
string addressline1,
string addressline2,
string city,
string state,
string printedname,
string titlename,
string fein,
string datesigned,
string zipcode,
string voidchecklink,
string comments,
string source)
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
var range = @"ACHPaymentUpload!A:S";
var valueRange = new ValueRange();
var oblist = new List <object>()
{
String.IsNullOrEmpty(companyname) ? "": companyname,
String.IsNullOrEmpty(contactname)? "" : contactname,
String.IsNullOrEmpty(bankname)? "" : bankname,
String.IsNullOrEmpty(accounttype)? "" : accounttype,
String.IsNullOrEmpty(bankroutingnumber)? "" : bankroutingnumber,
String.IsNullOrEmpty(bankaccountnumber)? "" : bankaccountnumber,
String.IsNullOrEmpty(emailaddress)? "" : emailaddress,
String.IsNullOrEmpty(addressline1)? "" : addressline1,
String.IsNullOrEmpty(addressline2)? "" : addressline2,
String.IsNullOrEmpty(city)? "" : city,
String.IsNullOrEmpty(state)? "" : state,
String.IsNullOrEmpty(zipcode)? "" : zipcode,
String.IsNullOrEmpty(printedname)? "" : printedname,
String.IsNullOrEmpty(titlename)? "" : titlename,
String.IsNullOrEmpty(fein)? "" : fein,
String.IsNullOrEmpty(datesigned)? "" : datesigned,
String.IsNullOrEmpty(voidchecklink)? "" : voidchecklink,
String.IsNullOrEmpty(comments)? "" : comments,
String.IsNullOrEmpty(source)? "" : source
};
valueRange.Values = new List <IList <object> > {
oblist
};
Google.Apis.Sheets.v4.Data.ValueRange requestBody =
new Google.Apis.Sheets.v4.Data.ValueRange();
var appendRequest =
sheetsService.Spreadsheets.Values.Append(valueRange, _registrationsheetid, range);
appendRequest.ValueInputOption =
SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Google.Apis.Sheets.v4.Data.AppendValuesResponse response = appendRequest.Execute();
}
