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;
}
};