/// <summary>
/// Writes a two dimensional array of data to the sheet.
/// </summary>
/// <param name="spreadsheetId">The id of the spreadsheet to be read. docs.google.com/spreadsheets/d/[SpreadsheetIdIsHere]/edit#gid=0</param>
/// <param name="rangeGet">The range to read data from. format: "sheetname!A1:E32"</param>
/// <param name="readByColumn">If true data is written by column, if false data is written by row</param>
/// <returns></returns>
public static bool WriteDataToSheet(string spreadsheetId, string rangeWrite, IList <IList <object> > dataToWrite, Dimension dimension = Dimension.Columns)
{
if (m_shouldRun == false)
{
return(false);
}
if (dimension >= Dimension.Count)
{
throw new ArgumentOutOfRangeException(m_errorArgumentOutOfRange);
}
ValueRange values = new ValueRange();
values.MajorDimension = m_dimensions[(int)dimension];
values.Range = rangeWrite;
values.Values = dataToWrite;
SpreadsheetsResource.ValuesResource.UpdateRequest requestUpdate = TCOSheetsInterface.service.Spreadsheets.Values.Update(values, spreadsheetId, values.Range);
requestUpdate.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
UpdateValuesResponse result = requestUpdate.Execute();
return(true);
}
// UpdateSheetAsync(): Will update information for specific sheet
// [param]sheetName: the name for the specific sheet the user wants from the spreadsheet file
// [param]startRange: specific cell number where reading will start
// [param]finishRange: specific cell number where reading will end
// [param]values: the sheet will be updated with these values
public async Task <int?> UpdateSheetAsync(string sheetName, string startRange, string finishRange, List <List <string> > values)
{
string range = "'" + sheetName + "'!" + startRange + ":" + finishRange;
var valueRange = new ValueRange();
//We need a IList<IList<object>> object to send the data. This loop is to convert [param]values into that object
IList <IList <object> > valuesObject = new List <IList <object> >();
foreach (var rowValue in values)
{
var rowObject = new List <object>();
foreach (var columnValue in rowValue)
{
rowObject.Add(columnValue);
}
valuesObject.Add(rowObject);
}
valueRange.Values = valuesObject;
SpreadsheetsResource.ValuesResource.UpdateRequest request = Service.Spreadsheets.Values.Update(valueRange, SpreadsheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
UpdateValuesResponse response = await request.ExecuteAsync();
return(response.UpdatedCells);
}
private void updateSheets()
{
toggleAllButtons();
if (needsUpdate)
{
string range1 = "Sheet1!A2:A";
string range2 = "Sheet1!B2:B";
clearColumn(range1);
clearColumn(range2);
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";
var oblist = updatedNames.Keys.Cast <object>().ToList();
valueRange.Values = new List <IList <object> > {
oblist
};
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadSheetId, range1);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
update.Execute();
oblist = updatedNames.Values.Cast <object>().ToList();
valueRange.Values = new List <IList <object> > {
oblist
};
update = service.Spreadsheets.Values.Update(valueRange, spreadSheetId, range2);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
update.Execute();
}
toggleAllButtons();
}
/// <summary>
/// Updates a range of data in a sheet.
/// </summary>
/// <param name="sheetsService"></param>
/// <param name="sheetId"></param>
/// <param name="range"></param>
/// <param name="valueRange"></param>
/// <returns></returns>
public UpdateValuesResponse UpdateSheetDataRange(SheetsService sheetsService, string sheetId, string range, ValueRange valueRange)
{
// Update the cell
SpreadsheetsResource.ValuesResource.UpdateRequest update = sheetsService.Spreadsheets.Values.Update(valueRange, sheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
return(update.Execute());
}
public async Task iLvl(string data)
{
bool valid = true;
if (data.Length > 3 || !data.All(char.IsDigit))
{
valid = false;
}
if (valid == false)
{
await ReplyAsync("Invalid parameter: **" + data + "**. Please type !halp to see a list of supported parameters."); return;
}
if (Utils.GetRowByDiscord(Program.context.User.ToString()) == 0)
{
await ReplyAsync("No user found, be sure you've added yourself with the !add *<Player Name>* command"); return;
}
range = "E" + Utils.GetRowByDiscord(Program.context.User.ToString());
Console.WriteLine(range);
requestbody = new Data.ValueRange();
requestbody.MajorDimension = "ROWS";
var info = new List <object>()
{
data
};
requestbody.Values = new List <IList <object> > {
info
};
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(requestbody, sheetid, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();
await ReplyAsync("Your item level has been set to: **" + data + "**.");
}
protected override Task ExecuteAsync(AsyncCodeActivityContext context, SheetsService sheetService)
{
var includeHeaders = IncludeHeaders;
var sheet = SheetName.Get(context);
var startingCell = StartingCell.Get(context);
var dataTable = DataTable.Get(context);
string cellToPassToService;
if (string.IsNullOrWhiteSpace(sheet))
{
cellToPassToService = startingCell;
}
else
{
cellToPassToService = string.Format("{0}!{1}", sheet, startingCell);
}
return(Task.Factory.StartNew <object>(() =>
{
ValueRange requestBody = CreateRequestBodyWithValues(dataTable, includeHeaders);
SpreadsheetsResource.ValuesResource.UpdateRequest request =
sheetService.Spreadsheets.Values.Update(requestBody, SpreadsheetId, cellToPassToService);
//request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
var response = request.Execute();
return response;
}));
}
private void UpdateEntry(string SpreadSheet, List <object> objectArray)
{
ValueRange values = GetValues(SpreadSheet);
int i = 1;
bool hasBeenSet = false;
foreach (IList <object> row in values.Values.Skip(1))
{
i++;
if (row[0].ToString() == objectArray[0].ToString())
{
hasBeenSet = true; break;
}
}
if (!hasBeenSet)
{
return;
}
string range = $"{SpreadSheet}!A{i}:Z{i}";
ValueRange valueRange = new ValueRange
{
Values = new List <IList <object> > {
objectArray
}
};
SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest = service.Spreadsheets.Values.Update(valueRange, SpreadSheetID, range);
updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
UpdateValuesResponse updateRespond = updateRequest.Execute();
}
public void EditSheet(String txt, int row, String col)
{
var service = GetService_sheetv4();
var last = currSheet.Sheets.Last();
int lastrow = GetLastRow();
String range = $"{last.Properties.Title}!{col}{row}";
ValueRange valueRange = new ValueRange();
var oblist = new List <object>()
{
txt
};
valueRange.Values = new List <IList <object> > {
oblist
};
valueRange.MajorDimension = "COLUMNS";//"ROWS";//COLUMNS
Console.WriteLine("Writing location: " + range);
Console.WriteLine("Content: " + txt);
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, SpreadSheetID, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();
}
public void SetValue(string cell, string value, string sheetName = null, bool numeric = false)
{
object v = value;
if (numeric)
{
value = value.Contains("£") ? value.Replace("£", "") : value;
value = value.Contains(",") ? value.Replace(",", "") : value;
v = double.Parse(value);
}
range = sheetName + "!" + cell;
var tempBody = new List <object>()
{
v
};
ValueRange bodyTemp = new ValueRange();
bodyTemp.Values = new List <IList <object> > {
tempBody
};
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(bodyTemp, spreadSheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
// update.ResponseValueRenderOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ResponseValueRenderOptionEnum.FORMATTEDVALUE;
// update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.INPUTVALUEOPTIONUNSPECIFIED;
//UpdateValuesResponse result2 = update.Execute();
update.Execute();
}
public bool InlineEditTable(string spreadsheetId, string sheetName, string text)
{
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";
var list = new List <object>()
{
text
};
valueRange.Values = new List <IList <object> > {
list
};
SpreadsheetsResource.ValuesResource.UpdateRequest request = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, sheetName);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse response = request.Execute();
return(true);
}
//Insert the initial Setup Information:
public static void insertSetupInfoColumn(string Range, string transactionName = "Transaction Name", string scriptName = "Script Name", string Min = "Min",
string Avg = "Avg", string Max = "Max", string Std = "Std", string Count = "Count", string Bound1 = "Bound1", string Bound2 = "Bound2", string spreadsheetId = "1rkEhkGsitr3VhKayIJpvdoUsIYOPfJUNimMD09CkMuE")
{
// 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 = "1 - Performance Test Results"; // TODO: Update placeholder value.
// string spreadsheetId = spreadsheetIDTextbox.Text; //Impliment this for the input entered in the textbox
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = GetCredential(),
ApplicationName = "RaveReportAutomationUtility",
});
Data.ValueRange valueRange = new Data.ValueRange();
valueRange.MajorDimension = "ROWS";
// var output = Math.Round(Convert.ToDouble(getOverallWorkflowMetricsData(nameTag, "Response time[s]", "Avg")), 3);
var oblist = new List <Object>()
{
transactionName, scriptName, Min, Avg, Max, Std, Count, Bound1, Bound2
}; //Try to make
valueRange.Values = new List <IList <object> > {
oblist
};
SpreadsheetsResource.ValuesResource.UpdateRequest update = sheetsService.Spreadsheets.Values.Update(valueRange, spreadsheetId, Range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
Data.UpdateValuesResponse result = update.Execute();
}
private void AddHeaderToSheet(SheetsService service, string spreadsheetId, string filename)
{
string range = filename + "!A2:D2";
ValueRange body = new ValueRange();
List <IList <object> > values = new List <IList <object> >();
List <object> value = new List <object>();
value.Add("Переведена ли");
value.Add("Русских вариантов");
value.Add("Китайский");
value.Add("Английский");
values.Add(value);
body.Values = values;
SpreadsheetsResource.ValuesResource.UpdateRequest request =
service.Spreadsheets.Values.Update(body, spreadsheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
request.Execute();
range = filename + "!E1:G1";
body = new ValueRange();
values = new List <IList <object> >();
value = new List <object>();
value.Add("Переведено/Всего");
value.Add("Процент");
value.Add("Время последнего обновления");
values.Add(value);
body.Values = values;
request = service.Spreadsheets.Values.Update(body, spreadsheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
request.Execute();
}
//Update headers (i.e add extra headers if any in the appending object)
public static void UpdateHeadersInSpreadsheet(string spreadsheetId, SheetsService service, List <Object> headers)
{
Data.ValueRange requestBody = new Data.ValueRange();
List <Object> headersInSpreadsheet = ReadHeaders(spreadsheetId, service);
//Check if Headers list is equal to headers
if (headers.All(i => headersInSpreadsheet.Contains(i)))
{
return;
}
//Update the headers
headersInSpreadsheet = headersInSpreadsheet.Union(headers).ToList();
requestBody.Values = new List <IList <Object> > {
headersInSpreadsheet
};
//Create update request
SpreadsheetsResource.ValuesResource.UpdateRequest request = service.Spreadsheets.Values.Update(requestBody, spreadsheetId, "Sheet1!1:1");
request.ValueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2;
Console.WriteLine("Updating headers...");
//Execute request
Data.UpdateValuesResponse response = request.Execute();
return;
}
public async Task SetAsync <T>(string table, string uId, T value)
{
string range = A1NotationByTableAndUid(table, uId);
ValueRange values = CreateValueRange(uId, JsonConvert.SerializeObject(value));
try
{
SpreadsheetsResource.ValuesResource.UpdateRequest request =
_service.Spreadsheets.Values.Update(values, _spreadsheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
var response = await request.ExecuteAsync();
}
catch (GoogleApiException ex)
{
if (ex.Message.Contains("Unable to parse range"))
{
await InsertInternal(table, uId, values, false);
}
else
{
throw;
}
}
}
private static void WriteTable(SheetsService service, Table table, int indexToStartAt)
{
IList <IList <object> > matrix = new List <IList <object> >();
foreach (Team t in table.GetTeams())
{
var score = t.score[0].ToString() + ":" + t.score[1];
List <object> entry = new List <object>
{
t.name,
t.wins,
t.draws,
t.losses,
t.points,
score
};
matrix.Add(entry);
}
var valueRange = new ValueRange
{
Values = matrix
};
SpreadsheetsResource.ValuesResource.UpdateRequest request =
service.Spreadsheets.Values.Update(valueRange, Sheet, $"Premier League 2020/21!O{indexToStartAt}:T{indexToStartAt+19}");
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = request.Execute();
}
void ChangeTextInCell(string cell, Note note, string screenShotPath)
{
try
{
IList <IList <object> > insertValues = new List <IList <object> >();
IList <object> ROW = new List <object>();
string json = JsonConvert.SerializeObject(note, jsonSettings);
ROW.Add(json);
insertValues.Add(ROW);
ValueRange body = new ValueRange();
body.Values = insertValues;
SpreadsheetsResource.ValuesResource.UpdateRequest result = spreadSheetsService.Spreadsheets.Values.Update(body, gSyncSettings.spreadshetrID, cell);
result.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
result.Execute();
if (note.GetType() == typeof(Screenshot) && note.IsDelete == true)
{
Screenshot screen = note as Screenshot;
DeleteScreenshot(screen, screenShotPath);
}
}
catch (System.Net.Http.HttpRequestException e)
{
OnSyncStateChanged?.Invoke(gSyncState.eLostConnection);
}
catch
{
OnSyncStateChanged?.Invoke(gSyncState.eFailed);
}
}
//Overwrites cells
static void WriteData(string SheetTitle, string SheetName, string Column, int StartingCell, List <object> cellData)
{
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = GetCredential(),
ApplicationName = ApplicationName,
});
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";//"ROWS";//COLUMNS
//var oblist = new List<object>() {};
var oblist = cellData;
valueRange.Values = new List <IList <object> > {
oblist
};
// Define request parameters.
String spreadsheetId = GetSheetIdByTitle(SheetTitle);
String range = SheetName + "!" + Column + Convert.ToString(StartingCell) + ":" + Column + Convert.ToString(StartingCell + cellData.Count);
SpreadsheetsResource.ValuesResource.UpdateRequest request =
service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = request.Execute();
}
/// <summary>
/// Writes Data in list to rectangle specified by startCell and endCell. This will overwrite existing data
/// </summary>
/// <param name="spreadSheetID"></param>
/// <param name="cellData"></param>
/// <param name="SheetName"></param>
/// <param name="startCell"></param>
/// <param name="endCell"></param>
public static void WriteData(String spreadSheetID, List <object> cellData, string SheetName, String startCell, String endCell)
{
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = GetCredential(),
ApplicationName = ApplicationName,
});
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "ROWS";//COLUMNS
//var oblist = new List<object>() {};
var oblist = cellData;
valueRange.Values = new List <IList <object> > {
oblist
};
// Define request parameters.
String range = SheetName + "!" + startCell + ":" + endCell;
SpreadsheetsResource.ValuesResource.UpdateRequest request =
service.Spreadsheets.Values.Update(valueRange, spreadSheetID, range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
UpdateValuesResponse result2 = request.Execute();
}
public void UpdateData(List <DiscordGuild> servers)
{
ClearSpreadsheet();
// Define request parameters.
string range = "Server List!A1";
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";
List <object> oblist = new List <object>();
foreach (var server in servers)
{
oblist.Add(server.Name);
}
valueRange.Values = new List <IList <object> > {
oblist
}; valueRange.Values = new List <IList <object> > {
oblist
};
SpreadsheetsResource.ValuesResource.UpdateRequest update = SheetsService.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
update.Execute();
}
public async Task Write(
string tab,
string range,
List <string[]> valueList)
{
string writeRange = $"{tab}!{range}";
ValueRange valueRange = new ValueRange();
valueRange.Values = valueList.ToArray();
SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest =
new SpreadsheetsResource.ValuesResource.UpdateRequest(service,
valueRange,
spreadsheetId,
writeRange);
updateRequest.ValueInputOption
= SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
while (true)
{
try
{
await writeThrottle.WaitTillReady();
await updateRequest.ExecuteAsync();
break;
}
catch (Exception e)
{
log.Error(e);
await Task.Delay(TimeSpan.FromSeconds(10));
}
}
}
public void AddDamageNote()
{
// write the data (f5)
String spreadsheetId2 = "1tql5eJCaCZfaMGoFo01M4n-p1k6BT7LSrqyOuHdDOwY";
//String range2 = "Sheet2!G" + chromebookUser;
String range2 = "Sheet3!I" + chromebookUser;
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";//"ROWS";//COLUMNS
var oblist = new List <object>()
{
addnote
};
valueRange.Values = new List <IList <object> > {
oblist
};
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId2, range2);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();
index = 1;
}
public void AddData(string spreadsheetId, string range, string[] data)
{
SheetsService sheetsService = Connect();
ClearValuesRequest clearrequestBody = new ClearValuesRequest();
//var result = sheetsService.Spreadsheets.Values.Clear(clearrequestBody, spreadsheetId, "Sheets1!A3:C").Execute();// Append( valueRange, spreadsheetId, "Sheets1!A6").Execute();
ValueRange body = new ValueRange();
List <Object> sub = new List <object>();
sub.Add("qwerewtreytrjy");
List <List <Object> > list1 = new List <List <object> >();
list1.Add(sub);
IList <IList <Object> > values = GetData(spreadsheetId, range);
values.Clear();
values.Add(data);
body.Values = values;
SpreadsheetsResource.ValuesResource.UpdateRequest request1 = sheetsService.Spreadsheets.Values.Update(body, spreadsheetId, range);
request1.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
Console.WriteLine(request1.ValueInputOption);
UpdateValuesResponse result = request1.Execute();
Console.WriteLine(JsonConvert.SerializeObject(result));
}
//write a formula of expected value in the spreadsheet cell
public List <FormTable> PutFormula()
{
//the cell with formula
String range = "A" + (N + 1);
//a range of values - only columns for this task
ValueRange valueRange = new ValueRange
{
MajorDimension = "COLUMNS"
};
//formula as query
task = "SV";
var q = "SELECT * FROM FormTable WHERE CodeTask ={0}";
var query = db.Database.SqlQuery <FormTable>(q, task).FirstOrDefault();
var tmp = query.Formula.Replace("N", N.ToString());
var obj = new List <object> {
tmp
};
valueRange.Values = new List <IList <object> > {
obj
};
//update the spreadsheet
//request
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, sheetID, range);
//allowance for editing the spreadsheet
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
//execute the request
UpdateValuesResponse res = update.Execute();
return(null);
}
string Upload(string str)
{
string range = string.Format("{0}", r.Next(1, RowCount + 1));
string googleTranslate = @"=GOOGLETRANSLATE(A" + range + @",""" + source + @""",""" + target + @""")";
ValueRange VRx = new ValueRange();
IList <IList <object> > xx = new List <IList <object> >();
xx.Add(new List <object> {
"'" + str, googleTranslate
});
VRx.Values = xx;
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(VRx, spreadsheetId, title + "!A" + range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
UpdateValuesResponse result = update.Execute();
/* 두번에 나눠서 처리할 경우, 위 ValueInputOptionEnum를 USERENTERED 말고 RAW를 쓴다
* xx = new List<IList<object>>();
* xx.Add(new List<object> { googleTranslate });
* VRx.Values = xx;
* update = service.Spreadsheets.Values.Update(VRx, spreadsheetId, title + "!B" + range);
* update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
* result = update.Execute();
*/
return(title + "!B" + range);
}
public void UpdateSheet(List <DayCareModel> list, string workSheetName, string spreadsheetId)
{
var service = AuthorizeGoogleApp();
String range = string.Format("{0}!{1}", workSheetName, "A:AA"); //"工作表1!A1:E";
ValueRange valueRange = new ValueRange();
valueRange.Range = range;
valueRange.MajorDimension = "ROWS";//"ROWS";//COLUMNS
var oblist = new List <object>()
{
"Status", "Facility Name", "Facility Street", "Facility City", "Facility State", "Facility Zip", "Facility County", "Facility Phone", "Facility LicenseStatus",
"Licensee Name", "Licensee Address", "Licensee Phone",
"License Number", "License FacilityType", "License Capacity", "License EffectiveDate", "License ExpirationDate", "License PeriodOfOperation",
"DaysOpen Sunday", "DaysOpen Monday", "DaysOpen Tuesday", "DaysOpen Wednesday", "DaysOpen Thursday", "DaysOpen Friday", "DaysOpen Saturday",
"ServicesOffered FullDay", "ServicesOffered Provides"
};
var dataList = HandleData(list);
valueRange.Values = new List <IList <object> > {
oblist
};
foreach (var r in dataList)
{
valueRange.Values.Add(r);
}
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();
}
private static UpdateValuesResponse UpdateGoogleRequest(SheetsService service, ValueRange valueRange, string spreadsheetId, string range)
{
SpreadsheetsResource.ValuesResource.UpdateRequest updateReq = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
updateReq.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result = updateReq.Execute();
return(result);
}
//Updates sheet at range with data from values
public void UpdateCellsData(string range)
{
this.range = this.tab + "!" + range;
this.values.Range = this.range;
SpreadsheetsResource.ValuesResource.UpdateRequest request =
service.Spreadsheets.Values.Update(this.values, this.sheetID, this.range);
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
request.Execute();
}
public async Task <bool> PutStat([FromUri] string gender, [FromUri] string terminalName)
{
try
{
UserCredential credential;
string[] Scopes = { SheetsService.Scope.Spreadsheets };
using (var stream =
new FileStream(HttpRuntime.AppDomainAppPath + "/client_secret.json", FileMode.Open, FileAccess.Read))
{
string credPath = HttpRuntime.AppDomainAppPath;
credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");
credential = await GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true));
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = "neuronStats",
});
String spreadsheetId = "1mKVcyL0Xo8BHqVbcgTVfvzqM86V7jRUry3V3SKUf0wc";
String range = terminalName + "!" + ((gender == "male")?"B2":"C2");
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadsheetId, range);
var result = request.Execute();
int setInt = 0;
if (result.Values.Count != 0)
{
setInt = Int32.Parse(result.Values[0][0].ToString()) + 1;
}
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "COLUMNS";//"ROWS";//COLUMNS
var oblist = new List <object>()
{
setInt
};
valueRange.Values = new List <IList <object> > {
oblist
};
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();
return(true);
}
}
catch (Exception e)
{
return(false);
}
}
static void UpdateData()
{
string[] Scopes = { SheetsService.Scope.Spreadsheets }; //delete token folder to refresh scope
UserCredential credential;
using (var stream =
new FileStream(@"D:\_Working\_3PSolution\myproject\research\google_sheets_v4\client_id.json", FileMode.Open, FileAccess.Read))
{
string credPath = "token.json";
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
if (credential != null)
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
// The spreadsheet to request.
string spreadsheetId = "1Ln6hwAS31EII2XdlWN9PN0kaNEt4S3RIZewf81w-ePQ"; // TODO: Update placeholder value.
String range2 = "Sheet1!F5"; // update cell F5
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "ROWS"; //"ROWS";//COLUMNS
var oblist = new List <object>()
{
"1", 2
};
valueRange.Values = new List <IList <object> > {
oblist, oblist
};
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range2);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
UpdateValuesResponse result2 = update.Execute();
}
Console.ReadLine();
}
protected static bool WriteSingleCellToSheet(string strDataToSave, string strCellToSaveData)
{
try
{
Thread.Sleep(1000); // Sleep for a second so we don't go over the Google allotted requests.
// How the input data should be interpreted.
SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum valueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
// TODO: Assign values to desired properties of `requestBody`. All existing
// properties will be replaced:
ValueRange requestBody = new ValueRange
{
MajorDimension = "COLUMNS" // "ROWS" / "COLUMNS"
};
var oblist = new List <object>()
{
strDataToSave
};
requestBody.Values = new List <IList <object> > {
oblist
};
UserCredential credential;
using (var stream =
new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
{
string credPath = "token.json";
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
SCOPES,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
}
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
SpreadsheetsResource.ValuesResource.UpdateRequest request = sheetsService.Spreadsheets.Values.Update(requestBody, SPREADSHEET_ID, strCellToSaveData);
request.ValueInputOption = valueInputOption;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
UpdateValuesResponse response = request.Execute();
// Data.UpdateValuesResponse response = await request.ExecuteAsync();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw;
}
} // End WriteSingleCellToSheet
