/// <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; } }