Description
Now, Google Docs and Google Sheets can insert smart chips. Smart chips are very useful for easily inserting information like users, maps, files, and so on. However, unfortunately, at the current stage, smart chips cannot be directly managed using Google Apps Script. Specifically, the information within smart chips cannot be directly retrieved by Google Apps Script. Although I believe this will be resolved in a future update, there might be cases where you want to retrieve information from smart chips using Google Apps Script. This report introduces a workaround for achieving this.
Workaround
The flow of workaround in this report is as follows.
When Google Docs and Google Sheets, including smart chips, are converted to DOCX and XLSX formats, the smart chips are converted to text with hyperlinks. This allows the text and hyperlinks of the smart chips to be retrieved. If the desired values can be directly obtained from DOCX and XLSX data, retrieval can also be directly achieved. Google Apps Script provides the Google Document service (DocumentApp) and the Google Spreadsheet service (SpreadsheetApp). Using these services, values can be simply obtained from Google Docs and Google Sheets using the native methods of Google Apps Script. Alternatively, this can be achieved by converting DOCX and XLSX data to Google Docs and Google Sheets, respectively.
Samples
Sample 1
Retrieving Values of Calendar Events of Smart Chips on Google Document using Google Apps Script
Sample 2
Retrieving Values of Dropdown List of Smart Chips on Google Document using Google Apps Script
Sample 3
Retrieving the text and the hyperlinks from Google Maps Smart Chips
Sample script
Please copy and paste the following script into the Google Sheets script editor. Then, set the cellA1Notation variable. In this example, it assumes the smart chip is located in cell ‘Sheet1’!A1.
Also, enable the Drive API v3 in Advanced Google services. Ref
function myFunction() {
const cellA1Notation = "'Sheet1'!A1"; // Please set your range.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const xlsxUrl = Drive.Files.get(ss.getId(), { fields: "exportLinks" })
.exportLinks[MimeType.MICROSOFT_EXCEL];
const xlsxBlob = UrlFetchApp.fetch(xlsxUrl, {
headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
}).getBlob();
const tempSSId = Drive.Files.create(
{ name: "temp", mimeType: MimeType.GOOGLE_SHEETS },
xlsxBlob
).id;
const tempSS = SpreadsheetApp.openById(tempSSId);
const range = tempSS.getRange(cellA1Notation);
const text = range.getValue();
const url = range.getRichTextValue().getLinkUrl();
console.log({ text, url });
Drive.Files.remove(tempSSId); // If you want to delete the temporal spreadsheet, please use this line.
}
Testing
When the following sample sheet is used (the cell A1 is on “Sheet1”.),
the following result is shown in the console.
{
"text": "Tokyo Station",
"url": "https://www.google.com/maps/place/Tokyo+Station/data=!4m2!3m1!19sChIJC3Cf2PuLGGAROO00ukl8JwA"
}