Getting cell text formatting (markup?)

[Gallouche]

Hi there,

I'm trying to get the markup from a text cell. For example, if I have a bold word in a sentence write in a cell, I would like to retrieve this information. Is there any way to do this ?

Thanks a lot by advance.

Théo

[JustinBeckwith]

Greetings! Can you share:

• Which API you're trying too use
• Any code you've already tried?

[Gallouche]

Hi, thanks for your answer !

• I'm using the googleapis@39 npm package
• I just tried to get cell values with the function values.get() hoping to have those informations.

To complete my case, I want to use a spreadsheet as a CMS in order to provide data for some tables in my application. So I need to know the formatting if there is some.

Thanks !

[sofisl]

Hi @Gallouche,

Thanks for your question!

I believe you can use some code like this to get the format information for a spreadsheet:

function execute() {
         return gapi.client.sheets.spreadsheets.get({
        "spreadsheetId": //Your spreadsheet id, not sheet Id
        "includeGridData": true,
        "ranges": [
               //You can use A1 notation here to specify the range
        ]
 })
     .then (function(response) { 
           console.log("Response", response);
    },
   function(err) { console.error("Execute error", err); });

}

You'll get a response like includes something like this for your cells with content

{
      "data": [
        {
          "rowData": [
            {
              "values": [
                {
                  "userEnteredValue": {
                    "stringValue": "HELLO"
                  },
                  "effectiveValue": {
                    "stringValue": "HELLO"
                  },
                  "formattedValue": "HELLO",
                  "effectiveFormat": {
                    "backgroundColor": {
                      "red": 1,
                      "green": 1,
                      "blue": 1
                    },
                    "padding": {
                      "top": 2,
                      "right": 3,
                      "bottom": 2,
                      "left": 3
                    },
                    "horizontalAlignment": "LEFT",
                    "verticalAlignment": "BOTTOM",
                    "wrapStrategy": "OVERFLOW_CELL",
                    "textFormat": {
                      "foregroundColor": {},
                      "fontFamily": "Arial",
                      "fontSize": 10,
                      "bold": false,
                      "italic": false,
                      "strikethrough": false,
                      "underline": false
                    },
                    "hyperlinkDisplayType": "PLAIN_TEXT"
                  }
                }
              ]
            },
            {
              "values": [
                {
                  "userEnteredValue": {
                    "stringValue": "hello"
                  },
                  "effectiveValue": {
                    "stringValue": "hello"
                  },
                  "formattedValue": "hello",
                  "effectiveFormat": {
                    "backgroundColor": {
                      "red": 1,
                      "green": 1,
                      "blue": 1
                    },
                    "padding": {
                      "top": 2,
                      "right": 3,
                      "bottom": 2,
                      "left": 3
                    },
                    "horizontalAlignment": "LEFT",
                    "verticalAlignment": "BOTTOM",
                    "wrapStrategy": "OVERFLOW_CELL",
                    "textFormat": {
                      "foregroundColor": {},
                      "fontFamily": "Arial",
                      "fontSize": 10,
                      "bold": false,
                      "italic": false,
                      "strikethrough": false,
                      "underline": false
                    },
                    "hyperlinkDisplayType": "PLAIN_TEXT"
                  }
                }
              ]
            },
            {
              "values": [
                {
                  "userEnteredValue": {
                    "stringValue": "hello"
                  },
                  "effectiveValue": {
                    "stringValue": "hello"
                  },
                  "formattedValue": "hello",
                  "userEnteredFormat": {
                    "textFormat": {
                      "bold": true
                    }
                  },
                  "effectiveFormat": {
                    "backgroundColor": {
                      "red": 1,
                      "green": 1,
                      "blue": 1
                    },
                    "padding": {
                      "top": 2,
                      "right": 3,
                      "bottom": 2,
                      "left": 3
                    },
                    "horizontalAlignment": "LEFT",
                    "verticalAlignment": "BOTTOM",
                    "wrapStrategy": "OVERFLOW_CELL",
                    "textFormat": {
                      "foregroundColor": {},
                      "fontFamily": "Arial",
                      "fontSize": 10,
                      "bold": true,
                      "italic": false,
                      "strikethrough": false,
                      "underline": false
                    },
                    "hyperlinkDisplayType": "PLAIN_TEXT"
                  }
                }
              ]
            },
            {
              "values": [
                {
                  "userEnteredValue": {
                    "stringValue": "hello"
                  },
                  "effectiveValue": {
                    "stringValue": "hello"
                  },
                  "formattedValue": "hello",
                  "userEnteredFormat": {
                    "textFormat": {
                      "strikethrough": true,
                      "underline": false
                    }
                  },
                  "effectiveFormat": {
                    "backgroundColor": {
                      "red": 1,
                      "green": 1,
                      "blue": 1
                    },
                    "padding": {
                      "top": 2,
                      "right": 3,
                      "bottom": 2,
                      "left": 3
                    },
                    "horizontalAlignment": "LEFT",
                    "verticalAlignment": "BOTTOM",
                    "wrapStrategy": "OVERFLOW_CELL",
                    "textFormat": {
                      "foregroundColor": {},
                      "fontFamily": "Arial",
                      "fontSize": 10,
                      "bold": false,
                      "italic": false,
                      "strikethrough": true,
                      "underline": false
                    },
                    "hyperlinkDisplayType": "PLAIN_TEXT"
                  }
                }
              ]
            }
          ],
          "rowMetadata": [
            {
              "pixelSize": 21
            },
            {
              "pixelSize": 21
            },
            {
              "pixelSize": 21
            },
            {
              "pixelSize": 21
            },
            {
              "pixelSize": 21
            }
          ],
          "columnMetadata": [
            {
              "pixelSize": 100
            }
          ]
        }
      ]
    }
  ],
}

You can then check for content format specifications in the response.

This code is in javascript so it uses the gapi library for authentication. You can search for node-specific examples in this repo: https://github.com/googleapis/google-api-nodejs-client/tree/master/samples/sheets.

Hope this helps! Feel free to reopen if this wasn't what you were looking for!