0

Looking for regex or another solution to update all the URLs without HTTPS at the start of the domain; for example, domain.com to > https://domain.com.

Please, note I have multiple domains in the sheet, for example. domain.com. domain1.com, domain2.com, etc. btw I want to find and replace functions, so domains are updated wherever they are in the sheet.

1 Answer 1

0

Use this formula (Where A2 is your url without the https)

=IF(REGEXMATCH(A1,"^https?:\/\/[\w\.]*"),A1,"https://" & A1)

Edit

function onEdit(e) {
  const s = SpreadsheetApp.getActive();
  const cell = e.range;
  const url = cell.getValue();
  if (/^(?<!https?:\/\/)[\w\.]+\.\w+/.test(url)) {
    cell.setValue("https://" + url)
  }
}

This uses a weird Regex syntax that I'm amazed actually works. Just slap this onto your Appscript editor then give it permission. If you input a url without the http:// or https://, then it will append an https:// to the url.

Edit#2

function onEdit(e) {
  const s = SpreadsheetApp.getActive();
  const cells = e.range;
  const urlList = cells.getValues();
  for (let i = 0; i < urlList.length; i++) {
    for (j = 0; j < urlList[i].length; j++) {
      const url = urlList[i][j];
      if (/^(?<!https?:\/\/)[\w\.]+\.\w+/.test(url)) {
        cells.getCell(i+1,j+1).setValue("https://" + url)
      }
    }
  }
}

This should deal with copy-pasting different values. I still don't understand why you need to append urls with https:// though. As for your other comment, do you have a really big dataset? Is it possible for you to just copy and paste the values onto the same cells? Sorry I took a while to getting back to you.

5
  • pls, note I have multiple domains in the sheet, for example. domain.com. domain1.com, domain2.com, etc. btw i want to run find and replace function so domains are updated where they are in the sheet
    – sub0
    May 12 at 22:17
  • Ah you wanted it to update every time a URL is inputted. Please edit your question while I figure the appscript for this.
    – David Tan
    May 12 at 22:49
  • Sorry for the confusion; I updated the questions.
    – sub0
    May 12 at 23:14
  • The formula works when you add new URLs or click on an existing URL to edit, but it doesn't replace automatically the domains that are missing HTTPS:// in the start
    – sub0
    May 15 at 20:25
  • There is another issue with the script; if you add a bunch of URLs, it will change all URLs with the value in the first top cell.
    – sub0
    May 19 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.