I want to run below code every hour but it's not working properly. What I mean to say is that when manually run the apps script it's fine but I had gave the trigger for this code as time-driven in every hour.
But this apps script is not updating my spreadsheet on every hour. Please provide me some solution for this.
function gControlMonitoring()
{
var sheet= SpreadsheetApp.getActiveSpreadsheet();
// var ss=sheet.getActiveSheet();
// var sheet= SpreadsheetApp.openById('19Fk3pxtfCqP_6Lj-bZvI7UO_ejqA-c1OQBNaBKLa2Tk');
// SpreadsheetApp.setActiveSpreadsheet(sheet);
var ss=sheet.getActiveSheet();
// var ss =SpreadsheetApp.getActiveSpreadsheet();
var resp = UrlFetchApp.fetch("https://mail.google.com/a/advanceforensictools.com");
Logger.log(resp);
if(resp.getContentText().indexOf('ssomanagerservlet')>-1)
{
var d=new Date();
var dateformat=Utilities.formatDate(d, "Asia/Kolkata", "MM/dd/yyyy");
Logger.log("dateformat=" + dateformat); //Taking the Current date
// var cell = sheet.getRange(2,1); //updating the spreadsheet with current date
// cell.setValue(" " +dateformat);
var timeFormat=Utilities.formatDate(d, "Asia/Kolkata", "HH");//Taking current Time
Logger.log("dateTime=" + timeFormat);
var data_column = ss.getRange(1,1,1,ss.getLastColumn()).getValues();//putting all the values of row one to data_column instance
Logger.log("record" +data_column);
if(timeFormat==0)
{
var newrow=updatingspread();//Add New row if a new day has start
var cell = sheet.getRange(2,1); //updating the spreadsheet with current date
cell.setValue(" " +dateformat);
}
for(var i=0;i<25;i++)
{
var datacol=data_column[0];
Logger.log(datacol[i]);
if(datacol[i]==timeFormat)
{
ss.getRange(2, i+1).setBackground('green').setValue(1);
// if(i==23)
// {
// sheet.getRange(2, i+1).setBackground('green').setValue(1);
// var newrow=updatingspread();
// }
}
}
}
else
{
for(var i=0;i<25;i++)
{
var datacol=data_column[0];
Logger.log(datacol[i]);
if(datacol[i]==timeFormat)
{
sheet.getRange(2, i+1).setBackground('red').setValue(0);
if(i==23)
{
ss.getRange(2, i+1).setBackground('red').setValue(0);
var newrow=updatingspread();
}
GmailApp.sendEmail('[email protected]', 'hello', 'Hello Team');
Logger.log(response.getResponseCode());
Logger.log(response.getContentText());
}
}
}
}
function updatingspread()
{
var newSheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = newSheet.insertRowBefore(2);
var data_column1 = sheet.getRange(2,1,1,sheet.getLastColumn()).getBackgrounds();
Logger.log(data_column1);
for(i=0;i<25;i++)
{
var dataval=data_column1[0];
Logger.log(dataval[i]);
if(dataval[i]!='white'&& dataval[i]!='#ffffff')
{
var newup=sheet.getRange(2,i+1).setBackgroundRGB(255,255,255);
}
}
var range = sheet.getRange('A7');
sheet.hideRow(range);
}
