CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaformula
Antonio Fergesi
Antonio Fergesi

Reputation: 171

How do I convert google smart chips into raw links?

After playing around with AppScript and formulas, I see plenty of ways to convert smart chips into raw text (eg, the title of the document), but no ways to harvest the link / raw URL.

For example, this extracts the link text:

function convertSmartChipsToLinks() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getDataRange();
  var numRows = dataRange.getNumRows();
  var values = dataRange.getValues();
  
  for (var i = 0; i < numRows; i++) {
    var cellValue = values[i][0];
    if (cellValue) {
      sheet.getRange(i + 1, 1).setValue(cellValue);
    }
  }
}

But when I try getRichTextValues() and getIdFromUrl(), it returns null for smart chips.

Is there any known solution?

Upvotes: 0

Views: 1804

Answers (1)

sidverma
sidverma

Reputation: 1185

Couldn't see any direct solution to this problem. I think you need to re-think and write your own solution based on use-cases.

Try this modified solution of your code:

function convertSmartChipsToLinks() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getDataRange();
  var numRows = dataRange.getNumRows();
  var values = dataRange.getValues();
  
//Inside this for loop: loops through each cell in the data range 
  for (var i = 0; i < numRows; i++) {
    var cell = sheet.getRange(i + 1, 1);
    var richTextValue = cell.getRichTextValue();
    var url = '';
// another for-loop: the text elements in the rich text object and checks if each one is a link URL 
    for (var j = 0; j < richTextValue.getNumTextElements(); j++) {
      var textElement = richTextValue.getTextElement(j);
      if (textElement.isLinkUrl()) {
        url = textElement.getLinkUrl();
        break;
      }
    }
    if (url) {
      cell.setValue(url);
    }
  }
}

Upvotes: 0

Related Questions