Set cell background color and text size using Google Java API

Question

I want to set Spreadsheet cell background color and text size. I use this Java code to set the text into the cell but I can't find a solution how to set the style.

CellData setUserEnteredValue = new CellData()
            .setUserEnteredValue(new ExtendedValue()
                .setStringValue("cell text"));

Is there any solution?

Answer 1

I had to go through allot of useless answers, but this worked for me. Here you go:

requests.add(new Request()
                .setRepeatCell(new RepeatCellRequest()
                        .setCell(new CellData()
                                .setUserEnteredValue( new ExtendedValue().setStringValue("cell text"))
                                .setUserEnteredFormat(new CellFormat()
                                        .setBackgroundColor(new Color()
                                                .setRed(Float.valueOf("1"))
                                                .setGreen(Float.valueOf("0"))
                                                .setBlue(Float.valueOf("0"))
                                        )
                                        .setTextFormat(new TextFormat()
                                                .setFontSize(15)
                                                .setBold(Boolean.TRUE)
                                        )
                                )
                        )
                        .setRange(new GridRange()
                                .setSheetId(sheetID)
                                .setStartRowIndex(1)
                                .setEndRowIndex(0)
                                .setStartColumnIndex(0)
                                .setEndColumnIndex(1)
                        )
                        .setFields("*")
                )
        );

Answer 2

AFAIK this is not possible in the Java Spreadsheet API, you instead have to use the Apps Script; https://developers.google.com/apps-script/reference/spreadsheet/

From their documentation, how to set the background;

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var range = sheet.getRange("B2:D5");
range.setBackground("red");

and how to set the font size;

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var cell = sheet.getRange("B2");
cell.setFontSize(20);

---

Update See flo5783's answer below, v4 now offers the ability to do this.

Answer 3

Looks like there is a class designed exactly for this: CellFormat

In particular these following methods:

public CellFormat setBackgroundColor(ColorbackgroundColor)

and

public CellFormat setTextFormat(TextFormattextFormat)

I haven't coded in Java in ages so I won't try to give you a working code example, but I think you'll be able to figure it out easily from this.

EDIT: Here's a basic example starting from your code:

CellData setUserEnteredValue = new CellData()
            .setUserEnteredValue(new ExtendedValue()
                .setStringValue("cell text"));

CellFormat myFormat = new CellFormat();
myFormat.setBackgroundColor(new Color().setRed(1)); // red background
myFormat.setTextFormat(new TextFormat().setFontSize(16)); // 16pt font

setUserEnteredValue.setUserEnteredFormat(myFormat);

Answer 4

// For handling single cells this is how you do it, where j and k are row and columns in g sheet .

CellData setUserEnteredValue =new CellData()
                .setUserEnteredValue(new ExtendedValue()
                        .setStringValue(maxs));
    List<CellData> values1.add(setUserEnteredValue);
        requests.add(new Request()
                .setUpdateCells(new UpdateCellsRequest()
                        .setStart(new GridCoordinate()
                                .setRowIndex(j)
                                .setColumnIndex(k))
                        .setRows(Arrays.asList(
                                new RowData().setValues(values1)))
                        .setFields("userEnteredValue,userEnteredFormat.backgroundColor")));
        

        CellFormat myFormat = new CellFormat();
        myFormat.setBackgroundColor(new Color().setRed(Float.valueOf("1"))); // red background
        setUserEnteredValue.setUserEnteredFormat(myFormat);

Answer 5

You can't change the background color or the font size on a CellData object. You instead need to iterate over some cell ranges. From there, you can set the background color based on the cell value. You can then make your code dependable, like a 2-step process. From another answer on SO:

//Sets the row color depending on the value in the "Status" column.
function setRowColors() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var statusColumnOffset = getStatusColumnOffset();

  for (var i = range.getRow(); i < range.getLastRow(); i++) {
    rowRange = range.offset(i, 0, 1); //Play with this range to get your desired columns.
    status = rowRange.offset(0, statusColumnOffset).getValue(); //The text value we need to evaluate.
    if (status == 'Completed') {
      rowRange.setBackgroundColor("#99CC99");
    } else if (status == 'In Progress') {
      rowRange.setBackgroundColor("#FFDD88");    
    } else if (status == 'Not Started') {
      rowRange.setBackgroundColor("#CC6666");          
    }
  }
}