Grabbing URL from @ formatted cell, Google Scripts API

Question

screenshot of sheet formatting

Hi all, I am trying to make a script which iterates over the cells in a spreadsheet and creates a set of folders populated with files in accordance to the specified structure. One such task is creating shortcuts, which are linked in the sheet cell. However, I am unable to get the URL associated with the shortcut.

ss = SpreadsheetApp.getActiveSpreadsheet()
var sht = ss.getSheetByName('Structure')

sht.getDataRange().setShowHyperlink(true);

nCols = sht.getLastColumn();
nRows = sht.getLastRow();


for(row=1; row < nRows+1; row++){
  for(col=1; col < nCols+1; col++){
     cell = sht.getRange(row, col)
     Logger.log(cell.getValue()+', '+cell.getRichTextValue().getLinkUrl());

I've tried a variety of strategies including .getRichTextValue().getLinkUrl() and other regular expression techniques. The main struggle has been that the cell's value is considered a string, which is the title of the document, losing the hyperlink data.

execution log

From Sheets, I can do a Data Extraction to get the URL. However, I want to do this from the script end and without populating a new cell.

data extraction approach

Answer 1

I believe your goal is as follows.

• You want to retrieve the URL of the Spreadsheet from the smart chips of a Spreadsheet using Google Apps Script.

In this case, how about the following sample script?

Unfortunately, in the current stage, the smart chips cannot be managed by Google Apps Script and APIs. I believe that this will be resolved in the future update. But, in the current stage, in order to achieve your goal, it is required to use a workaround. In this answer, I would like to propose a workaround. The flow of this workaround is as follows. The basic logic of this workaround is from this report (Author: me).

1. Convert Google Spreadsheet to Microsoft Excel.
2. Convert Microsoft Excel to Google Spreadsheet.

By this flow, the smart chips of the Spreadsheet are changed to text including a hyperlink. In this case, the hyperlinks can be retrieved by Google Apps Script. The sample script of this workaround is as follows.

Sample script:

In this sample script, Drive API is used. So, before you run this script, please enable Drive API at Advanced Google services.

function myFunction() {
  const sheetName = "Sheet1"; // Please set your sheet name.
  const column = 3; // From your showing image, I understood that the smart chips are put into column "C".

  const ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
  const url = `https://docs.google.com/spreadsheets/export?exportFormat=xlsx&id=${ssId}`;
  const blob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() }, }).getBlob();
  const { id } = Drive.Files.insert({ title: "temp", mimeType: MimeType.GOOGLE_SHEETS }, blob);
  const file = DriveApp.getFileById(id);
  const convertedSS = SpreadsheetApp.open(file);
  const sheet = convertedSS.getSheetByName(sheetName);
  const values = sheet.getRange(2, column, sheet.getLastRow() - 1).getRichTextValues().map(([c]) => [c.getText(), c.getLinkUrl()]);
  file.setTrashed(true); // or Drive.Files.remove(id);

  console.log(values);
}

Testing:

When this script is run to the Spreadsheet of your showing image, the following result is obtained.

[
  ["spreadsheet title 1","https://docs.google.com/spreadsheets/u/0/d/{spreadsheetID1}/edit"],
  ["spreadsheet title 2","https://docs.google.com/spreadsheets/u/0/d/{spreadsheetID2}/edit"],
  ["spreadsheet title 3","https://docs.google.com/spreadsheets/u/0/d/{spreadsheetID3}/edit"],
  ,
  ,
  ,
]

Note:

• In this sample script, the values are retrieved from column "C" of "Sheet1". Please modify them to your actual situation.

Reference:

• Method: files.insert