CODEWITHSUNDEEP
rubygoogle-sheets-apigoogle-workspace
Will
Will

Reputation: 845

How to access celldata objects in sheets api

I'm working on a google sheets integration project where I'd like to add formatted text to cells (bold, italic). This needs to be for only part of the cell (e.g. only some of the text in the cell is bold ) I can see that this can be done though the CellData object, documented in the sheets api here:

CellData

But I can't work out how to get an instance of these objects. I'm using the sheets service to successfully get a SpreadSheet, Sheet and ValueRange objects, but I can't work out how to get through to the cell data objects themselves to use these methods.

Upvotes: 0

Views: 1347

Answers (1)

Tanaike
Tanaike

Reputation: 201513

  1. When a part of value of a cell has several formats, you want to retrieve the formats.
  2. You want to put a value with several formats to a cell.

I understand your question as above. If my understanding is correct, how about these samples?

1. Retrieve value

When a part of value of a cell has several formats like below image,

enter image description here

the script for retrieving the values with the formats is as follows.

Sample script:

This sample script retrieves the value from the cell "A1" of "Sheet1".

spreadsheet_id = '### spreadsheet ID ###'
ranges = ['Sheet1!A1']
fields = 'sheets(data(rowData(values(textFormatRuns,userEnteredValue))))'
response = service.get_spreadsheet(spreadsheet_id, ranges: ranges, fields: fields)

Result:

{
  "sheets": [
    {
      "data": [
        {
          "rowData": [
            {
              "values": [
                {
                  "userEnteredValue": {
                    "stringValue": "abcdefg"
                  },
                  "textFormatRuns": [
                    {
                      "format": {}
                    },
                    {
                      "format": {
                        "fontSize": 24,
                        "foregroundColor": {
                          "red": 1
                        },
                        "bold": true
                      },
                      "startIndex": 2
                    },
                    {
                      "format": {},
                      "startIndex": 5
                    }
                  ]
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}

2. Put value

When a value with several formats is put to a cell, the script is as follows.

Sample script:

This sample script puts the value to the cell "B1" of "Sheet1". As a sample, update_cells is used for this situation.

spreadsheet_id = '### spreadsheet ID ###'
requests = {requests: [
  update_cells: {
    fields: 'userEnteredValue,textFormatRuns',
    range: {sheet_id: 0, start_row_index: 0, end_row_index: 1, start_column_index: 1, end_column_index: 2},
    rows: [{values: [{user_entered_value: {
          string_value: 'abcdefg'},
          text_format_runs: [{format: {}}, {format: {font_size: 24, foreground_color: {red: 1}, bold: true}, start_index: 2}, {format:{}, start_index: 5}]
    }]}]
  }
]}
response = service.batch_update_spreadsheet(spreadsheet_id, requests, {})
  • About sheet_id: 0, if you want to other sheet, please modify it.

Result:

enter image description here

Note:

  • These sample scripts supposes that your environment can use Sheets API.
  • These are simple samples. So please modify them to your situation.

References:

Upvotes: 3

Related Questions