Hello everyone,
I spend lots of time for getting result as per my requirement but not able
to get result.
Q:
I have google spread sheet.
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 requirment 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. So help me to solve this problem . I'm doing this
using C# . Language I attach my code and image file .
So please help me to solve it ASAP. Thanks.
*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.ColumnIndex = 4; // If i use this
, the current column will replace with new one
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;
}
}
--
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.
