Script JS Google docs

4

I have a spreadsheet in Google docs like the images below. In figure 1 that is spreadsheet I have the URL of the site that will be monitored and sent to the email that is inserted, beside I have in Table C it shows if the site is responding or not and in table D it shows the time there was the communication error. All that I've described is working, the script that was mounted is in Ferramentas -> Editor de Script , this script makes the table work and sends the reports to my e-mail.

The point I need help with: in the table below I need to monitor IP the port and name of arquivo.ogg as shown in the first image, however it always shows that the URL does not exist, I believe it is because of the lines that I left in bold that they only ping and do not check if the file exists or not:

Follow the check script code:

var sheet = SpreadsheetApp.getActiveSheet(),
startRow = 2,  // skips header line
lastRow = sheet.getLastRow() -1, // get last line with data
dataRange = sheet.getRange(startRow, 1, lastRow, 3), // first value is the row, second is the column in number
data = dataRange.getValues(); // get data of every cell within the range


function checkAllSites() {
  for (var i = 0; i < data.length; i++) {
    var row = data[i],
    siteUrl = row[0],
    notificationEmail = row[1],
    currentRow = (startRow+i),
    wasItAvailable = sheet.getRange(currentRow, 3), //getRange is 1-based instead of 0-based 
    lastCheckDate = sheet.getRange(currentRow, 4),
    timestamp = Utilities.formatDate(new Date(), "America/Sao_Paulo", "dd/MM/yyyy HH:mm:ss");

**if(!pingSite(siteUrl)) { // Let's try to open the site and check if we get a success** response
  var subject = "Your site is unavailable :(",
        message = "We tried to check your site " + siteUrl + " at " + timestamp + " but it was unavailable!";
    MailApp.sendEmail(notificationEmail, subject, message);      
    wasItAvailable.setValue('no');      
  } else {
    wasItAvailable.setValue('yes');
  }    
 lastCheckDate.setValue(timestamp);
 }
};

function pingSite(url) {
  var status;

try{
  **status = UrlFetchApp.fetch(url).getResponseCode();**
  if(status>=200 && status<=206){
    return true;
  } else{
  return false;
 }
} catch(error){ // the typical error that might occur here is DNS related. No matter what happens instead of HTTP 2xx code, we'll consider an error
return false;
}
};
    
asked by anonymous 04.12.2014 / 13:23

0 answers