invitation india
How to add multiple links to a single cell of Google Sheet ?
In a recent update, Google Sheet are rolling out multiple hyperlinks in a single cell. Earlier, a cell had only one hyperlink. But now we can add multiple hyperlinks to a single cell of Google Sheet.
" Cells now support multiple links : You can link a portion of text or add multiple links in a cell by selecting the text and clicking the 'Insert Link' icon (or Ctrl/Cmd + K). "
How to Add Multiple Hyperlinks to a Single Cell of Google Sheet
- Double click on cell.
- Select Text, where you want to add link.
- Add link
- Use keyboard shortcut : Ctrl + K (Windows) OR Cmd + K (Mac).
- Choose "Insert" > "Insert Link" from the menu.
- Click on the "Insert Link" button from the toolbar.
How to Add Multiple Hyperlinks to a Single Cell with App Script
Google has come up with a way to format only certain part of the cell's text, using Google Script. So that we can apply multiple text styles to a cell value.
- newRichTextValue()
- RichTextValueBuilder
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); var cellValue = SpreadsheetApp.newRichTextValue() .setText("url1 and url2") .setLinkUrl(0, 4, "http://www.google.com/") .setLinkUrl(9, 13, "http://www.youtube.com/")
.build(); sheet.getRange("A1").setRichTextValue(cellValue);
How to Get the Hyperlinks of Cell from Google Sheets with App Script
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); var url = sheet.getRange()("A2").getRichTextValue().getLinkUrl();
Get the hyperlinks of cell from Google Sheets with app script and Sheet API
/**
* Get hyperlinks from cells.
* @param {string} spreadsheetId - The spreadsheet ID to request
* @param {string} ranges - The ranges to retrieve from the spreadsheet
* @return {Object[][]} 2D array of cell hyperlinks
*
* Enable Google Sheets API - in the script editor, select Resources > Advanced Google services.... > Google Sheets API (ON)
# Source : https://mashe.hawksey.info/2020/04/everything-a-google-apps-script-developer-wanted-to-know-about-reading-hyperlinks-in-google-sheets-but-was-afraid-to-ask/
*/
function getHyperLink() {
var spreadsheetId = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXX';
var ranges = 'G1:G';
//var ranges = 'G3' || 'A1:Z20';
var response = Sheets.Spreadsheets.get(spreadsheetId, {
includeGridData: true,
ranges: ranges
});
var rowData = response.sheets[0].data[0].rowData;
var hyperlinks = rowData.map(row => row.values.map(cell => cell.hyperlink || ''));
return hyperlinks;
}
function SaveToGoogleDrive() {
var ss = SpreadsheetApp.openById("XXXXXXXXXXXXXXXXXXXXXXX");
var sheet = ss.getSheetByName('Sheet1');
var Hyperlinks = getHyperLink();
for (var i = 0; i < 5; i++) {
var URL = Hyperlinks[i][0]; // row = i, col = 0
}
}Sources:
No comments: