CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
WasHere
WasHere

Reputation: 59

Q: How can I Convert a list of URL domains into a full url

I have a list of thousands of URL domains but have copied some into an example here(spreadsheet here) and was looking for a way to convert them into their full URL, as I need the full URL for something I am doing further down the line.

For example all-about-test.eu would become http://all-about-test.eu/

As the URLS might differ between www., https, http etc I cant simply concat this onto the front.

I had a look for google sheet functions that might be able to do this, but couldn't find any.

Upvotes: 1

Views: 284

Answers (1)

Iamblichus
Iamblichus

Reputation: 19319

You could use UrlFetchApp.fetch in order to try accessing the different URLs with the different protocols, specifying the parameter followRedirects: false. You would then check the HTTP response code (see getResponseCode()), and return the URL if it's an acceptable response code:

function returnFullURL(domain) {
  const options = ["https://","http://", "www.", "https://www.", "http://www.", ""]; // Possible protocols, change if necessary
  const badCodes = [404,301,302,303,307,308]; // Acceptable response codes, change if necessary
  for (let i = 0; i < options.length; i++) {
    const url = options[i] + domain;
    try {
      const params = { 
        muteHttpExceptions: true,
        followRedirects: false
      }
      const response = UrlFetchApp.fetch(options[i] + domain, params);
      const code = response.getResponseCode();
      if (!badCodes.includes(code)) return url;
    } catch (err) {
      console.log(err);
    }
  }
  return "No combination allowed";
}

You could then use this as a Custom function:

enter image description here

Note:

  • I'm not sure whether these are the appropriate HTTP response status codes to check, and whether these are all the possibilities for protocols, www subdomains, etc., but this should give you a start.

Edit:

As an alternative, you could build all URLs with a single call to your function, instead of calling it for every domain. In order to do that, copy and call the following function and specify the range for all domains (in your sample sheet, that would be =returnAllURLs(A2:A18)):

function returnAllURLs(domains) {
  const options = ["https://","http://", "www.", "https://www.", "http://www.", ""]; // Possible protocols, change if necessary
  const badCodes = [404,301,302,303,307,308]; // Acceptable response codes, change if necessary
  return domains.map(domain => {
    for (let i = 0; i < options.length; i++) {
      const url = options[i] + domain[0];
      try {
        const params = { 
          muteHttpExceptions: true,
          followRedirects: false
        }
        const response = UrlFetchApp.fetch(options[i] + domain[0], params);
        const code = response.getResponseCode();
        if (!badCodes.includes(code)) return [url];
      } catch (err) {
        console.log(err);
      }
    }
    return ["No combination allowed"];
  });
}

Related:

Upvotes: 1

Related Questions