Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Apps Script: how to get hyperlink from a cell where there is no formula

I have a sheet where hyperlink is set in cell, but not through formula. When clicked on the cell, in "fx" bar it only shows the value.

I searched on web but everywhere, the info is to extract hyperlink by using getFormula().

But in my case there is no formula set at all.

I can see hyperlink as you can see in image, but it's not there in "formula/fx" bar.

enter image description here

How to get hyperlink of that cell using Apps Script or any formula?

like image 427
Irfan Alam Avatar asked Dec 20 '18 06:12

Irfan Alam


People also ask

How do I extract a URL from a cell in Google Sheets?

Just type =””& and then the cell (no parenthesis) that you want to retrieve the link text from. Then hit Enter.

How do you reference a cell in Google script?

You do the proper thing - work on an array of the Range's values - as opposed to repeatedly call over the interface via getCell or similar just to check the value. To reference the cell whose value is values[r][c] , note that the Javascript array is 0-base and the Range is 1-base indexed. So A1 = [0][0].

How do I automate a hyperlink in Google Sheets?

Right click in your spreadsheet and select the Insert link option. Use the Ctrl + K keyboard shortcut (Cmd + K on a Mac). In the “Text” field that appears, type or edit the text you'd like displayed in the cell containing the link.


1 Answers

When a cell has only one URL, you can retrieve the URL from the cell using the following simple script.

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var url = sheet.getRange("A2").getRichTextValue().getLinkUrl(); //removed empty parentheses after getRange in line 2

Source: https://gist.github.com/tanaikech/d39b4b5ccc5a1d50f5b8b75febd807a6

like image 97
abonzer Avatar answered Sep 22 '22 17:09

abonzer