4

Is there any way for Google Sheets to validate that the path/URL (referenced in a cell) is valid?

I'd like Sheets to scan a column and mark an adjacent column as true or false depending on whether something exists at that path or not.

  • 2
    Good questions, but don't try to shoehorn multiple questions into one question post. Your question about local file paths should be in a separate post. – ale Oct 15 '15 at 19:49
  • 1
    @Normal: Wasn't looking to get it closed. Just trying to help the Asker get the help they need. – ale Oct 16 '15 at 1:10
4

The spreadsheet functions like importhtml and importxml seem insufficient for this; they throw an error when they can't parse the content in a specified way, which doesn't mean the content is unreachable.

But you can do this with a script. For illustration, my script scans the URLs in cells A1:A10, and records the result in B1:B10. Adjust the ranges as you see fit; they can also be fetched as strings from some spreadsheet cells.

function validate() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var links = sheet.getRange("A1:A10").getValues();
  var results = [];
  for (var i = 0; i < links.length; i++) {
    var code = 0;
    if (/^http/i.test(links[i][0])) {
      try {
        code = UrlFetchApp.fetch(links[i][0]).getResponseCode();
      }
      catch(e) { 
      }
    }
    results.push([code === 200]);
  }    
  sheet.getRange("B1:B10").setValues(results);
}

Explanation: each of the supposed links is first checked for sanity: does it begin with http? If yes, then an attempt to get HTTP response code is made. This attempt may well fail; hence the try-catch block. The result is recorded as True only if the code is 200.

Sample output:

+-----------------------------------------+-------+
| http://www.example.com                  | TRUE  |
| http://www.invalid.example.com          | FALSE |
| http://stackexchange.com                | TRUE  |
| http://google.com                       | TRUE  |
| http://google.com/non-existent-file.pdf | FALSE |
| blah                                    | FALSE |
| nonsense                                | FALSE |
| 1                                       | FALSE |
| 2                                       | FALSE |
| 3                                       | FALSE |
+-----------------------------------------+-------+

(Technical note: the method UrlFetchApp.fetch allows for an option muteHttpExceptions that allows it to proceed after getting a failure HTTP code. However, setting this on does not help against DNS lookup errors; this is why I used try-catch instead.)

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.