How to pull a response code into Google Spreadshee

2019-06-08 14:14发布

I have a list of URL's that I want to check the response code for. Does anyone know how I could do this in Google docs? I have 16000 url's so it's quite a big ask.

Any help appreciated.

1条回答
相关推荐>>
2楼-- · 2019-06-08 14:47

There is no builtin formula function to do this, but you can write one in Google Apps Script. Here is an example that you can adapt to your purpose.

function HTTPResponse( uri )
{
  var response_code ;
  try {
    response_code = UrlFetchApp .fetch( uri ) .getResponseCode() .toString() ;
  }
  catch( error ) {
    response_code = error .toString() .match( / returned code (\d\d\d)\./ )[1] ;
  }
  finally {
    return response_code ;
  }
}

Having defined this function, you can call it from a cell:

A1: 'http://www.google.com/
A2: = HTTPResponse( A1 )

A bug in UrlFetchApp.fetch() makes the string matching necessary. A fix was promised in the discussion thread UrlFetchApp.fetch - get response code on exception?, but I cannot find that it was ever delivered. The workaround is dependent on the text of the exception message. That seems unreliable and dependent on locale.

Google's article Troubleshooting and Debugging Scripts warns that Google Apps Script imposes quotas on various actions. You might well find that 16,000 simultaneous HTTP requests will be throttled back. The article refers users having special needs to the Google Apps Script Help Forum.

查看更多
登录 后发表回答