/// <summary>
/// Поиск листа в файле
/// </summary>
/// <param name="Sheet">Имя лист</param>
/// <returns>id листа, если нету то null</returns>
int?FindSheet(string Sheet)
{
SpreadsheetsResource.GetRequest srgrSheetsName = sheetsService.Spreadsheets.Get(SpreadsheetsId);
Spreadsheet sSheetsName = srgrSheetsName.Execute();
return(sSheetsName.Sheets.Where(x => x.Properties.Title == Sheet)?.FirstOrDefault()?.Properties.SheetId);
}
public static void AtualizarPlanilha()
{
SpreadsheetsResource.GetRequest get = sheetsService.Spreadsheets.Get(idPlanilha);
planilhas = get.Execute();
valueInputOption = (SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum) 2;
foreach (Sheet planilha in planilhas.Sheets)
{
ClearValuesRequest clearRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(clearRequest, idPlanilha, "!A1:Z1000");
request.Execute();
for (int i = 0; i <= 5; i++)
{
GerarTabelaMesSheet(i);
}
for (int i = 0; i <= 5; i++)
{
GerarTabelaSavingsSheet(i);
}
Layout();
}
}
// Returns spreadsheet with only listed sheets filled with data, and the non listed ones being obtained only as a sheet dataless shells.
internal static async Task <Spreadsheet> GetSpreadsheetPartiallyAsync(this SheetsService sheetsService, string spreadsheetId, IList <string> sheetTitleIds)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain get spreadsheet shell request
SpreadsheetsResource.GetRequest getSpreadsheetShellRequest = GoogleServicesExtensionsRequestsFactory.GetGetSpreadsheetShellRequest(sheetsService, spreadsheetId);
// Execute google request in a safe synchronous manner.
Spreadsheet spreadsheet = await RequestsExecutor.SafeExecuteAsync <Spreadsheet>(getSpreadsheetShellRequest);
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain get spreadsheet listed sheets only request
SpreadsheetsResource.GetRequest getSpreadsheetListedSheetsOnlyRequest = GoogleServicesExtensionsRequestsFactory.GetGetSpreadsheetListedSheetsOnlyRequest(sheetsService, spreadsheetId, sheetTitleIds);
// Execute google request in a safe synchronous manner.
Spreadsheet spreadsheetSheetsData = await RequestsExecutor.SafeExecuteAsync <Spreadsheet>(getSpreadsheetListedSheetsOnlyRequest);
// Loops through sheets with data and replaces they empty representations in the spreadsheetDataShell with them.
foreach (Sheet sheetData in spreadsheetSheetsData.Sheets)
{
// Find spreadsheet shell
Sheet sheetSheetToBeReplaced = spreadsheet.Sheets.Where((sheetShell) => { return(sheetShell.Properties.Title == sheetData.Properties.Title); }).First();
// Replace sheet shell, with instance of a sheet containing data.s
spreadsheet.Sheets.Remove(sheetSheetToBeReplaced);
spreadsheet.Sheets.Add(sheetData);
}
// Return spreadsheet shell filled with data of the listed sheets.
return(spreadsheet);
}
public virtual void GetTranslationStats(ref List <TranslationStatEntry> stats)
{
Console.WriteLine("Calculating Translation Statistic for " + AssetName);
SpreadsheetsResource.GetRequest request = GoogleSheetConnector.GetInstance().Service.Spreadsheets.Get(SheetId);
request.Ranges = SheetRange;
request.IncludeGridData = true;
Spreadsheet sheet = request.Execute();
IList <GridData> grid = sheet.Sheets[0].Data;
//Getting each range (should only be one)
AssetEntry tmpEntry = new AssetEntry(VariableDefinitions);
foreach (GridData gridData in grid)
{
//For each row
foreach (var row in gridData.RowData)
{
SheetCellWithColor[] rowRaw = new SheetCellWithColor[row.Values.Count];
for (int i = 0; i < row.Values.Count; i++)
{
rowRaw[i] = new SheetCellWithColor(row.Values[i]);
}
tmpEntry.CalculateTranslationStats(rowRaw, ref stats);
}
}
}
internal virtual void GetSheetData() // Get All system Data
{
SpreadsheetsResource.GetRequest request = _sheetsService.Spreadsheets.Get(SpreadsheetId);
IList <Sheet> sheets = request.Execute().Sheets;
RowCount = sheets[SheetIndex].Properties.GridProperties.RowCount.Value; // Max count
ColumnCount = sheets[SheetIndex].Properties.GridProperties.ColumnCount.Value; // Max count
}
/// <summary>
/// Recupera todas as folhas/abas da planilha.
/// </summary>
/// <returns>Uma lista contendo todas as abas da planilha.</returns>
private IList <Sheet> GetSheets()
{
SpreadsheetsResource.GetRequest request = service.Spreadsheets.Get(spreadsheetId);
Spreadsheet response = request.Execute();
IList <Sheet> values = response.Sheets;
return(response.Sheets);
}
// Return get spreadsheet listed sheets only request.
internal static SpreadsheetsResource.GetRequest GetGetSpreadsheetListedSheetsOnlyRequest(SheetsService sheetsService, string spreadsheetId, IList <string> sheetTitleIds)
{
// Get spreadsheet containing only specific ranges
SpreadsheetsResource.GetRequest request = sheetsService.Spreadsheets.Get(spreadsheetId);
request.IncludeGridData = true;
request.Ranges = new Google.Apis.Util.Repeatable <string>(sheetTitleIds);
// Return constructed spreadsheet - only listed spreadsheet. Not listed spreadsheets hasn't been returned at all, not even as sheet shells.
return(request);
}
// Return get spreadsheet request.
internal static SpreadsheetsResource.GetRequest GetGetSpreadsheetRequest(SheetsService sheetsService, string spreadsheetId)
{
// Construct new get spreadsheet request associated with provided spreadsheet id.
SpreadsheetsResource.GetRequest request = sheetsService.Spreadsheets.Get(spreadsheetId);
// Set IncludeGridData to true, to assure that the spreadsheet data will be included.
request.IncludeGridData = true;
// Return constructed spreadsheet.
return(request);
}
// Returns existing spreadsheet dataless shell
internal static Spreadsheet GetSpreadsheetShellSync(this SheetsService sheetsService, string spreadsheetId)
{
// Wait for google apis request quota availability.
SessionRequestsLimiter.Instance.Wait();
// Obtain get spreadsheet request
SpreadsheetsResource.GetRequest getSpreadsheetShellRequest = GoogleServicesExtensionsRequestsFactory.GetGetSpreadsheetShellRequest(sheetsService, spreadsheetId);
// Execute google request in a safe synchronous manner.
return(RequestsExecutor.SafeExecuteSync <Spreadsheet>(getSpreadsheetShellRequest));
}
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 IEnumerable <Dictionary <string, string> > GetAvailableSheetProperties(SheetsService service)
{
SpreadsheetsResource.GetRequest request = service.Spreadsheets.Get(spreadSheetId);
var response = request.Execute();
return(response.Sheets.Select(e => {
return new Dictionary <string, string>()
{
{ "Title", e.Properties.Title },
{ "SheetId", e.Properties.SheetId.ToString() }
};
}));
}
public List <string> GetTabNames(string SheetID)
{
SpreadsheetsResource.GetRequest request = SpreadsheetService.Spreadsheets.Get(SheetID);
Spreadsheet sheet = request.Execute();
List <string> tabNames = new List <string>();
foreach (Sheet sh in sheet.Sheets)
{
tabNames.Add(sh.Properties.Title);
}
return(tabNames);
}
// Retrieves sheet int? Id based on the provided spreadsheetId and sheetTitleId.
internal static async Task <int?> GetSheetIdFromSheetTitleIdIdAsync(this SheetsService sheetsService, string spreadsheetId, string sheetTitleId)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain get spreadsheet shell request.
SpreadsheetsResource.GetRequest request = GoogleServicesExtensionsRequestsFactory.GetGetSpreadsheetShellRequest(sheetsService, spreadsheetId);
// Get spreadsheet shell data in safe synchronous way.
Spreadsheet spreadsheetShellData = await RequestsExecutor.SafeExecuteAsync <Spreadsheet>(request);
// Loop through all the sheet shells and return the int? sheetId of the one with Title equal to provided sheetTitleId.
return(spreadsheetShellData.Sheets.Where((sheetShell) => { return sheetShell.Properties.Title == sheetTitleId; }).First().Properties.SheetId);
}
private async Task <int> getSheetID(string spreadsheetId, string tableName)
{
//get sheet id by sheet name
SpreadsheetsResource.GetRequest requs = _service.Spreadsheets.Get(spreadsheetId);
Spreadsheet spr = await autoAuthorize(requs);
if (spr == null)
{
throw new Exception("找不到此ID的表");
}
Sheet sh = spr.Sheets.Where(s => s.Properties.Title == tableName).FirstOrDefault();
return((int)sh.Properties.SheetId);
}
private async Task <Sheet> GetHeroicSignupSheet()
{
var range = "'Signup Sheet'!A1:K86";
var sheetRequest = new SpreadsheetsResource.GetRequest(this.SheetsService, HeroicSheetsId);
sheetRequest.Ranges = new Google.Apis.Util.Repeatable <string>(new[] { range });
sheetRequest.IncludeGridData = true;
var sheet = await sheetRequest.ExecuteAsync();
var sheetToUpdate = sheet.Sheets[0];
var rowData = sheetToUpdate.Data.First().RowData;
return(sheetToUpdate);
}
// Create new spreadsheet from data containing spreadsheet blueprint
internal static async Task <Spreadsheet> AddSpreadsheetAsync(this SheetsService sheetsService, string spreadsheetTitle, IList <Tuple <string, int, int, IList <IList <string> > > > spreadsheetBlueprint)
{
// Wait for google apis request quota availability.
await SessionRequestsLimiter.Instance.WaitAsync();
// Obtain create spreadsheet request.
SpreadsheetsResource.CreateRequest createSpreadsheetRequest = GoogleServicesExtensionsRequestsFactory.GetAddSpreadsheetRequest(sheetsService, spreadsheetTitle, spreadsheetBlueprint);
// Execute createSpreadsheetRequest in safe asynchronous manner, and store the result in a local variable.
Spreadsheet spreadsheet = await RequestsExecutor.SafeExecuteAsync <Spreadsheet>(createSpreadsheetRequest);
// Obtain get spreadsheet request.
SpreadsheetsResource.GetRequest getSpreadsheetRequest = GoogleServicesExtensionsRequestsFactory.GetGetSpreadsheetRequest(sheetsService, spreadsheet.SpreadsheetId);
// Execute getSpreadsheetRequest in safe synchronous manner.
return(await RequestsExecutor.SafeExecuteAsync <Spreadsheet>(getSpreadsheetRequest));
}
// Create new spreadsheet from data containing spreadsheet blueprint
internal static Spreadsheet AddSpreadsheetSync(this SheetsService sheetsService, string spreadsheetTitle, IList <Tuple <string, int, int, IList <IList <string> > > > spreadsheetBlueprint)
{
// Wait for google apis request quota availability.
SessionRequestsLimiter.Instance.Wait();
// Obtain create spreadsheet request.
SpreadsheetsResource.CreateRequest createSpreadsheetRequest = GoogleServicesExtensionsRequestsFactory.GetAddSpreadsheetRequest(sheetsService, spreadsheetTitle, spreadsheetBlueprint);
// Execute createSpreadsheetRequest in safe synchronous manner, and store revived dateless spreadsheet instance
Spreadsheet spreadsheet = RequestsExecutor.SafeExecuteSync <Spreadsheet>(createSpreadsheetRequest);
// Obtain get spreadsheet request.
SpreadsheetsResource.GetRequest getSpreadsheetRequest = GoogleServicesExtensionsRequestsFactory.GetGetSpreadsheetRequest(sheetsService, spreadsheet.SpreadsheetId);
// Execute getSpreadsheetRequest in safe synchronous manner.
return(RequestsExecutor.SafeExecuteSync <Spreadsheet>(getSpreadsheetRequest));
}
public List <string> GetTableSheets(string spreadsheetId)
{
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
SpreadsheetsResource.GetRequest request = service.Spreadsheets.Get(spreadsheetId);
var res = request.Execute();
List <string> names = new List <string>();
foreach (var item in res.Sheets)
{
names.Add(item.Properties.Title);
}
return(names);
}
private static void pullSiteData(string siteName, String sheetKey, Queue <string> sheets)
{
SpreadsheetsResource.GetRequest request = service.Spreadsheets.Get(sheetKey);
request.IncludeGridData = true;
try
{
Google.Apis.Sheets.v4.Data.Spreadsheet response = request.Execute();
foreach (var sheetName in sheets)
{
try
{
Sheet sheet = response.Sheets.FirstOrDefault <Sheet>(s => s.Properties.Title == sheetName);
if (sheet == null)
{
Console.WriteLine("Couldn't find sheet {0} in spreadsheet {1} {2}", sheetName, siteName, sheetKey);
continue;
}
foreach (var row in sheet.Data.First <GridData>().RowData)
{
Console.Write("{0}\t{1}\t", siteName, sheetName);
foreach (var cell in row.Values)
{
Console.Write("{0}\t", (cell.FormattedValue ?? "").Trim());
}
Console.WriteLine();
}
}
catch (Exception e)
{
Console.WriteLine("Error processing sheet data for {0}:{1}:{2}={3}", siteName, sheetKey, sheetName, e.Message);
};
}
}
catch (Exception e)
{
Console.WriteLine("Couldn't get sheet data for {0} on {1} = {2}", siteName, sheetKey, e.GetType());
};
}
static void Main(string[] args)
{
UserCredential credential;
using (var stream =
new FileStream(@"secrets\credentials.json", FileMode.Open, FileAccess.Read))
{
// The file token.json stores the user's access and refresh tokens, and is created
// automatically when the authorization flow completes for the first time.
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,
});
// Define request parameters.
String spreadsheetId = "1kL0cEVkh0dTVY6CfCCJij30Sh_D9XhylaOVFQxsZJXk";
String range = "Типовой этаж!3:14";
SpreadsheetsResource.GetRequest request = service.Spreadsheets.Get(spreadsheetId);
request.Ranges = range;
request.IncludeGridData = true;
var response = request.Execute();
var sheetData = response.Sheets[0].Data[0];
SaveHblockFlats(sheetData.RowData, Environment.CurrentDirectory);
}
/// <summary>
/// This is the method that actually does the work.
/// </summary>
/// <param name="DA">The DA object is used to retrieve from inputs and store in outputs.</param>
protected override void SolveInstance(IGH_DataAccess DA)
{
string id = "";
if (!DA.GetData(0, ref id))
{
return;
}
// get authorized service
var service = Core.Authorizer.GetAuthorizedService();
// build request object for id
SpreadsheetsResource.GetRequest request = service.Spreadsheets.Get(id);
request.IncludeGridData = true;
// execute request, result is spreadsheet object
var spreadSheet = request.Execute();
// set data on output
DA.SetData(0, spreadSheet);
}
public async Task <Google.Apis.Sheets.v4.Data.Spreadsheet> GetSheet(string spreadsheetId, string range)
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = _credentials,
ApplicationName = "BiblioSalas/0.1",
});
// The ranges to retrieve from the spreadsheet.
List <string> ranges = new List <string> {
range
};
bool includeGridData = true;
SpreadsheetsResource.GetRequest request = sheetsService.Spreadsheets.Get(spreadsheetId);
request.Ranges = ranges;
request.IncludeGridData = includeGridData;
Google.Apis.Sheets.v4.Data.Spreadsheet response = await request.ExecuteAsync();
return(response);
}
/// <summary>
/// this will get the index of the sheet's location to be used for GridRange usages,returns -1 if not found
/// </summary>
/// <param name="SheetName"></param>
/// <returns></returns>
public static int?GetSheetIdBySheetName(string spreadsheetId, string SheetName)
{
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = GetCredential(),
ApplicationName = ApplicationName,
});
SpreadsheetsResource.GetRequest request = sheetsService.Spreadsheets.Get(spreadsheetId);
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Data.Spreadsheet response = request.Execute();
for (int i = 0; i < response.Sheets.Count; i++)
{
Sheet tempSheet = response.Sheets[i];
if (tempSheet.Properties.Title == SheetName)
{
return(tempSheet.Properties.SheetId);
}
}
return(-1);
}
public static void Reload()
{
service?.Dispose();
timer?.Dispose();
if (!Config.sheetsEnabled)
{
return;
}
UserCredential credential;
using (FileStream stream = new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
{
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(GoogleClientSecrets.Load(stream).Secrets, scopes, "SupportBoi", CancellationToken.None, new FileDataStore("token.json", true)).Result;
Console.WriteLine("Google credential file saved to 'token.json'");
}
// Create Google Sheets API service.
service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = "SupportBoi"
});
SpreadsheetsResource.GetRequest request = service.Spreadsheets.Get(Config.spreadsheetID);
Spreadsheet spreadsheet = request.Execute();
if (spreadsheet == null)
{
Console.WriteLine();
throw new ArgumentException("ERROR: Could not find a Google Sheets Spreadsheet with provided ID.");
}
timer = new Timer(RunJobs, null, 1000, Timeout.Infinite);
}
public void NotMain()
{
UserCredential credential;
using (var stream =
new FileStream("client_secret.json", FileMode.Open, FileAccess.ReadWrite))
{
string credPath = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
}
sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = credential,
ApplicationName = "TableTop Brawl",
ApiKey = "AIzaSyCnF3u6zTn4U2PE-ypGNIB154Ko5Cbeet4",
});
// Spreadsheet to request
string spreadsheetId = "1nITN-NKCSODuR4LLJGuqBKyrf47lmvCYiRl7oCe453U";
// Range of cells
List <string> ranges = new List <string>(); // TODO: Update placeholder value.
// Grid Data
bool includeGridData = false;
SpreadsheetsResource.GetRequest request = sheetsService.Spreadsheets.Get(spreadsheetId);
request.Ranges = ranges;
request.IncludeGridData = includeGridData;
}
}//выбор таблицы
public void Add(List <Parse> Liste)
{
string[] Scopes = { SheetsService.Scope.Spreadsheets };
String spreadsheetId = "1IUrDULP4pzI8kioVAcl_5f1-B8mFdaahjuj8qwVe5dA";
string AppName = "WpfApp1";
using (var stream =
new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
{
UserCredential credential;
string credPath = Environment.GetFolderPath(
Environment.SpecialFolder.Personal);
credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = AppName
});
SpreadsheetsResource.GetRequest request = service.Spreadsheets.Get(spreadsheetId);
var response = request.Execute();
var sheet = response.Sheets.FirstOrDefault(x => Convert.ToBoolean(x.Properties.GridProperties.RowCount));
List <Request> requests = new List <Request>();
BatchUpdateSpreadsheetRequest busrer = new BatchUpdateSpreadsheetRequest
{
Requests = requests
};
for (int i = 4; i < sheet.Properties.GridProperties.RowCount; i++)
{
List <CellData> Valurrr = new List <CellData>
{
new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = ""
}
},
new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = ""
}
},
new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = ""
}
}
};
requests.Add(new Request {
UpdateCells = new UpdateCellsRequest {
Start = new GridCoordinate {
SheetId = 0, RowIndex = i, ColumnIndex = 0
}, Rows = new List <RowData> {
new RowData {
Values = Valurrr
}
}, Fields = "userEnteredValue"
}
});
}
for (int i = 4; i < Liste.Count; i++)
{
List <CellData> Value = new List <CellData>
{
new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = Liste[i].Name,
}
},
new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = Liste[i].Price
}
},
new CellData {
UserEnteredValue = new ExtendedValue {
FormulaValue = "=SUM(D" + (i + 5) + ":" + (i + 5) + ")"
}
}
};
requests.Add(new Request {
UpdateCells = new UpdateCellsRequest {
Start = new GridCoordinate {
SheetId = 0, RowIndex = i, ColumnIndex = 0
}, Rows = new List <RowData> {
new RowData {
Values = Value
}
}, Fields = "userEnteredValue"
}
});
}
List <CellData> Cash = new List <CellData>();
for (char c = 'D'; c <= 'Z'; c++)
{
Cash.Add(new CellData {
UserEnteredValue = new ExtendedValue {
FormulaValue = "=ROUNDUP(SUMPRODUCT($B5:$B;" + c + "5:" + c + ");1)"
}
});
}
requests.Add(new Request {
UpdateCells = new UpdateCellsRequest {
Start = new GridCoordinate {
SheetId = 0, RowIndex = 1, ColumnIndex = 3
}, Rows = new List <RowData> {
new RowData {
Values = Cash
}
}, Fields = "userEnteredValue"
}
});
List <CellData> Dates = new List <CellData>
{
new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = DateTime.Today.ToShortDateString()
}
}
};
requests.Add(new Request {
UpdateCells = new UpdateCellsRequest {
Start = new GridCoordinate {
SheetId = 0, RowIndex = 0, ColumnIndex = 0
}, Rows = new List <RowData> {
new RowData {
Values = Dates
}
}, Fields = "userEnteredValue"
}
});
List <CellData> Sum = new List <CellData>
{
new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = "Сумма"
}
},
};
requests.Add(new Request {
UpdateCells = new UpdateCellsRequest {
Start = new GridCoordinate {
SheetId = 0, RowIndex = 1, ColumnIndex = 1
}, Rows = new List <RowData> {
new RowData {
Values = Sum
}
}, Fields = "userEnteredValue"
}
});
List <CellData> Info = new List <CellData>
{
new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = "Дополнительная информация"
}
},
};
requests.Add(new Request {
UpdateCells = new UpdateCellsRequest {
Start = new GridCoordinate {
SheetId = 0, RowIndex = 2, ColumnIndex = 0
}, Rows = new List <RowData> {
new RowData {
Values = Info
}
}, Fields = "userEnteredValue"
}
});
List <CellData> Name = new List <CellData>
{
new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = "Наименование"
}
},
new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = "Цена"
}
},
};
requests.Add(new Request {
UpdateCells = new UpdateCellsRequest {
Start = new GridCoordinate {
SheetId = 0, RowIndex = 3, ColumnIndex = 0
}, Rows = new List <RowData> {
new RowData {
Values = Name
}
}, Fields = "userEnteredValue"
}
});
List <CellData> SumFormula = new List <CellData>
{
new CellData {
UserEnteredValue = new ExtendedValue {
FormulaValue = "=SUM(D2:2)"
}
}
};
service.Spreadsheets.BatchUpdate(busrer, spreadsheetId).Execute();
MessageBox.Show("Выполнено");
}
}
private static Spreadsheet GetSpreadsheet()
{
SpreadsheetsResource.GetRequest request = service.Spreadsheets.Get(Config.spreadsheetID);
return(request.Execute());
}
static void GetData()
{
string[] Scopes = { SheetsService.Scope.SpreadsheetsReadonly }; //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 = "1Ecm_3kKV4Wgz8BpZhPeMez5fy2pZuNSr_BNojTmOPwU"; // TODO: Update placeholder value.
// The ranges to retrieve from the spreadsheet.
List <string> ranges = new List <string>(); // TODO: Update placeholder value.
ranges.Add("Sheet1!A1:F");
ranges.Add("Sheet2!A1:N");
ranges.Add("Sheet3!A1:N");
// True if grid data should be returned.
// This parameter is ignored if a field mask was set in the request.
bool includeGridData = false; // TODO: Update placeholder value.
SpreadsheetsResource.GetRequest request = sheetsService.Spreadsheets.Get(spreadsheetId);
request.Ranges = ranges;
request.IncludeGridData = includeGridData;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Spreadsheet response = request.Execute();
// Data.Spreadsheet response = await request.ExecuteAsync();
string str = JsonConvert.SerializeObject(response);
// TODO: Change code below to process the `response` object:
Console.WriteLine(str);
}
Console.ReadLine();
}
static void Main(string[] args)
{
UserCredential credential;
using (var stream =
new FileStream("client_secret_424891321849-fo9l06m0jeke5hsvgtuisf97fnd4inf6.apps.googleusercontent.com.json", FileMode.Open, FileAccess.Read))
{
string credPath = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
//https://docs.google.com/a/theryanzoo.com/spreadsheets/d/1fwoB2S-j6k10TYA-5wXtmVI2NXpP2iYPBZYSHCWNO-M/edit?usp=sharing
// Define request parameters.
String spreadsheetId = "1fwoB2S-j6k10TYA-5wXtmVI2NXpP2iYPBZYSHCWNO-M";
String range = "A1:b3";
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadsheetId, range);
//sheet_metadata = service.spreadsheets().get(spreadsheetId = spreadsheet_id).execute()
//sheets = sheet_metadata.get('sheets', '')
//title = sheets[0].get("properties", { }).get("title", "Sheet1")
//sheet_id = sheets[0].get("properties", { }).get("sheetId", 0)
SpreadsheetsResource.GetRequest xxx = service.Spreadsheets.Get(spreadsheetId);
// https://docs.google.com/spreadsheets/d/1fwoB2S-j6k10TYA-5wXtmVI2NXpP2iYPBZYSHCWNO-M/edit
ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
if (values != null && values.Count > 0)
{
Console.WriteLine("Name, Major");
foreach (var row in values)
{
// Print columns A and E, which correspond to indices 0 and 4.
Console.WriteLine("{0}, {1}", row[0], row[1]);
}
}
else
{
Console.WriteLine("No data found.");
}
Console.Read();
}
public static bool CreateSheetFile(string pathCredential, string pathToken, string pathSpreadsheet, string spreadsheetId, Dictionary <string, string> dicSheetGet)
{
//return true;
var credential = GetCredential(pathCredential, pathToken);
if (credential != null)
{
if (string.IsNullOrEmpty(pathSpreadsheet))
{
throw new Exception("Not found pathSpreadsheet");
}
if (string.IsNullOrEmpty(spreadsheetId))
{
throw new Exception("Not found spreadsheetId");
}
if (dicSheetGet == null || dicSheetGet.Count == 0)
{
throw new Exception("lstSheetGet is empty");
}
Dictionary <string, IList <RowData> > dic = new Dictionary <string, IList <RowData> >();
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = credential,
ApplicationName = "Google-SheetsSample/0.1",
});
SpreadsheetsResource.GetRequest request = sheetsService.Spreadsheets.Get(spreadsheetId);
request.Ranges = new List <string>();
request.IncludeGridData = false;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Spreadsheet response = request.Execute();
var lstSheets = new List <string>();
foreach (var sheet in response.Sheets)
{
lstSheets.Add(sheet.Properties.Title);
}
foreach (var item in dicSheetGet)
{
if (!dic.ContainsKey(item.Key) && lstSheets.Contains(item.Key))
{
request.Ranges = new List <string> {
item.Key + "!" + item.Value
};
request.IncludeGridData = true;
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
response = request.Execute();
dic.Add(item.Key, response.Sheets[0].Data.FirstOrDefault().RowData);
}
}
if (File.Exists(pathSpreadsheet))
{
File.Delete(pathSpreadsheet);
}
using (var package = new ExcelPackage(new FileInfo(pathSpreadsheet)))
{
foreach (var item in dic)
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(item.Key);
if (item.Value != null && item.Value.Count > 0)
{
for (int r = 0; r < item.Value.Count; r++)
{
var row = item.Value[r];
if (row != null && row.Values != null && row.Values.Count > 0)
{
for (int c = 0; c < row.Values.Count; c++)
{
if (row.Values[c] != null && row.Values[c].FormattedValue != null)
{
worksheet.Cells[r + 1, c + 1].Value = row.Values[c].FormattedValue;
}
}
}
}
}
}
package.Save();
}
return(true);
}
else
{
throw new Exception("Not auth 2.0 google");
}
}
