Stop Google Sheets from creating link automatically

user_15694975 0
Viewed 1677

I am trying to write some text in Google Sheets, specifically some URLs. I need that because I am trying to explain to an agency the new tracking we are implementing. I have the URL in a cell, and I went to have some caracters in red to highlight the changes, and leave the rest in black. However, as soon as I modified my stuff in the formula bar, if I press enter or try to save the edit, Sheets automatically detects a link so it put everything in light blue and underlined, which is exactly what I don't want him to do. I know there is a "stop detecting links automatically" thing in Microsoft Word and Google Doc but I can't find it in Sheets settings. Any solution?

google-sheets-api
3 Answers

immediately after typing the link after its been cast to a hyperlink, if you then press backspace a single time the conversion to link will be undone

user_14598600

I've double checked the behavior of URLs in Google Sheets and I can see that even when you remove the hyperlink of a URL in a cell (via Remove link option), it will still automatically reappear once you edit the cell of that URL.

Recommendation:

According to an answer from this post How do I stop automatic hyperlinks in google sheets?, one of the community member have recommended to use a different format to the URLs to stop the cell from automatically creating hyperlinks. And this recommendation worked on my testing.

With that being said, I have crated a workaround using a custom bound script, where it will automatically format all URLs (it'll add "//" at the beginning of each URLs) on a sheet and then it will visually hide the format for a cleaner look for your convenience. After that, the hyperlinks will no longer be auto-created on the URLs. The catch on this method is that users will need to remove the "//" at the beginning of the URLs to access them.

Sample

Sample sheet with URLs on Column A:

enter image description here

Custom Bound Script:

function onOpen() { //Creates a custom menu called "Hyperlink" at the top right of your spreadsheet file where you can run this script
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Hyperlink')
      .addItem('Remove Hyperlink', 'addFormat')
      .addToUi();
}

function addFormat() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getRange("A1:A").getValues(); //CHANGE THIS RANGE IF YOUR URLS ARE IN A DIFFERENT COLUMN
var text = "//";
  for(var x=1; x<=range.length;x++){ 
    if(range[x-1]=="" || x==1){ //If it is cell 1 (the title of the column) or if it is an empty cell, it will be skipped for formatting
    }else{
      if(range[x-1][0][0] == "/"){ //If a cell has been already formatted (//), it will be skipped
      }else{ //Formats new URL on the cell of Column (e.g. A)
        sheet.getRange(x,1).clearContent().setValue(text+range[x-1]).setFontColor("black");
        Logger.log("Done with "+text+range[x-1]+" on row"+ x);
        hideFormat(x); //function to visually hide the "//" on each URLs
      }
    }
  }
}

function hideFormat(row){ //Function to visually hide the "//" format at the beginning by coloring it to white font color
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A"+row); //CHANGE THIS COLUMN IF YOUR URLS ARE IN A DIFFERENT COLUMN
  var hide = SpreadsheetApp.newTextStyle()
      .setForegroundColor('white')
      .build();
  var richText = SpreadsheetApp.newRichTextValue()
      .setText(sheet.getRange("A"+row).getValue()) //CHANGE THIS COLUMN IF YOUR URLS ARE IN A DIFFERENT COLUMN
      .setTextStyle(0, 2, hide)
      .build();
  range.setRichTextValue(richText);
}

After saving & running the script, click the Hyperlink > Remove Hyperlink on your Spreadsheet:

enter image description here

Here's the result:

enter image description here

Then, you can make any changes on the URL cells without hyperlinks being auto-created:

enter image description here

user_153848250

None of the existing answers actually provide a solution to the exact issue mentioned in the question, which is "[when] I press enter or try to save the edit, Sheets automatically detects a link so it put everything in light blue and underlined"

Pressing backspace as per @rexfordkelly's answer is only valid when the link is created during editing of the cell.

Here, the issue is that the link will be created when saving the cell after edition.

The only way I found to prevent this is to put the cursor outside of the text of the link.

Example:

Let's say ^ represents our cursor, and let this be our cell content:

my link: https://mywebsite.com

If you have your cursor e.g. here:

my link: htt^ps://mywebsite.com

or here (etc...):

my link: https://mywebsite.com^

the link will inevitably be created when saving the cell (i.e. hitting Enter).

However, if you have your cursor here (or anywhere else not on the link itself):

my l^ink: https://mywebsite.com

the link won't be created when pressing Enter.

user_169742180
Related