How to add hyperlink in table (or array) in a Google Doc using Google DOC API Script?-CodePudding

I am having the following code for appending table in Google Docs.

var sss = SpreadsheetApp.openById('id of spreadsheet'); 
var rawData =  sss.getDataRange().getValues()    

var data = []  
for (var i = 0; i< rawData.length; i  ){
tempData=[]
tempData=[rawData[i][1],rawData[i][2],rawData[i][3]]
data.push(tempData)

}
var someDoc = DocumentApp.openById(someId);
var body = someDoc.getBody();

body.appendTable(data).editAsText().setBold(false);

This code works fine. The problem is that there is url in rawdata[i][3]. It gets displayed in Google doc as plain text. How can I convert it into hyperlink? It would be even better if it is possible to write it as body.appendParagraph("my link").setLinkUrl("http://www.google.com"). The problem is that it is in an array, not in paragraph.

CodePudding user response：

I believe your goal is as follows.

You want to put a table to Google Document by retrieving the values from Google Spreadsheet.
In your Spreadsheet, the column "D" has the hyperlinks. And, you want to set the value as the hyperlink.

In this case, how about the following modification?

Modified script:

var sss = SpreadsheetApp.openById('id of spreadsheet'); 
var rawData = sss.getDataRange().getValues();
var data = []
for (var i = 0; i < rawData.length; i  ) {
  tempData = []
  tempData = [rawData[i][1], rawData[i][2], rawData[i][3]]
  data.push(tempData)
}
var someDoc = DocumentApp.openById(someId);
var body = someDoc.getBody();

// I modified below script.
var table = body.appendTable(data);
for (var r = 0; r < table.getNumRows(); r  ) {
  var obj = table.getCell(r, 2).editAsText();
  var text = obj.getText();
  if (/^https?:\/\//.test(text)) obj.setLinkUrl(text);
}
table.editAsText().setBold(false);

When this script is run, a table is put using the values retrieved from Spreadsheet. And, about the column "C" of the table, the text is changed to the hyperlink.

Note:

This modified script supposes that your values of column "D" are like https://### and http://###. Please be careful about this.

If you want to give the specific text (for example, click here) with the hyperlink, please modify as follows.

From

  if (/^https?:\/\//.test(text)) obj.setLinkUrl(text);

  if (/^https?:\/\//.test(text)) obj.setText("click here").setLinkUrl(text);

References:

CodePudding user response：

You can try the following to make all URLs in a Google Doc clickable:

function urlClickable() {
  var urlRegex = 'http[s]?:\/\/[^ ] ';
  var doc = DocumentApp.openById('yourDocId');
  var body = doc.getBody();
  var urlElement = body.findText(urlRegex);  

  while (urlElement != null) {    
    var text = urlElement.getElement().asText();

    var startOffset = urlElement.getStartOffset();
    var endOffset = urlElement.getEndOffsetInclusive();

    text.setLinkUrl(startOffset, endOffset, getUrl(text.getText()));

    urlElement = body.findText(urlRegex, urlElement);
  }
}

function getUrl(text) {
  var startOffset = text.indexOf('http');
  var endOffset = text.indexOf(' ', startOffset);

  if (endOffset === -1) {
    endOffset = text.length;
  }
  return text.substring(startOffset, endOffset);
}