I have a Google spreadsheet. On my E column I want to add recent month with
year (eg: Sept 2017) if not available on E column.Suppose there is (Aug
2017) and now when I add (Sept 2017) it add but it replace the existing
column that is Aug 2107. My requirement is to keep both: newly added (Sept
2017) and (Aug 2017). I want recent month on E column and shift existing
column on right that is F column. I'm doing this using C#.
*Code:*
*static void Main(string[] args)
{
HttpClient client = new HttpClient();
client.DefaultRequestHeaders.Accept.Clear();
client.DefaultRequestHeaders.Accept.Add(new
MediaTypeWithQualityHeaderValue("application/json"));
try
{
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = GetCredential(),
ApplicationName = ApplicationName,
});
String spreadsheetId = "1lZnvQe6lTGG81hyuQvf7HjH8YpnIadFlNHjFUq_G-5Q";
String range = "E1";
SpreadsheetsResource.ValuesResource.GetRequest getRequest =
service.Spreadsheets.Values.Get(spreadsheetId, range);
Data.ValueRange response = getRequest.Execute();
string recentMonth = DateTime.Now.ToString("MMM yyyy");
var recentMonthArrayValue = new List<string[]>();
recentMonthArrayValue.Add(new string[] { recentMonth });
if (response.Values[0][0].ToString() != recentMonth)
{
DateTime now = DateTime.Now;
var currentMonthStartDate = new DateTime(now.Year, now.Month,
1).ToShortDateString();
Data.BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new
Data.BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List<Data.Request>();
Data.Request request = new Data.Request();
batchUpdateSpreadsheetRequest.Requests.Add(request);
request.UpdateCells = new Data.UpdateCellsRequest();
var gridCoordinate = new Data.GridCoordinate();
gridCoordinate.ColumnIndex = 5;
gridCoordinate.SheetId = 0;
request.UpdateCells.Start = gridCoordinate;
request.UpdateCells.Fields = "*";
request.UpdateCells.Rows = new List<Data.RowData>();
var rowData = new Data.RowData();
request.UpdateCells.Rows.Add(rowData);
rowData.Values = new List<Data.CellData>();
var cellData = new Data.CellData();
cellData.UserEnteredValue = new Data.ExtendedValue();
cellData.UserEnteredValue.FormulaValue = "=TEXT(\"" + currentMonthStartDate +
"\",\"MMM yyyy\")";
rowData.Values.Add(cellData);
SpreadsheetsResource.BatchUpdateRequest batchUpdateRequest =
service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId);
batchUpdateRequest.Execute();
}
}
catch (Exception e)
{
//throw;
}
}
public static UserCredential GetCredential()
{
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/sheets.googleapis.com-dotnet-quickstart.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
return credential;
}
}
*
<https://lh3.googleusercontent.com/-bHjohr9DAP4/WcnhmxPsTbI/AAAAAAAAAAM/cF5FU7cBYgoSqw1uGvkgXLmnVR-gJ919gCLcBGAs/s1600/test-q2.png>
--
You received this message because you are subscribed to the Google Groups
"Google Spreadsheets API" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
