This video greatly helped sir, saved a ton of time. I don't understand anything in these scripts, just copied your code and it magically works. THANK YOU
Hello thanks for sharing the script, but I'm getting error if the hyperlink is in the same google drive for example image link uploaded in the same google drive. Anything I can do to fix this? #ERROR! TypeError: Cannot read properties of null (reading 'getRuns') (line 6).
Great videos thank you, has helped me a lot (I'm not very savvy in this). I'm working on a project and I'm trying to extract a hyperlink from 1 column, column C, and put it into another column, D but within Parenthesis of column D. Is there a formula for that?
Very very good. I have to make a python crawler and now i can finish my project in case of your tutorial. Many thanks - it saved me lot of time. **********
Thank you! I've been trying to do this for ages but all the other scripts I found didn't work. Just wondering if there is a way to use a variation of this script in google docs. So the script runs through a document and then exports the links URL and anchor text to a spreadsheet
This was so helpful! Quick Question toward the end of the video when you have a cell with multiple hyperlinks. Like you mentioned, if you have many rows of data, the different links with collide. However, I also am working with data that also has a lot of columns, so doing the transpose (or updating the function to do so) is also not feasible. Is there a way to display the individual urls still within only one cell as to not change the original row/column dimensions?
Hiya. Unfortunately I'm not near a computer for the next few weeks so I can't test, but perhaps something like this might work: =join(", " , geturl(B8))
it works on my google sheet, tqvm for the awesome script 🙏 In a multiple hyperlink cell, is there a way to extract only one single URL hyperlinked to a specific word, say for example 'view' ?
Yeah, we can do that. This script takes in two arguments: cell and text. When you use the custom function in the spreadsheet, you have to put the cell in double quotation marks, and the text is case sensitive. So for example, =geturl("B8","view") If you want the cell reference to be dynamic so you can copy it down, use =geturl("B"&row(),"view") Here's the script: function getURL(cell, text) { var myRange = SpreadsheetApp.getActiveSheet().getRange(cell); var richTextValues = myRange.getRichTextValue().getRuns(); var urls = []; for (var i = 0; i < richTextValues.length; i++) { var url = richTextValues[i].getLinkUrl(); var textRun = richTextValues[i].getText(); if (url) { if (text && textRun.indexOf(text) !== -1) { return url; // Return the URL immediately if the specific text is found } urls.push(url); } } if (text) { return null; // If text is specified but not found, return null } return urls; // Return all URLs if no specific text is specified }
I don't know what I'm doing wrong, i don't see a list of sheets to choose from when I'm trying to create a link (internal link) kinda like a table of contents that I'm trying to do. Any help would be greatly appreciated.
Hello, I'm attempting to find an easy way to directly link cell to cell with a hyper link. For instance, I want cell H11 to link me to nots in HH11, and HH12 to Hyper link me "back" to H11. I want to expand this to all my cells in an easy way. I want this same hyper link function, because it can be interchangeable with other sheets. The issue is easily getting the values to expand, instead of changing each link. The hyperlinks to google don't reveal a pattern that is practical to change either. I just want to link to the same line, H11 to HH11, and back, all the way down the document to H1000, etc, to HH1000 etc. Can you help me out?
Hi Laura, I can't as TH-cam doesn't allow certain characters in description boxes. But I'll give you the code here: function GetURL(input) { var myFormula = SpreadsheetApp.getActiveRange().getFormula(); var myAddress = myFormula.replace(/=.*?\(/,'').replace(')',''); var myRange = SpreadsheetApp.getActiveSheet().getRange(myAddress);
var richTextValues = myRange.getRichTextValue().getRuns(); var urls = []; for (var i = 0 ; i < richTextValues.length; i++) { var url = richTextValues[i].getLinkUrl(); if (url) { urls.push(url); } } return [urls]; };
Hi Hash, Thank you so very much for this script! Your video was very clear and concise. I have found one scenario that seems to break the functionality and that is to add rows above: If I have, e.g., three rows in which C2, C3, and C4 are retreiving the URLs from A2, A3, and A4, then I add two rows above row 2, C4 and C6 will now both display the URL from A6, and the URL for A4 does not display anywhere. When uswing GetURL, my workaround has been to delete the entire column containing the GetURL formula, add rows above, reenter the GetURL formula, then drag it to the bottom of the sheet; it works, but it's cumbersome. Is there any way to alter this script so that it can accommodate the additon of rows above and still reference the intended cells? Thanks so much again!
Huh, I never tested that. One solution would be to recalculate all formulas. Doing that manually won't work because the data will be cached, so we can create a new script to do the recalculation for us. I haven't had a chance to test it, but I think this *should* work. It's my go-to recalculating script when I have cached formulas: script.google.com/d/105t_qQhmLowS8FdjLig4iq3UMkZhIGIx9-D951iCJrhh0jv9tAEgllFm/edit?usp=sharing
@@HashAliNZ Thanks for getting back to me! I'm completely new to scripts, so I'm not sure how to implement this. I went into Apps Script and added the recalculateFormulas script, but I'm not sure how properly to execute it after that. I tried =recalculateFormulas(geturl(A4)), but that came up with an error.
@@djerekkayzakian-rowe Oh sorry! In your Sheet with the links, click on Insert > Drawing. Then add a rectangle or something to act as a button. Add some words to it, something like "Reset formulas" or something like that. Click on Save and Close and place it where you want it in your Sheet. Then click on it, click the three dots, and click on "Assign Script". When the popup box comes up, type the name of the function (in this case, recalculateFormulas). Then save that and click the newly made button. It'll run by itself (you might need to authorise it - if you do need to, then click it again after it's been authorised)
@@HashAliNZ Thanks so much Hash! Very cool how that works; I had no idea you could create custom buttons like that. I did create the button and try it, but it's still getting the wrong url when you add rows above then run that script. I so appreciate your help, and it's no big deal if this is not possible!
@@djerekkayzakian-rowe Ah bugger! I was really hoping that would work. I'll test out a few other things later tonight and let you know if I find anything!
If you didn't follow the video all the way through, then you'll need to ensure the function getURL has the same cases (upper case and lower case) as the script name. If you did all the way through, then you'll see how to fix this.
Hi Dinesh! Once you have your script, you don't need to hit the Run button. Just save it, then go back to your spreadsheet and start writing your custom function =GetURL(B4)
@@HashAliNZ i tried as you suggested , but the problem still persists, sharing my file link, could you please have a review docs.google.com/spreadsheets/d/1ZYLjo2niA2_k7Nabtu7QtJTjmpQhhoS9OqnKyHlGlcE/edit#gid=0
@HasAliNZ , I think i figured the problem , the label I assigned to the URL is not a text, but is a number . So could you please help with a script that works with number & text both ?
@@dineshgoel6394 Hey Dinesh, The way the function works is to extract the hyperlink from the richTextValues. Google Sheets doesn't store numbers as a rich text value (and we can't force it to - I tried using two different methods but both failed). Since numbers aren't rich text values, we cannot extract the hyperlink from it. The easiest way to create a fix is to turn adjust your number so it becomes a rich text value. For example, if your number is 456, then change it to something like 456x. There are other options like incorporating metadata (like notes), but the option I gave before is by far the easiest for data entry.
You may have had the same issue as me - I removed the word "input" from (input) in the first line in the script and then it worked: function GetURL() { var myFormula = SpreadsheetApp.getActiveRange().getFormula(); var myAddress = myFormula.replace('=GetURL(','').replace(')',''); var myRange = SpreadsheetApp.getActiveSheet().getRange(myAddress); return myRange.getRichTextValue().getLinkUrl(); };
Hi Laura, This happens in the myRange variable using the getActiveSheet() function. Here's the full and final code if you need it: function GetURL(input) { var myFormula = SpreadsheetApp.getActiveRange().getFormula(); var myAddress = myFormula.replace(/=.*?\(/,'').replace(')',''); var myRange = SpreadsheetApp.getActiveSheet().getRange(myAddress);
var richTextValues = myRange.getRichTextValue().getRuns(); var urls = []; for (var i = 0 ; i < richTextValues.length; i++) { var url = richTextValues[i].getLinkUrl(); if (url) { urls.push(url); } } return [urls]; };
Thanks Ali for this. But you only provide the original script to copy and past. Would be easier, I believe, if you would leave the code in the remarks so dislectic people can also copy and past it 🤣
your code is only working where sheet cell value has English Alphabets but I have integers in sheet cell.... like 1 then 2 then 3 How can i get that ? Below is your code Function GetURL(input){ var myFormula = SpreadsheetApp.getActiveRange().getFormula(); var myAddress = myFormula.replace(/=.*?\(/,'').replace(')',''); var myRange = SpreadsheetApp.getActiveSheet().getRange(myAddress); return myRange.getRichTextValue().getLinkUrl(); };
![🔴 ฟุตบอลแชมป์กีฬา 7HD แชมเปียน คัพ 2024 [รอบ 8 ทีมสุดท้าย] - ช่วงเช้า](http://i.ytimg.com/vi/bXFWzzy4UJw/mqdefault.jpg)
You're amazing! Directly to the point, more cases, fast (i can pause so great) and functional, thank you!
Thanks for the script, worked a treat (once I copied it correctly!)
This video greatly helped sir, saved a ton of time. I don't understand anything in these scripts, just copied your code and it magically works. THANK YOU
Glad it helped!
Heck, yes, Hash. You just saved me a tonne of time! Thank you, mate.
Happy to help, Brody
Awesome, exactly what I needed and well explained, thanks
One million upvotes for this
Hello thanks for sharing the script, but I'm getting error if the hyperlink is in the same google drive for example image link uploaded in the same google drive. Anything I can do to fix this?
#ERROR!
TypeError: Cannot read properties of null (reading 'getRuns') (line 6).
I'm having the same issue. Have you fouund a fix??
This was actually soooo helpful thank you so much
Great videos thank you, has helped me a lot (I'm not very savvy in this). I'm working on a project and I'm trying to extract a hyperlink from 1 column, column C, and put it into another column, D but within Parenthesis of column D. Is there a formula for that?
it was incredibly helpful!! you are the best! thanks!!
THANK YOU SO MUCH!!!!!
Absolutely helpful from start to end !!
This was perfect, thank you!!
Very very good. I have to make a python crawler and now i can finish my project in case of your tutorial. Many thanks - it saved me lot of time. **********
life savour thanks a bunch.
Happy to help
Thanks! Very helpful
Thank you! I've been trying to do this for ages but all the other scripts I found didn't work.
Just wondering if there is a way to use a variation of this script in google docs. So the script runs through a document and then exports the links URL and anchor text to a spreadsheet
This is really nice and detailed. It's unfortunate extracting the RichText source isn't a built-in function.
Loving your videos. Thank you for sharing!
hi! great video!!!. there is a way for open all the url at same time, with a iteratite script? thanks!
Saved me hours - thank you!
Thank you very much! It perfectly works!!!!!
great video!
Thanks, very useful
This was so helpful! Quick Question toward the end of the video when you have a cell with multiple hyperlinks. Like you mentioned, if you have many rows of data, the different links with collide. However, I also am working with data that also has a lot of columns, so doing the transpose (or updating the function to do so) is also not feasible. Is there a way to display the individual urls still within only one cell as to not change the original row/column dimensions?
Hiya. Unfortunately I'm not near a computer for the next few weeks so I can't test, but perhaps something like this might work:
=join(", " , geturl(B8))
it works on my google sheet, tqvm for the awesome script 🙏 In a multiple hyperlink cell, is there a way to extract only one single URL hyperlinked to a specific word, say for example 'view' ?
Yeah, we can do that. This script takes in two arguments: cell and text. When you use the custom function in the spreadsheet, you have to put the cell in double quotation marks, and the text is case sensitive. So for example,
=geturl("B8","view")
If you want the cell reference to be dynamic so you can copy it down, use
=geturl("B"&row(),"view")
Here's the script:
function getURL(cell, text) {
var myRange = SpreadsheetApp.getActiveSheet().getRange(cell);
var richTextValues = myRange.getRichTextValue().getRuns();
var urls = [];
for (var i = 0; i < richTextValues.length; i++) {
var url = richTextValues[i].getLinkUrl();
var textRun = richTextValues[i].getText();
if (url) {
if (text && textRun.indexOf(text) !== -1) {
return url; // Return the URL immediately if the specific text is found
}
urls.push(url);
}
}
if (text) {
return null; // If text is specified but not found, return null
}
return urls; // Return all URLs if no specific text is specified
}
@@HashAliNZ i've just tested this and it works perfectly, tqvm bro, u r amazing ! 🙏👍😃
Thanks for the useful script. Save a day!
Glad it was helpful!
Well played; thank you!
I don't know what I'm doing wrong, i don't see a list of sheets to choose from when I'm trying to create a link (internal link) kinda like a table of contents that I'm trying to do. Any help would be greatly appreciated.
Hello, I'm attempting to find an easy way to directly link cell to cell with a hyper link. For instance, I want cell H11 to link me to nots in HH11, and HH12 to Hyper link me "back" to H11. I want to expand this to all my cells in an easy way. I want this same hyper link function, because it can be interchangeable with other sheets. The issue is easily getting the values to expand, instead of changing each link. The hyperlinks to google don't reveal a pattern that is practical to change either. I just want to link to the same line, H11 to HH11, and back, all the way down the document to H1000, etc, to HH1000 etc. Can you help me out?
Nice, thanks!!
Can you copy-paste the final script into the description box above? Thanks
Hi Laura, I can't as TH-cam doesn't allow certain characters in description boxes. But I'll give you the code here:
function GetURL(input) {
var myFormula = SpreadsheetApp.getActiveRange().getFormula();
var myAddress = myFormula.replace(/=.*?\(/,'').replace(')','');
var myRange = SpreadsheetApp.getActiveSheet().getRange(myAddress);
var richTextValues = myRange.getRichTextValue().getRuns();
var urls = [];
for (var i = 0 ; i < richTextValues.length; i++) {
var url = richTextValues[i].getLinkUrl();
if (url) {
urls.push(url);
}
}
return [urls];
};
Really helpful
Thanks!
Perfect👍
any way to extract the link for the second case? a link without hyperlink formula?
Yes, the video is showing how to write the script to extract it
Thank you so much.
You're welcome!
Hi Hash,
Thank you so very much for this script! Your video was very clear and concise. I have found one scenario that seems to break the functionality and that is to add rows above:
If I have, e.g., three rows in which C2, C3, and C4 are retreiving the URLs from A2, A3, and A4, then I add two rows above row 2, C4 and C6 will now both display the URL from A6, and the URL for A4 does not display anywhere. When uswing GetURL, my workaround has been to delete the entire column containing the GetURL formula, add rows above, reenter the GetURL formula, then drag it to the bottom of the sheet; it works, but it's cumbersome. Is there any way to alter this script so that it can accommodate the additon of rows above and still reference the intended cells?
Thanks so much again!
Huh, I never tested that. One solution would be to recalculate all formulas. Doing that manually won't work because the data will be cached, so we can create a new script to do the recalculation for us. I haven't had a chance to test it, but I think this *should* work. It's my go-to recalculating script when I have cached formulas: script.google.com/d/105t_qQhmLowS8FdjLig4iq3UMkZhIGIx9-D951iCJrhh0jv9tAEgllFm/edit?usp=sharing
@@HashAliNZ Thanks for getting back to me! I'm completely new to scripts, so I'm not sure how to implement this. I went into Apps Script and added the recalculateFormulas script, but I'm not sure how properly to execute it after that. I tried =recalculateFormulas(geturl(A4)), but that came up with an error.
@@djerekkayzakian-rowe Oh sorry! In your Sheet with the links, click on Insert > Drawing. Then add a rectangle or something to act as a button. Add some words to it, something like "Reset formulas" or something like that.
Click on Save and Close and place it where you want it in your Sheet. Then click on it, click the three dots, and click on "Assign Script". When the popup box comes up, type the name of the function (in this case, recalculateFormulas). Then save that and click the newly made button. It'll run by itself (you might need to authorise it - if you do need to, then click it again after it's been authorised)
@@HashAliNZ Thanks so much Hash! Very cool how that works; I had no idea you could create custom buttons like that. I did create the button and try it, but it's still getting the wrong url when you add rows above then run that script. I so appreciate your help, and it's no big deal if this is not possible!
@@djerekkayzakian-rowe Ah bugger! I was really hoping that would work. I'll test out a few other things later tonight and let you know if I find anything!
wow, wonderfull
Thanks a lot
Please Help, does NOT work. The cell says #NAME? and comments Error Unknown function: 'getURL'.
If you didn't follow the video all the way through, then you'll need to ensure the function getURL has the same cases (upper case and lower case) as the script name. If you did all the way through, then you'll see how to fix this.
hI
i tried the script , but it shows Error Exception: Range not found (line 4).
Hi Dinesh!
Once you have your script, you don't need to hit the Run button. Just save it, then go back to your spreadsheet and start writing your custom function =GetURL(B4)
@@HashAliNZ i tried as you suggested , but the problem still persists, sharing my file link, could you please have a review docs.google.com/spreadsheets/d/1ZYLjo2niA2_k7Nabtu7QtJTjmpQhhoS9OqnKyHlGlcE/edit#gid=0
@HasAliNZ , I think i figured the problem , the label I assigned to the URL is not a text, but is a number . So could you please help with a script that works with number & text both ?
@@dineshgoel6394 Hey Dinesh, The way the function works is to extract the hyperlink from the richTextValues. Google Sheets doesn't store numbers as a rich text value (and we can't force it to - I tried using two different methods but both failed). Since numbers aren't rich text values, we cannot extract the hyperlink from it. The easiest way to create a fix is to turn adjust your number so it becomes a rich text value. For example, if your number is 456, then change it to something like 456x.
There are other options like incorporating metadata (like notes), but the option I gave before is by far the easiest for data entry.
You may have had the same issue as me - I removed the word "input" from (input) in the first line in the script and then it worked:
function GetURL() {
var myFormula = SpreadsheetApp.getActiveRange().getFormula();
var myAddress = myFormula.replace('=GetURL(','').replace(')','');
var myRange = SpreadsheetApp.getActiveSheet().getRange(myAddress);
return myRange.getRichTextValue().getLinkUrl();
};
life saver
My Google Sheet can not recognize the script I created. How do I link the GSheet to the GetURL()? Thanks
Hi Laura, This happens in the myRange variable using the getActiveSheet() function. Here's the full and final code if you need it:
function GetURL(input) {
var myFormula = SpreadsheetApp.getActiveRange().getFormula();
var myAddress = myFormula.replace(/=.*?\(/,'').replace(')','');
var myRange = SpreadsheetApp.getActiveSheet().getRange(myAddress);
var richTextValues = myRange.getRichTextValue().getRuns();
var urls = [];
for (var i = 0 ; i < richTextValues.length; i++) {
var url = richTextValues[i].getLinkUrl();
if (url) {
urls.push(url);
}
}
return [urls];
};
I'm so fucking grateful to you. Bless you, laddie 💖
Happy to help, lady
Nice
Thank you Hash(im?) Ali sir!
Most welcome!
When I tried the original code, it just gave me blank cell
Here you go: script.google.com/d/1-RxfEgtFhU0rhRjUkMNSUWc8jLjCGnT5DBTptsUztD9jS3ZuBaVckPXX/edit?usp=sharing
I wanted to give it a thumbs up, but that would change it 405 from 404. I love your work but 404 is fun.
🤣🤣🤣
Thanks Ali for this. But you only provide the original script to copy and past. Would be easier, I believe, if you would leave the code in the remarks so dislectic people can also copy and past it 🤣
Fair point, Karel!
Here you go: script.google.com/d/1-RxfEgtFhU0rhRjUkMNSUWc8jLjCGnT5DBTptsUztD9jS3ZuBaVckPXX/edit?usp=sharing
plz give me answer ASAP
Sorry I don't see your question anywhere. How can I help?
your code is only working where sheet cell value has English Alphabets but I have integers in sheet cell.... like 1 then 2 then 3
How can i get that ? Below is your code
Function GetURL(input){
var myFormula = SpreadsheetApp.getActiveRange().getFormula();
var myAddress = myFormula.replace(/=.*?\(/,'').replace(')','');
var myRange = SpreadsheetApp.getActiveSheet().getRange(myAddress);
return myRange.getRichTextValue().getLinkUrl();
};
any update ??