/// <summary>
/// 複製template建立新的Sheet
/// </summary>
public void CreateSheetFromTemplate(string newSheetName)
{
var copyRequestBody = new CopySheetToAnotherSpreadsheetRequest
{
DestinationSpreadsheetId = _googleSheetModel.SpreadSheetId
};
var copyRequest = _sheetsService.Spreadsheets.Sheets.CopyTo(copyRequestBody, _googleSheetModel.SpreadSheetId, Convert.ToInt32(_googleSheetModel.TemplateSheetId));
var copyResponse = copyRequest.Execute();
var cloneSheetId = copyResponse.SheetId;
var renameRequestBody = new BatchUpdateSpreadsheetRequest
{
Requests = new List <Request>
{
new Request
{
UpdateSheetProperties = new UpdateSheetPropertiesRequest
{
Fields = "title",
Properties = new SheetProperties
{
SheetId = cloneSheetId,
Title = newSheetName
}
}
}
}
};
var renameRequest = _sheetsService.Spreadsheets.BatchUpdate(renameRequestBody, _googleSheetModel.SpreadSheetId);
renameRequest.Execute();
}
//- Copy (Tab)
public void CopyTabFrom(string sheet_url, int tab_id)
{
CopySheetToAnotherSpreadsheetRequest requestBody = new CopySheetToAnotherSpreadsheetRequest
{
DestinationSpreadsheetId = SheetUrl
};
var request = Service.Spreadsheets.Sheets.CopyTo(requestBody, sheet_url, tab_id);
SheetProperties response = request.Execute();
}
public void Backup <T>()
{
int sheetId = GetSheetIdByNameClass(typeof(T).Name);
CopySheetToAnotherSpreadsheetRequest requestBody = new CopySheetToAnotherSpreadsheetRequest();
requestBody.DestinationSpreadsheetId = SpreadsheetId;
SpreadsheetsResource.SheetsResource.CopyToRequest request = Service.Spreadsheets.Sheets.CopyTo(requestBody, SpreadsheetId, sheetId);
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
SheetProperties response = request.Execute();
// Data.SheetProperties response = await request.ExecuteAsync();
// TODO: Change code below to process the `response` object:
var json = JsonConvert.SerializeObject(response);
}
public static void ShablonDuplicater(int shID, string title)
{
CopySheetToAnotherSpreadsheetRequest requestBody = new CopySheetToAnotherSpreadsheetRequest
{
DestinationSpreadsheetId = SpreadsheetId
};
SpreadsheetsResource.SheetsResource.CopyToRequest request = service.Spreadsheets.Sheets.CopyTo(requestBody, SpreadsheetId, shID);
SheetProperties response = request.Execute();
BatchUpdateSpreadsheetRequest requestBodyBU = new BatchUpdateSpreadsheetRequest();
IList <Request> LQReq = new List <Request>();
LQReq.Add(google_requests.RenamerSh(response.SheetId, title));
requestBodyBU.Requests = LQReq;
BatchUpdateRequest BUrequest = service.Spreadsheets.BatchUpdate(requestBodyBU, SpreadsheetId);
var resp2 = BUrequest.Execute();
newSheetId = response.SheetId ?? 0;
}
public SheetProperties CopySheets(string ClientId, string ClientSecret, string destinationSpreadsheetId, string sourceSpreadsheetId, int sourceSheetId)
{
var sheetsService = GoogleAPI.GetSheetsService(ClientId, ClientSecret);
CopySheetToAnotherSpreadsheetRequest requestBody = new CopySheetToAnotherSpreadsheetRequest();
requestBody.DestinationSpreadsheetId = destinationSpreadsheetId;
SpreadsheetsResource.SheetsResource.CopyToRequest request = sheetsService.Spreadsheets.Sheets.CopyTo(requestBody, sourceSpreadsheetId, sourceSheetId);
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
SheetProperties response = request.Execute();
// Data.SheetProperties response = await request.ExecuteAsync();
// TODO: Change code below to process the `response` object:
Console.WriteLine(JsonConvert.SerializeObject(response));
return(response);
}
public async Task <IActionResult> Index()
{
try
{
// Define request parameters.
var accessTokenGoogle = await HttpContext.GetTokenAsync("Google", "access_token");
var refreshToken = await HttpContext.GetTokenAsync("Google", "refresh_token");
var accessToken = await HttpContext.GetTokenAsync("access_token");
var user = await _userManager.GetUserAsync(User);
string externalAccessToken = null;
ExternalLoginInfo info = await _signInManager.GetExternalLoginInfoAsync();
if (User.Identity.IsAuthenticated)
{
var userFromManager = await _userManager.GetUserAsync(User);
string authenticationMethod = User.Claims.FirstOrDefault(c => c.Type == ClaimTypes.AuthenticationMethod)?.Value;
if (authenticationMethod != null)
{
externalAccessToken = await _userManager.GetAuthenticationTokenAsync(userFromManager, authenticationMethod, "access_token");
}
else
{
externalAccessToken = await _userManager.GetAuthenticationTokenAsync(userFromManager, "Google", "access_token");
}
}
GoogleCredential googleCredential = GoogleCredential.FromAccessToken(externalAccessToken);
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = googleCredential,
ApplicationName = "Gdevapps Portal"
});
// String spreadsheetId = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms";//1IYcktfn-VBqzPDh_meN1TfpL5vCJmTx6YybrlPqhOQc
//String range = "Class Data!A2:E";
//String spreadsheetId = "1IYcktfn-VBqzPDh_meN1TfpL5vCJmTx6YybrlPqhOQc";//
//String range = "GradeBook!A2:E";
String spreadsheetId = "1JF459hxZuD_uKdBI7QXzfOhV8w8b_oaVpV3Ap6HYBC0";//
String range = "Data!A1:C16";
//Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
ValueRange response = await service.Spreadsheets.Values.Get(spreadsheetId, range).ExecuteAsync();
IList <IList <Object> > values = response.Values;
for (var a = 0; a < values.Count; a++)
{
for (var b = 0; b < values[a].Count; b++)
{
//var val = values[a][b].ToString();
//values[a][b] = string.IsNullOrEmpty(val) ? val : val.Replace("_1", "*");
var val = "=Column()+1";
values[a][b] = val;
}
}
var valueRange = new ValueRange()
{
Range = range,
MajorDimension = "ROWS",
Values = values
};
var newSpreadSheet = await service.Spreadsheets.Create(new Spreadsheet()
{
Properties = new SpreadsheetProperties()
{
Title = "Pash_test"
}
}).ExecuteAsync();
CopySheetToAnotherSpreadsheetRequest copyToRequest = new CopySheetToAnotherSpreadsheetRequest();
copyToRequest.DestinationSpreadsheetId = newSpreadSheet.SpreadsheetId;
var result = await service.Spreadsheets.Sheets.CopyTo(copyToRequest, spreadsheetId, 0).ExecuteAsync();
DeleteSheetRequest deleteSheetRequest = new DeleteSheetRequest()
{
SheetId = 0
};
BatchUpdateSpreadsheetRequest updateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest()
{
Requests = new List <Request>
{
new Request()
{
DeleteSheet = deleteSheetRequest
}
},
ResponseIncludeGridData = false,
IncludeSpreadsheetInResponse = false
};
await service.Spreadsheets.BatchUpdate(updateSpreadsheetRequest, newSpreadSheet.SpreadsheetId).ExecuteAsync();
if (result != null)
{
return(Ok(result));
}
// var updateRequest = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, range);
//Formula
// updateRequest.ResponseValueRenderOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ResponseValueRenderOptionEnum.FORMULA;
// updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
//service.Spreadsheets.Values.Update().
// await updateRequest.ExecuteAsync();
if (values != null && values.Count > 0)
{
return(Ok(values));
}
else
{
return(BadRequest("No data found."));
}
}
catch (Exception ex)
{
string message = ex.Message;
throw;
}
}
/// Documentation https://developers.google.com/sheets/v4/reference/sheets/copyTo
/// Generation Note: This does not always build corectly. Google needs to standardise things I need to figuer out which ones are wrong.
/// </summary>
/// <param name="service">Authenticated Sheets service.</param>
/// <param name="spreadsheetId">The ID of the spreadsheet containing the sheet to copy.</param>
/// <param name="sheetId">The ID of the sheet to copy.</param>
/// <param name="body">A valid Sheets v4 body.</param>
/// <returns>SheetPropertiesResponse</returns>
public static SheetProperties CopyTo(SheetsService service, string spreadsheetId, int sheetId, CopySheetToAnotherSpreadsheetRequest body)
{
try
{
// Initial validation.
if (service == null)
{
throw new ArgumentNullException("service");
}
if (body == null)
{
throw new ArgumentNullException("body");
}
if (spreadsheetId == null)
{
throw new ArgumentNullException(spreadsheetId);
}
// Make the request.
return(service.Sheets.CopyTo(body, spreadsheetId, sheetId).Execute());
}
catch (Exception ex)
{
throw new Exception("Request Sheets.CopyTo failed.", ex);
}
}
