How do I change a cell font in google sheets api v4 in C#?

Question

Is it possible? I can change the values by updating the ValueRange object as in the code above but couldnt find a way to change the format.

  ValueRange l_oValueRange = new ValueRange();
  List<object> l_olCellsNewValue = new List<object>() { DateTime.UtcNow.ToString("dd'/'MM'/'yyyy HH:mm:ss") };
  l_oValueRange.Values = new List<IList<object>> { l_olCellsNewValue };

  SpreadsheetsResource.ValuesResource.UpdateRequest l_oUpdate = service.Spreadsheets.Values.Update(
                                                                l_oValueRange,
                                                                spreadsheetId,
                                                                "A60");
  l_oUpdate.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
  UpdateValuesResponse l_oResponse = l_oUpdate.Execute();

If you check the [documentation](https://developers.google.com/sheets/samples/conditional-formatting#add_date_and_text_conditional_formatting_rules_to_a_range) of the Sheets API, the only thing that you can do with the cell font is just making it **BOLD** and *italicize*. For more information, check the [conditional formatting](https://developers.google.com/sheets/guides/conditional-format) to know the only formatting available in the Sheets API. — KENdi, Dec 04 '16 at 11:45

score 6 · Answer 1 · answered Mar 21 '17 at 12:08

string spreadsheetId = "1DD3zfGe6.......UtENHhnBwz0CA";

//get sheet id by sheet name
Spreadsheet spr = service.Spreadsheets.Get(spreadsheetId).Execute();
Sheet sh = spr.Sheets.Where(s => s.Properties.Title == sheetName).FirstOrDefault();
int sheetId = (int)sh.Properties.SheetId;

//define cell color
var userEnteredFormat = new CellFormat()
{
    BackgroundColor = new Color()
    {
        Blue = 0,
        Red = 1,
        Green = (float)0.5,
        Alpha = (float)0.1
    },
    TextFormat = new TextFormat()
    {
        Bold = true,
        FontFamily = "your font family",
        FontSize = 12
    }
};
BatchUpdateSpreadsheetRequest bussr = new BatchUpdateSpreadsheetRequest();

//create the update request for cells from the first row
var updateCellsRequest = new Request()
{
    RepeatCell = new RepeatCellRequest()
    {
        Range = new GridRange()
        {
            SheetId = sheetId,
            StartColumnIndex = 0,
            StartRowIndex = 0,
            EndColumnIndex = 28,
            EndRowIndex = 1
        },
        Cell = new CellData()
        {
            UserEnteredFormat = userEnteredFormat
        },
        Fields = "UserEnteredFormat(BackgroundColor,TextFormat)"
    }
};
bussr.Requests = new List<Request>();
bussr.Requests.Add(updateCellsRequest);            
bur = service.Spreadsheets.BatchUpdate(bussr, spreadsheetId);
bur.Execute();

This works great, thank you for posting a code example on formatting. I've been searching for this. — badMonkey, Apr 17 '23 at 14:05

vomnes · Answer 2 · 2019-02-01T13:03:44.490

You need to have an other update (Requests) using BatchUpdate (I'm using Golang):

func (s *Settings) UpdateSheet(req *sheets.Request)(*sheets.BatchUpdateSpreadsheetResponse, error) {
    update := sheets.BatchUpdateSpreadsheetRequest{
      Requests: []*sheets.Request{req},
    }
    return s.service.Spreadsheets.BatchUpdate(s.spreadsheetId, &update).Context(s.ctx).Do()
}

The request must contain FontFamily, there aren't lots of docs but it must contain a string under this format "arial,sans,sans-serif" :

req := &sheets.Request{
    RepeatCell: &sheets.RepeatCellRequest{
       Range: &sheets.GridRange{
           SheetId:        sheetId,
           StartRowIndex:  startRow,
           EndRowIndex:    endRow,
           EndColumnIndex: endCol,
       },
       Cell: &sheets.CellData{
           UserEnteredFormat: &sheets.CellFormat{
               TextFormat: &sheets.TextFormat{
                   FontFamily:      "arial,sans,sans-serif", // The font family
               },
           },
       },
    },
}

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#textformat

TextFormat: {
  "foregroundColor": {
    object(Color)
  },
  "fontFamily": string,
  "fontSize": number,
  "bold": boolean,
  "italic": boolean,
  "strikethrough": boolean,
  "underline": boolean,
}

Keywords: Google Sheets API v4 Golang

About the only docs I could find for golang on doing style. Adding keyword Google Sheets API v4 Golang to hopefully find this in google easier for others — Jaybeecave, May 12 '18 at 00:53
How you exactly determine font family to use in? I tried to simply paste font name and paste font family defined in Google Sheets CSS. But it had no effect — Nikita Romaniuk, Feb 27 '20 at 18:48
I have found that you can only use the fonts that are present in the [Google Fonts Repository](https://github.com/google/fonts) or https://fonts.google.com/. If it still doesn't work you can maybe find more informations on the questions about FamilyFont from the other Google APIs for example this one : https://stackoverflow.com/questions/2455020/font-family-selection-with-google-charts — vomnes, Apr 24 '20 at 10:10

How do I change a cell font in google sheets api v4 in C#?

2 Answers2