/// <summary>
/// Creates a new sheet within the Spreadsheet with the id <see cref="SpreadSheetId"/>.
/// </summary>
/// <param name="title">The title for the new sheet</param>
/// <param name="newSheetProperties"></param>
/// <returns>The new sheet id.</returns>
public int AddSheet(string title, NewSheetProperties newSheetProperties)
{
if (string.IsNullOrEmpty(SpreadSheetId))
{
throw new Exception($"{nameof(SpreadSheetId)} is required. Please assign a valid Spreadsheet Id to the property.");
}
if (newSheetProperties == null)
{
throw new ArgumentNullException(nameof(newSheetProperties));
}
var createRequest = new Request()
{
AddSheet = new AddSheetRequest
{
Properties = new SheetProperties {
Title = title
}
}
};
var batchUpdateReqTask = SendBatchUpdateRequest(SpreadSheetId, createRequest);
var sheetId = batchUpdateReqTask.Replies[0].AddSheet.Properties.SheetId.Value;
SetupSheet(SpreadSheetId, sheetId, newSheetProperties);
return(sheetId);
}
Request HighlightDuplicateKeys(int sheetId, NewSheetProperties newSheetProperties)
{
return(new Request
{
// Highlight duplicates in the A(Key) field
AddConditionalFormatRule = new AddConditionalFormatRuleRequest
{
Rule = new ConditionalFormatRule
{
BooleanRule = new BooleanRule
{
Condition = new BooleanCondition
{
Type = "CUSTOM_FORMULA",
Values = new[] { new ConditionValue {
UserEnteredValue = "=countif(A:A,A1)>1"
} }
},
Format = new CellFormat {
BackgroundColor = UnityColorToDataColor(newSheetProperties.DuplicateKeyColor)
}
},
Ranges = new[]
{
new GridRange
{
SheetId = sheetId,
EndColumnIndex = 1
}
}
}
},
});
}
Request SetTitleStyle(int sheetId, NewSheetProperties newSheetProperties)
{
return(new Request
{
// Header style
RepeatCell = new RepeatCellRequest
{
Fields = "*",
Range = new GridRange
{
SheetId = sheetId,
StartRowIndex = 0,
EndRowIndex = 1,
},
Cell = new CellData
{
UserEnteredFormat = new CellFormat
{
BackgroundColor = UnityColorToDataColor(newSheetProperties.HeaderBackgroundColor),
TextFormat = new TextFormat
{
Bold = true,
ForegroundColor = UnityColorToDataColor(newSheetProperties.HeaderForegroundColor)
}
}
}
}
});
}
void SetupSheet(string spreadSheetId, int sheetId, NewSheetProperties newSheetProperties)
{
var requests = new List <Request>();
requests.Add(SetTitleStyle(sheetId, newSheetProperties));
if (newSheetProperties.FreezeTitleRowAndKeyColumn)
{
requests.Add(FreezeTitleRowAndKeyColumn(sheetId));
}
if (newSheetProperties.HighlightDuplicateKeys)
{
requests.Add(HighlightDuplicateKeys(sheetId, newSheetProperties));
}
if (requests.Count > 0)
{
SendBatchUpdateRequest(spreadSheetId, requests);
}
}
/// <summary>
/// Creates a new Google Spreadsheet.
/// </summary>
/// <param name="spreadSheetTitle">The title of the Spreadsheet.</param>
/// <param name="sheetTtitle">The title of the sheet(tab) that is part of the Spreadsheet.</param>
/// <param name="newSheetProperties"></param>
/// <param name="reporter">Optional reporter to display the progress and status of the task.</param>
/// <returns>Returns the new Spreadsheet and sheet id.</returns>
public (string spreadSheetId, int sheetId) CreateSpreadsheet(string spreadSheetTitle, string sheetTitle, NewSheetProperties newSheetProperties, ITaskReporter reporter = null)
{
if (newSheetProperties == null)
{
throw new ArgumentNullException(nameof(newSheetProperties));
}
try
{
reporter?.Start("Create Spreadsheet", "Preparing Request");
var createRequest = SheetsService.Service.Spreadsheets.Create(new Spreadsheet
{
Properties = new SpreadsheetProperties
{
Title = spreadSheetTitle
},
Sheets = new Sheet[]
{
new Sheet
{
Properties = new SheetProperties
{
Title = sheetTitle,
}
}
}
});
reporter?.ReportProgress("Sending create request", 0.2f);
var createResponse = ExecuteRequest <Spreadsheet, CreateRequest>(createRequest);
SpreadSheetId = createResponse.SpreadsheetId;
var sheetId = createResponse.Sheets[0].Properties.SheetId.Value;
reporter?.ReportProgress("Setting up new sheet", 0.5f);
SetupSheet(SpreadSheetId, sheetId, newSheetProperties);
reporter?.Completed(string.Empty);
return(SpreadSheetId, sheetId);
}
catch (Exception e)
{
reporter?.Fail(e.Message);
throw;
}
}
