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

Answer 1

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();

}

Answer 2

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
}

Answer 3

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)