Google Sheets setUnderline (offsetBegin, offsetEnd)

Question

Does anyone know of a way to underline a portion of text in a Google Sheets cell? I am really looking for a function like my Title to this query.

I have played with .getDisplayValue, .getValue for the Range class and have tried to see if the Doc Text Class might be reachable from Google sheets with no success. I do know I can do this directly from Google Sheets but need to this capability from Google Apps Scripts (GAS).

I also know that this capability in the actual spreadsheet editor is new and may be that the Apps Script needs to catch up.

Thank you for any responses there may be.

Terry

Anton Dementiev · Accepted Answer · 2018-03-15T07:03:11.127

4

Try the function below for applying underline to your string. Unfortunately, Google Sheets don't seem to support continuous underline.

function underline(string, start, end) {

  start = start || 0;
  end = end || string.length;

  var res = "";

  for (var i=start; i < end; i++) {

    res += string.charAt(i) + "\u0332";

  }

  return res.toString();

}

edited Mar 15 '18 at 07:03

answered Mar 12 '18 at 23:54

Anton Dementiev

5,451
4
20
32

I think that your answer is nice workaround. I would like to upvote this answer. From your script, I thought that when ``return string.slice(0, start) + res.toString() + string.slice(end);`` is used as a return value, ``=underline("abcdefg", 2, 5)`` will show ``abc̲d̲e̲fg``. If this was not useful, please ignore. – Tanaike Mar 13 '18 at 00:19
I incorporated this function into my script, I noticed however if its in all caps the underline becomes broken under each letter. Also if there is a space the underline bumps down lower slightly than the rest of the underlining. Is there anyway to keep this from happening? – VikingScript Mar 06 '19 at 16:03

score 3 · Answer 2 · answered Jul 21 '18 at 11:36

You can now setUnderlines in GAS.

function underline(startOffset,endOffset){
startOffset = startOffset || 1;
endOffset = endOffset || 3;
  var rng = SpreadsheetApp.getActiveSheet().getRange("A1");
  var val = rng.getValue().toString();
  var rich = SpreadsheetApp.newRichTextValue(); //new RichText
  rich.setText(val); //Set Text value in A1 to RichText
    var style = SpreadsheetApp.newTextStyle(); // Create a new text style
    style.setUnderline(true);
    var buildStyle = style.build(); 
    rich.setTextStyle(startOffset,endOffset,buildStyle); // set this text style to the offset character range and save it to Rich text     
  var format = rich.build()
  rng.setRichTextValue(format); //Set the final RichTextValue back to A1
}

score 2 · Answer 3 · answered Mar 13 '18 at 00:02

See https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#CellData and https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#TextFormatRun for how to do this.

See https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#RepeatCellRequest for how to apply the formatting to multiple cells at once, if you need to do that.

I personally haven't tried the TextFormatRun capabilities, but as a more generic example, here's how you change the color of the background and foreground, alignment, and boldness of a row:

def sheets_batch_update(SHEET_ID,data):
    print ( ("Sheets: Batch update"))
    service.spreadsheets().batchUpdate(spreadsheetId=SHEET_ID,body=data).execute() #,valueInputOption='RAW'

data={
  "requests": [

#format header row
    {
      "repeatCell": {
        "range": {
          "sheetId": all_sheets['Users'],
          "startRowIndex": 0,
          "endRowIndex": 1
#           "startColumnIndex": 0,
#           "endColumnIndex": 6
        },
        "cell": {
          "userEnteredFormat": {
            "backgroundColor": {
              "red": 0.4,
              "green": 0.4,
              "blue": 0.4
            },
            "horizontalAlignment" : "LEFT",
            "textFormat": {
              "foregroundColor": {
                "red": 1.0,
                "green": 1.0,
                "blue": 1.0
              },
              #"fontSize": 12,
              "bold": True
            }
          }
        },
        "fields": "userEnteredFormat(backgroundColor,textFormat,horizontalAlignment)"
      }
    },


  ]
}

sheets_batch_update(SHEET_ID, data)

I wrote an example using TextFormatRun here: https://stackoverflow.com/a/49289754/9337071 — tehhowch, Mar 15 '18 at 12:34

Google Sheets setUnderline (offsetBegin, offsetEnd)

3 Answers3

Linked