CODEWITHSUNDEEP
javagoogle-sheetsgoogle-sheets-api
Peter Penzov
Peter Penzov

Reputation: 1678

Set custom column width size in google sheets

I'm using this Java code to generate Google sheets data from Java application.

List < RowData > rowDataValues = new ArrayList < > ();

List < CellData > headerValues = new ArrayList < > ();
headerValues.add(new CellData().setNote("ID")
    .setUserEnteredValue(new ExtendedValue()
        .setStringValue("#")).setUserEnteredFormat(myFormat));
.setStringValue("Environment")).setUserEnteredFormat(myFormat));

.........

headerValues.add(new CellData()
    .setUserEnteredValue(new ExtendedValue()
        .setStringValue("Name")).setUserEnteredFormat(myFormat));
RowData setHeaderValues = new RowData();
setHeaderValues.setValues(headerValues);
rowDataValues.add(setHeaderValues);

requests.add(new Request()
    .setUpdateCells(new UpdateCellsRequest()
        .setStart(new GridCoordinate()
            .setSheetId(randomSheetId)
            .setRowIndex(0)
            .setColumnIndex(0))
        .setRows(rowDataValues)
        .setFields("*")));

BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest().setRequests(requests);

BatchUpdateSpreadsheetResponse response = service.spreadsheets().batchUpdate(spreadsheetId, body).execute();

How I can set custom width for each column?

Update:

This is what i've tried so far :

new UpdateDimensionPropertiesRequest().setRange(
        new DimensionRange()
        .setDimension("COLUMNS")
        .setStartIndex(0).setEndIndex(1)
    )
    .setProperties(new DimensionProperties().setPixelSize(400)).setFields("pixelSize"));

Upvotes: 1

Views: 1791

Answers (2)

Andrew
Andrew

Reputation: 11

Please take a look into dev documentation:

{
  "requests": [
    {
      "updateDimensionProperties": {
        "range": {
          "sheetId": sheetId,
          "dimension": "COLUMNS",
          "startIndex": 0,
          "endIndex": 1
        },
        "properties": {
          "pixelSize": 160
        },
        "fields": "pixelSize"
      }
    }
  ]
}

Request body from the example for column width could be translated into the next Java code:

public void updateFirstColumnWidth(Sheets sheetService, String sheetId) throws IOException {
    final Spreadsheet spreadsheet = sheetService.spreadsheets()
        .get(sheetId)
        .execute();
    final var sheets = spreadsheet.getSheets();
    final var sheet = sheets.get(0); // get your specific sheet
    final int sheetUuid = sheet.getProperties().getSheetId();
    final var batchRequest = new BatchUpdateSpreadsheetRequest();
    List<Request> requests = List.of(getColumnWidthRequest(sheetUuid, 160));
    batchRequest.setRequests(requests);
    final BatchUpdateSpreadsheetResponse response = sheetService.spreadsheets()
        .batchUpdate(sheetId, batchRequest)
        .execute();
}

private Request getColumnWidthRequest(int sheetUuid, int width) {
    return new Request()
        .setUpdateDimensionProperties(
            new UpdateDimensionPropertiesRequest()
                .setRange(
                    new DimensionRange()
                        .setSheetId(sheetUuid)
                        .setDimension("COLUMNS")
                        .setStartIndex(0)
                        .setEndIndex(1)
                )
                .setProperties(new DimensionProperties().setPixelSize(width))
                .setFields("pixelSize")
        );
}

Upvotes: 1

&#214;mer Erden
&#214;mer Erden

Reputation: 8793

You need to create UpdateDimensionPropertiesRequest. In your case you can use this sample code, which increases the size of first column (startIndex = 0, endIndex = 1) . 

requests.add(new Request().setUpdateDimensionProperties(
    new UpdateDimensionPropertiesRequest()
    .setRange(
        new DimensionRange()
        .setSheetId(randomSheetId)
        .setDimension("COLUMNS")
        .setStartIndex(0)
        .setEndIndex(1)
)
.setProperties(new DimensionProperties().setPixelSize(400)).setFields("pixelSize"))));

In here i used setDimension("COLUMNS") to change column(s) width, it is possible to change row(s) height by using setDimension("ROWS").

Additional problem from @PeterPenzov 's comment :

I get "Invalid requests.updateDimensionProperties: No grid with id: 0",

You'll get this error when your sheetId is not set properly.

From API v4 documentation SheetId is ;

Individual sheets in a spreadsheet have titles (which must be unique) and IDs. The sheetId is used frequently in the Sheets API to specify which sheet is being read or updated

So you need to set sheetId of DimensionRange Object. In your case you need to use your sheetId as randomSheetId(i've updated the code above).

Spreadsheet spreadsheet = service.spreadsheets().get(spreadsheetId).execute();
spreadsheet.getSheets().stream()
                       .map(s->s.getProperties().getSheetId())
                       .forEach(System.out::println);

Upvotes: 2

Related Questions