static SheetsAPILibrary()
{
// Creating a ServiceAccountCredential.Initializer
// ref: https://googleapis.dev/dotnet/Google.Apis.Auth/latest/api/Google.Apis.Auth.OAuth2.ServiceAccountCredential.Initializer.html
ServiceAccountCredential.Initializer initializer = new ServiceAccountCredential.Initializer(serviceAccountID);
// Getting ServiceAccountCredential from the private key
// ref: https://googleapis.dev/dotnet/Google.Apis.Auth/latest/api/Google.Apis.Auth.OAuth2.ServiceAccountCredential.html
// can use .FromJson to load in a string if desired, like the one from resources
ServiceAccountCredential credential = (ServiceAccountCredential)GoogleCredential
.FromFile(Application.streamingAssetsPath + "/Creds/key.json")
.CreateScoped(SheetsService.Scope.Spreadsheets)
.UnderlyingCredential;
service = new SheetsService(new Google.Apis.Services.BaseClientService.Initializer()
{
HttpClientInitializer = credential
});
} public static IList<IList<object>> GetSheetRange(string sheetNameAndRange, string sheetID = spreadsheetId)
{
SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, sheetNameAndRange);
ValueRange response = request.Execute();
IList<IList<object>> values = response.Values;
if (values != null && values.Count > 0)
{
return values;
}
else
{
Debug.Log("No data found.");
return null;
}
}public static void UpdateSheet(string sheetName, string range, List<object> data)
{
ValueRange valueRange = new ValueRange();
valueRange.Values = new List<IList<object>>() { data };
SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, $"{sheetName}!{range}");
updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
UpdateValuesResponse updateResponse = updateRequest.Execute();
}public static void Append(string sheetName, string range, List<object> data)
{
ValueRange valueRange = new ValueRange();
valueRange.Values = new List<IList<object>>() { data };
SpreadsheetsResource.ValuesResource.AppendRequest appendRequest = service.Spreadsheets.Values.Append(valueRange, spreadsheetId, $"{sheetName}!{range}");
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
AppendValuesResponse appendResponse = appendRequest.Execute();
}Two videos I found super helpful
We need to install the Google Sheets .Net API. The easiest way I found was to use NuGet For Unity plugin. Then search Google Sheets and install them that way. I had to download one version less than current, but other than that I had no problems.
When we pull the data, we get a IList<IList<object>>, each object which we can cast as a string. Each IList<object> is a row, with each object inside being a cell in that row.
When updating we also need to send a IList<IList<object>> but I’m usually only modifying a single cell, or row at most, so just wrapping an object list in another list works for me. If doing multiple rows, as specified by the range, just populate the list of list of objects accordingly.
Leave a Reply