Skip to content

Instantly share code, notes, and snippets.

@saleemdev

saleemdev/gist:47e671e644f939f283f99e61a3bef72f

Created June 26, 2022 18:40
Show Gist options
  • Save saleemdev/47e671e644f939f283f99e61a3bef72f to your computer and use it in GitHub Desktop.
Save saleemdev/47e671e644f939f283f99e61a3bef72f to your computer and use it in GitHub Desktop.
Download ZIP
AppScript Brian
Raw
gistfile1.txt
// custom menu function, this adds another option to the ui of google, not sure if i want to keep this later but for now it is okay.
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Calmail_28ofMonth_lastTrigger','Calmail_28ofMonth_lastTrigger')
.addItem('do not touch', 'CreateJobCard')
.addItem('Send Charts', 'emailCharts')
.addItem('pastedate' , 'PasteDate')
.addToUi();
}
// this function copies the date from the cell with the formala TODAY as values only. The reason for this is the nature of the function today,
// everytime a change is made to the sheet the function today is recalculated and all dependant functions as well. This slows down the program.
// by pasting as a value only the cell that contains todates date will be updated. And this function will trigger everyday to make sure the date is correct.
// furthermore, all other dates refer to cell AP5 for the date as to calculate the date only once.
function PasteDate() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var Sheet = ss.getSheetByName('PPM schedule');
resetFilterPPM()
var date = Sheet.getRange('AP4').getValue();
var datespot = Sheet.getRange('AP5')
Sheet.getRange('AP4').copyTo(Sheet.getRange('AP5'),{contentsOnly: true})
}
/*
// great thanks to Danny Blaker
// create a menu
function onOpen() {
var menuEntries = [ {name: "DO NOT TOUCH YET WORK IN PROGRESS", functionName: "CreateJobCard"}];
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.addMenu("Job Card Generator", menuEntries);
*/
// select first cell in column - tools - macro's - formulaDropDown --> copies function to all cells in column, just to help us build the spreadsheet
function formulaDropDown(){
var sheet = SpreadsheetApp.getActiveSheet();
var active = sheet.getActiveCell();
var formula = active.getFormula();
var column = active.getColumn();
var start = active.getRow();
var lr = sheet.getLastRow();
var range = lr-start+1
sheet.getRange(start,column,range).setFormula(formula);
};
// sort the list in portfolio on Unique numbers. When the list is not ordered on number, the service request is not working.
// important after adding a new equipment
function sortUniqueNumbers() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheetByName('Portfolio');
var UN = dataSheet.getRange('B2').activate();
UN.getFilter().sort(2, true);
};
// The owner and calibration computer can add rows.
// Works when pressing 'add new equipment' button
// make sure the both lists have the same length
function insertRow() {
// part 1 = resett filters on sheet Calibration
// do not want to add rows half way the sheet
// adjust to new order portfolio, ppm and calibration
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = ss.getId();
var dataSheet = ss.getSheetByName('Calibration Shedule');
var lastRow = dataSheet.getLastRow();
var sheetId = dataSheet.getSheetId();
var filterSettings = {
"range": {
"sheetId": sheetId,
"startRowIndex": 1,
"endRowIndex": lastRow,
"startColumnIndex": 0,
"endColumnIndex": 31 //# columns with filter option
}
};
var requests = [{
"setBasicFilter": {
"filter": filterSettings
}
}];
Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId); // save settings
// part 2: resett filter ppm
//request spreadsheet not needed - done in part 1
var dataSheet = ss.getSheetByName('PPM schedule');
var lastRow = dataSheet.getLastRow();
var sheetId = dataSheet.getSheetId();
var filterSettings = {
"range": {
"sheetId": sheetId,
"startRowIndex": 1,
"endRowIndex": lastRow,
"startColumnIndex": 0,
"endColumnIndex": 31 // # columns with filter option
}
};
var requests = [{
"setBasicFilter": {
"filter": filterSettings
}
}];
Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId); // save settings
// part 3 = resett filters in sheet 'portfolio'
var dataSheet = ss.getSheetByName('Portfolio');
var lastRow = dataSheet.getLastRow();
var sheetId = dataSheet.getSheetId();
var filterSettings = {
"range": {
"sheetId": sheetId,
"startRowIndex": 1,
"endRowIndex": lastRow,
"startColumnIndex": 0,
"endColumnIndex": 25 // # columns with filter option
}
};
var requests = [{
"setBasicFilter": {
"filter": filterSettings
}
}];
Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId); // save settings
// part 4 add row
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var portfolio = spreadsheet.getSheetByName('Portfolio');
var PPM = spreadsheet.getSheetByName('PPM schedule');
var Cal = spreadsheet.getSheetByName('Calibration Shedule');
var lr = portfolio.getLastRow();
var lr2 = lr+1;
Logger.log(lr)
Cal.insertRowsAfter(lr, 1); // it is important that lr of PPM and portfolio have the same length
Cal.getRange('A5:AQ5').copyTo(Cal.getRange('A'+lr2+':AQ'+lr2), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false); // paste format because of use of arrayformula (paste the format and data validation only)
Cal.getRange('AI5:AI5').copyTo(Cal.getRange('AI'+lr2+':AI'+lr2), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false); // paste formula --> paste the formula present in the cell (last performed cal date)
PPM.insertRowsAfter(lr, 1); // it is important that lr of PPM and portfolio have the same length
PPM.getRange('A5:AS5').copyTo(PPM.getRange('A'+lr2+':AS'+lr2), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false); // paste format because of use of arrayformula (paste the format and data validation only)
PPM.getRange('AK5:AK5').copyTo(PPM.getRange('AK'+lr2+':AK'+lr2), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false); // paste formula --> paste the formula present in the cell (last performed ppm date)
portfolio.insertRowsAfter(lr, 1);
portfolio.getRange('A5:AH5').copyTo(portfolio.getRange('A'+lr2+':AH'+lr2),SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
portfolio.getRange('N5:N5').copyTo(portfolio.getRange('N'+lr2+':N'+lr2), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false); // paste formulua --> last maintenance
portfolio.getRange('D5:D5').copyTo(portfolio.getRange('D'+lr2+':D'+lr2), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false); // paste formulas only --> Job card link
portfolio.getRange('Z5:AC5').copyTo(portfolio.getRange('Z'+lr2+':AC'+lr2), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false); // iferror --> arrayformula not working --> copy formula
portfolio.getRange('AI5:AI5').copyTo(portfolio.getRange('AI'+lr2+':AI'+lr2), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false); // iferror lookup phone number requester
// shows gui with information how to add new equipment
var ui = SpreadsheetApp.getUi(); // Same variations.
var result = ui.alert(
'How to add New Equipment',
// '1) Search for the appropriate equipment, either by filtering department etc or use ctrl + f \n 2) Change status to current status of Equipment in column O \n 3) Change date of the equipment in column S to date today (double click on cell) \n 4) Open link of the equipment in Column A and fill in form',
'1) Scroll down and insert equipment unique number, name, engineer information (such as name, email and phone) directorate, department, location etc. in the last row (it is empty now).\n\n2) In case the equipment requires calibration add the Calibration date (dd/mm/yyyy).\n\n3) In case the equipment requires PPM add the PPM date (dd/mm/yyyy).\n\n4) When finished press the reset filter button to sort the list of equipment on unique number',
ui.ButtonSet.OK);
}
// buttons
function save() {
//this function is coupled to the save 'button' and saves the file
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.Save
SpreadsheetApp.getActiveSpreadsheet().toast(" ","File Saved");
};
function portfolio() {
//this function is coupled to the portfolio 'button' and brings you to the portfolio sheet
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Portfolio'), true);
//sort list unique numbers
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheetByName('Portfolio');
var UN = dataSheet.getRange('B2').activate();
UN.getFilter().sort(2, true);
};
function Service_History(){
//this function is coupled to the Service History 'button' and brings you to the Service History sheet
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Service History'), true);
};
function PPM_Schedule(){
//this function is coupled to the PPM Schedule 'button' and brings you to the PPM sheet
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('PPM schedule'), true);
};
function Cal_Schedule(){
//this function is coupled to the PPM Schedule 'button' and brings you to the Calibration sheet
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Calibration Shedule'), true);
};
function Statistics(){
//this function is coupled to the PPM Schedule 'button' and brings you to the Statistics sheet
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Statistics'), true);
};
// this botton gives information for the biomeds how to open the google form, where they can fill in the maintenance etc.
// need to be adapted when we use unique numbers
function showAlert() {
//this function is coupled to the Add Maintenance 'button' and explains how to add maintenance
var ui = SpreadsheetApp.getUi(); // Same variations.
var result = ui.alert(
'How to Add Maintenance/Repair/Calibration',
// '1) Search for the appropriate equipment, either by filtering department etc or use ctrl + f \n 2) Change status to current status of Equipment in column O \n 3) Change date of the equipment in column S to date today (double click on cell) \n 4) Open link of the equipment in Column A and fill in form',
'1) Search for the appropriate equipment, either by filtering department etc or use ctrl + f \n 2) Change status to current status of Equipment in column O \n 3) Open link of the equipment in Column A and fill in form',
ui.ButtonSet.OK);
}
// This function is used to reset the filters back to their original status with everything selected after each use. Otherwise the next engineer will only be able to see a limited amount of information.
// connected to reset filter button in portfolio
function resetFilterPortfolio() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = ss.getId();
var dataSheet = ss.getSheetByName('Portfolio');
var lastRow = dataSheet.getLastRow();
var sheetId = dataSheet.getSheetId();
var filterSettings = {
"range": {
"sheetId": sheetId,
"startRowIndex": 1,
"endRowIndex": lastRow,
"startColumnIndex": 0,
"endColumnIndex": 25
}
};
var requests = [{
"setBasicFilter": {
"filter": filterSettings
}
}];
Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
// sort Unique numbers
var UN = dataSheet.getRange('B2').activate();
UN.getFilter().sort(2, true);
}
// This function is used to reset the filters back to their original status with everything selected after each use. Otherwise the next engineer will only be able to see a limited amount of information.
// function couples to 'Reset filter' button in ppm
function resetFilterPPM() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = ss.getId();
var dataSheet = ss.getSheetByName('PPM schedule');
var lastRow = dataSheet.getLastRow();
var sheetId = dataSheet.getSheetId();
var filterSettings = {
"range": {
"sheetId": sheetId,
"startRowIndex": 1,
"endRowIndex": lastRow,
"startColumnIndex": 0,
"endColumnIndex": 28 // # columns with filter option
}
};
var requests = [{
"setBasicFilter": {
"filter": filterSettings
}
}];
Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}
// This function is used to reset the filters back to their original status with everything selected after each use. Otherwise the next engineer will only be able to see a limited amount of information.
// connected to reset filter button in calibration
function resetFilterCal() { var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = ss.getId();
var dataSheet = ss.getSheetByName('Calibration Shedule');
var lastRow = dataSheet.getLastRow();
var sheetId = dataSheet.getSheetId();
var filterSettings = {
"range": {
"sheetId": sheetId,
"startRowIndex": 1,
"endRowIndex": lastRow,
"startColumnIndex": 0,
"endColumnIndex": 27 //# columns with filter option
}
};
var requests = [{
"setBasicFilter": {
"filter": filterSettings
}
}];
Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}
// This function is used to reset the filters back to their original status with everything selected after each use. Otherwise the next engineer will only be able to see a limited amount of information.
// connected to reset filter button in service history
function resetFilterServiceHistory() { var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = ss.getId();
var dataSheet = ss.getSheetByName('Service History');
var lastRow = dataSheet.getLastRow();
var sheetId = dataSheet.getSheetId();
var filterSettings = {
"range": {
"sheetId": sheetId,
"startRowIndex": 3,
"endRowIndex": lastRow,
"startColumnIndex": 1,
"endColumnIndex": 49 //# columns with filter option
}
};
var requests = [{
"setBasicFilter": {
"filter": filterSettings
}
}];
Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}
// After Service request by user a email + text will be sent to the biomed --> SMS is currently not functioning, needs to be addapted to new Africastalking account
function emailServiceRequest(service) { // activated by a trigger on form submit, built in with checkbox to make sure it only gets sent once
var ss = SpreadsheetApp.getActiveSpreadsheet(); // Document actief maken in de functie
var sheet = ss.getSheetByName('MTRH Service Request'); // Forces the active sheet in the document to be the service request
var lastRow = sheet.getLastRow(); // Get last row of the active sheet
var priority = sheet.getRange('T'+lastRow).getValue(); // Get the priority of the request, to determine urgent or non urgent message.
if (sheet.getRange('R'+lastRow).getValue() == "Sent") { // Check box to make sure only one request is sent to the engineers. If already sent then the script wont run
}
else {
var name = sheet.getRange('B'+lastRow).getValue(); // Get the value of the cell in column B and the last row for the email.
var unique = sheet.getRange('C'+lastRow).getValue(); // Repeat the previous step for all variables
var problem = sheet.getRange('D'+lastRow).getValue();
var design = sheet.getRange('E'+lastRow).getValue();
var phone = sheet.getRange('F'+lastRow).getValue();
var emaileng = sheet.getRange('H'+lastRow).getValue();
var asset = sheet.getRange('I'+lastRow).getValue();
var inventory = sheet.getRange('J'+lastRow).getValue();
var serial = sheet.getRange('K'+lastRow).getValue();
var directorate = sheet.getRange('L'+lastRow).getValue();
var department = sheet.getRange('M'+lastRow).getValue();
var location = sheet.getRange('N'+lastRow).getValue();
var emaildep = sheet.getRange('O'+lastRow).getValue();
var engineer = sheet.getRange('P'+lastRow).getValue();
var phoneengineer = sheet.getRange('Q'+lastRow).getValue();
if (priority=='Normal'){ // Check normal priority,
// The next section will send an email to the engineer with all the information from the service request combined with the machine information from the portfolio
// It is HTML coded with the variables added as javascript code.
// normal priority
var subject = "Att. "+engineer+" Service Request for " + asset ; // Set the subject for the email
var message = "<p>Dear " +engineer+",</p>"+ // Message of the email in HTLM code
"<p><b>"+name +"</b> has requested maintenance for <b>" + asset +"</b> with unique number <b>" + unique +"</b>.<br/>"+
"The problem description is as follows: <b>" + problem + "</b>. </p>"+
"<p>Contact details for the requester:<br/>"+
"Name:<b> "+ name +"</b><br/>"+
"Design:<b> "+design+"</b><br/>"+
"Phone number: <b>"+phone+"</b></p>"+
"<p>Device information:<br/>"+
"Asset Name: <b>"+asset+"</b><br/>"+
"Unique Number: <b>"+unique+ "</b><br/>"+
"Inventory Number: <b>"+inventory+"</b><br/>"+
"Serial Number: <b>"+serial+"</b><br/>"+
"Directorate: <b>"+directorate+"</b><br/>"+
"Department: <b>"+department+"</b><br/>"+
"Location: <b>"+location+"</b></p>"+
"<p>Kind regards,<br/>BioMedical Engineering Department MTRH</p>";
MailApp.sendEmail(emaileng, subject, message,{ // send email --> sendEmail(recipient, subject, body, options)
name: 'Biomedical Engineering Department MTRH',
htmlBody: message,
cc: emaildep+","+"calibrationmtrh@gmail.com, markchebutuk@mtrh.go.ke, hodbiomed@mtrh.go.ke",
});
// With many thanks to Salim, this is a trial phone server we can use to send text messages to the responsible engineers. Should be replaced with the official one from the hospital.
// paybill number for africas talking: 525900 accountno: salim1990
// TURN ON WHEN SMS SERVER IS WORKING!!!!
//var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phoneengineer+"&message=Dear "+engineer+", maintenance has been requested for "+asset+". Please check your email or the calibration computer for more information. MTRH");
}
else{
// If not normal urgency, then it is emergency priority. Thus jacob and mark also get a text message notification to help the process.
// The next section will send an email to the engineer with all the information from the service request combined with the machine information from the portfolio
// It is HTML coded with the variables added as javascript code.
var subject = "Att. "+engineer+" Emergency Service Request for " + asset ; // Set the subject for the email
var message = "<p><b>This is an Emergency service request.</b></p><p>Dear " +engineer+",</p>"+ // Message of the email in HTLM code
"<p><b>"+name +"</b> has requested Emergency maintenance for <b>" + asset +"</b> with unique number <b>" + unique +"</b>.<br/>"+
"The problem description is as follows: <b>" + problem + "</b>. <br />Please give this request high priority. </p>"+
"<p>Contact details for the requester:<br/>"+
"Name:<b> "+ name +"</b><br/>"+
"Design:<b> "+design+"</b><br/>"+
"Phone number: <b>"+phone+"</b></p>"+
"<p>Device information:<br/>"+
"Asset Name: <b>"+asset+"</b><br/>"+
"Unique Number: <b>"+unique+ "</b><br/>"+
"Inventory Number: <b>"+inventory+"</b><br/>"+
"Serial Number: <b>"+serial+"</b><br/>"+
"Directorate: <b>"+directorate+"</b><br/>"+
"Department: <b>"+department+"</b><br/>"+
"Location: <b>"+location+"</b></p>"+
"<p>Kind regards,<br/>BioMedical Engineering Department MTRH</p>";
MailApp.sendEmail(emaileng, subject, message,{ // send email --> sendEmail(recipient, subject, body, options)
name: 'Biomedical Engineering Department MTRH',
htmlBody: message,
cc: emaildep+","+"calibrationmtrh@gmail.com, markchebutuk@mtrh.go.ke, hodbiomed@mtrh.go.ke",
});
// With many thanks to Salim, this is a trial phone server we can use to send text messages to the responsible engineers. Should be replaced with the official one from the hospital.
// paybill number for africas talking: 525900 accountno: salim1990
// TURN ON WHEN SMS SERVER IS WORKING!!!!
//var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phoneengineer+"&message=Dear "+engineer+", Emergency maintenance has been requested for "+asset+" by "+name+" with phone number: "+phone+". Please check your email or the calibration computer for more information. MTRH");
//Text message to Mark to help coordinate:
// TURN ON WHEN SMS SERVER IS WORKING!!!!
//var phonemark = '0720252868';
//var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phonemark+"&message=Dear Mark, Emergency maintenance has been requested for "+asset+" by "+name+" with phone number: "+phone+". Please help coordinate this service request. More information can be found in your MTRH email. MTRH");
//Text message to Jacob to help coordinate:
// TURN ON WHEN SMS SERVER IS WORKING!!!!
//var phonejacob = '0722437391';
//var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phonemark+"&message=Dear Mark, Emergency maintenance has been requested for "+asset+" by "+name+" with phone number: "+phone+". Please help coordinate this service request. More information can be found in your MTRH email. MTRH");
}
sheet.getRange('R'+lastRow).setValue('Sent'); // Set to sent to avoid further emails about this when other forms are applied
var rowchange = sheet.getRange('S'+lastRow).getValue(); // Look up the last row for the unique number in the portfolio
var sheetrowchange = ss.getSheetByName('Portfolio'); // Forces the active sheet in the document to be the Portfolio
sheetrowchange.getRange('E'+rowchange).setValue('Service Requested'); // Set item to service requested in portfolio
// This part below requests a response email from the text message server. Letting you know whether or not the service works.
/* MailApp.sendEmail(emailAddress, subject, response,{ // send email --> sendEmail(recipient, subject, body, options)
name: 'Biomedical Engineering Department MTRH',
htmlBody: response,
});*/
}
// TURN ON WHEN SMS SERVER IS WORKING!!!!
/* if (phone == ""){ // in this section we send a message to the requester if they have entered their phone number. This message is to let them know that their request has been received
}
else {
// With many thanks to Salim, this is a trial phone server we can use to send text messages to the responsible engineers. Should be replaced with the official one from the hospital.
// paybill number for africas talking: 525900 accountno: salim1990
var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phone+"&message=Dear "+name+", your maintenance request for "+asset+" has been registered. The responsible engineer, "+engineer+", has been notified and will commence with maintenance as soon as possible. MTRH");
}
*/
}
// sent reminder after 7 days when no job card has been filled in after service requested.
function emailServiceRequest_weekLater(){ // activated by a daily trigger at 9 am.
var ss = SpreadsheetApp.getActiveSpreadsheet(); // Document actief maken in de functie
var sheet = ss.getSheetByName('Portfolio'); // Forces the active sheet in the document to be the service request
var amount = sheet.getRange('AG1').getValue(); // gets the counter value of the amount of unique values there are for a service follow up
if (amount>=0.0){
var i = 4; // set to 3 because the first 4 rows of information are empty, we need to start with the 4th, will increment with 1 in the loop
for (var n=1;n<(amount+1);n++){ // starts a for loop for the amount of unique values there are to get all the follow ups
if (ss.getRange('AG'+i).getValue() =="#N/A") // To prevent errors a check if there are any values at all, if not, terminate script
{
}
else{
var row = sheet.getRange('AG'+i).getValue(); // gets the value of the row for this equipment to get all the information
var status = sheet.getRange('E'+row).getValue(); // double check to see if the status is on service requested, also doing a check with the last maintenance performed in the sheet itself
if(status == 'Service Requested'){
var email = sheet.getRange('R'+row).getValue(); // Information acquisition for the equipment row and the information needed in the email and sms
var engineer = sheet.getRange('A'+row).getValue(); // Repeat previous step
var directorate = sheet.getRange('F'+row).getValue();
var department = sheet.getRange('G'+row).getValue();
var location = sheet.getRange('H'+row).getValue();
var name = sheet.getRange('C'+row).getValue();
var CCemail= sheet.getRange('T'+row).getValue();
var inventoryNumber = sheet.getRange('I'+row).getValue();
var serialNumber = sheet.getRange('J'+row).getValue();
var brand = sheet.getRange('K'+row).getValue();
var unique = sheet.getRange('B'+row).getValue();
var phoneengineer = sheet.getRange('S'+row).getValue();
var requester = sheet.getRange('Z'+row).getValue();
var problem = sheet.getRange('AB'+row).getValue();
var emaildep = sheet.getRange('T'+row).getValue();
// Sending an email to the engineer to remind about the service request
var message = "Dear "+engineer+",<p>This is a kind reminder that a week ago service was requested by "+requester+" for "+name+" with the following problem description: "+problem+" the following equipment:</p><p><b>Asset Name:</b> "+name+"<br/><b>Unique Number: </b>"+unique+"<b>Directorate:</b> "+directorate+"<br/><b>Department:</b> "+department+"<br/><b>Location:</b> "+location+"<br/><b>Inventory number:</b> "+inventoryNumber+"<br/><b>Serial number:</b> "+serialNumber+"<br/><b>Brand:</b> "+brand+"</p><p>It appears this machine has not yet been serviced. In case you already performed service on this machine, kindly fill in a job card for the repair.</p><p>Kind Regards, </p><p>BioMedical Engineering Department MTRH</p>"
var subject = "Att. "+engineer+" Reminder of requested service"
MailApp.sendEmail(email, subject, message,{
name: 'Biomedical Engineering Department MTRH',
htmlBody: message,
cc: emaildep+","+"calibrationmtrh@gmail.com, markchebutuk@mtrh.go.ke, hodbiomed@mtrh.go.ke",
});
// TURN ON WHEN SMS SERVER IS WORKING!!!!
// With many thanks to Salim, this is a trial phone server we can use to send text messages to the responsible engineers. Should be replaced with the official one from the hospital.
// paybill number for africas talking: 525900 accountno: salim1990
// var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phoneengineer+"&message=Dear "+engineer+", this is a kind reminder that a week ago service has been requested for "+name+" and this has not yet been resolved. Please check your email or the calibration computer for more information. MTRH");
}
var i = i +1 ; // increment the variable i with 1 to switch to the next service request reminder.
}}
}
}
function CreateJobCard() {
var sleepINT = 300
// specify doc template and get values from spread
var templateid = "1FjcFFon6IhVzfqCYXVAx1qn-AraKkwW-9BGPIuPs6jk"; // template file id, file ID in google can be found from the link, e.g.
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.toast("ENGINE INITIALIZING");
var sheet = ss.getSheetByName('Service History');
var JobCardno = sheet.getRange(1,55).getValue()
var data = sheet.getRange(sheet.getLastRow(), 1, 1, 45).getValues();
Utilities.sleep(sleepINT)
Logger.log(JobCardno)
ss.toast("data captured");
// Make a copy of the Job Card template, then Fill up it up with the data from the spreadsheet.
//NOTE: body.replace method does not have to be in any specific order.
for (var i in data) {
var row = data[i];
var docid = DriveApp.getFileById(templateid).makeCopy().getId();
var doc = DocumentApp.openById(docid);
var body = doc.getActiveSection();
body.replaceText("%UNIQUE%", row[2]);
body.replaceText("%COST%", row[4]);
body.replaceText("%NAMEREQUEST%", row[5]);
body.replaceText("%DESIGNATION%", row[6]);
//body.replaceText("%PROBLEM%", row[7]);
body.replaceText("%DIRECTORATE%", row[8]);
body.replaceText("%DEPARTMENT%", row[9]);
body.replaceText("%LOCATION%", row[10]);
body.replaceText("%ASSET%", row[11]);
body.replaceText("%SERIAL%", row[12]);
var startdate = Utilities.formatDate(row[15], "GMT+3", "dd-MM-yyyy"); // this is to formulate the date in a proper manner, otherwise it would say something like time, date and timezone
body.replaceText("%STARTDATE%", startdate);
var starttime = Utilities.formatDate(row[16], "GMT+3", "HH:mm");
body.replaceText("%STARTTIME%", starttime);
var enddate = Utilities.formatDate(row[17], "GMT+3", "dd-MM-yyyy");
body.replaceText("%ENDDATE%", enddate);
var endtime = Utilities.formatDate(row[18], "GMT+3", "HH:mm");
body.replaceText("%ENDTIME%", endtime);
body.replaceText("%NAME%", row[19]);
body.replaceText("%TYPE%", row[20]);
body.replaceText("%PRIORITY%", row[21]);
body.replaceText("%CAUSE%", row[22]);
body.replaceText("%DIAGNOSIS%", row[23]);
body.replaceText("%PREVENTION%", row[24]);
body.replaceText("%PARTS%", row[25]);
body.replaceText("%CALTYPE%", row[26]);
body.replaceText("%CERTIFICATE%", row[27]);
body.replaceText("%CHECK%", row[28]);
body.replaceText("%CURRENT%", row[29]);
body.replaceText("%VOLTAGE%", row[30]);
body.replaceText("%ACDC%", row[31]);
body.replaceText("%LEAKAGE%", row[32]);
body.replaceText("%PRESSURE%", row[33]);
body.replaceText("%TEMP%", row[34]);
body.replaceText("%INSULATION%", row[35]);
body.replaceText("%SPEED%", row[36]);
body.replaceText("%MASS%", row[37]);
body.replaceText("%OTHER%", row[38]);
body.replaceText("%SAFETY%", row[39]);
body.replaceText("%VERIFI%", row[40]);
body.replaceText("%FUNCTION%", row[41]);
body.replaceText("%CAL%", row[42]);
body.replaceText("%COMMENT%", row[43]);
body.replaceText("%STATUS%", row[44]);
doc.saveAndClose();
ss.toast("template data replaced");
//copy the modified template to the specified folder, then delete the first copy we made (to modify it)
var file = DriveApp.getFileById(doc.getId());
var newfolder = DriveApp.getFolderById("1GWNUwghVE8PQ1uFjQJKN4soWeFcCU437");
var oldfolder = DriveApp.getFolderById("1CDSL8zp34qNLJiokvH7sTpKxJblaRp3e");
newfolder.addFile(file);
oldfolder.removeFile(file);
Utilities.sleep(sleepINT)
ss.toast("Job card has been put in correct folder");
//customize the title for the Job Card
var name = doc.getName();
doc.setName(JobCardno + ' - Job Card - '+row[2]+' -'+row[19]);
ss.toast("Job card name changed");
//create and organize pdf version
var pdffolder = DriveApp.getFolderById("1GWNUwghVE8PQ1uFjQJKN4soWeFcCU437");
var pdfFILE = DriveApp.getFileById(doc.getId()).getAs('application/pdf');
pdfFILE.setName(doc.getName() + ".pdf");
var theFolder = pdffolder;
var theFile = theFolder.createFile(pdfFILE);
theFolder.addFile(theFile);
ss.toast("PDF generated");
ss.getRange('BC1').setValue(JobCardno+1);
// JobCardno +=1
Utilities.sleep(sleepINT)
}
}
//I'm not changing the name because of the trigger that uses this function. But it will now also send a message to the requester (if appliccable) to notify them that maintenance on the machine has been done
function setjobcardstatus() { // activated by on form submit trigger, built in checkbox to make sure it is only activated once
var ss = SpreadsheetApp.getActiveSpreadsheet(); // Document actief maken in de functie
var sheet = ss.getSheetByName('Service History'); // Forces the active sheet in the document to be the service history
var lastRow = sheet.getLastRow(); // Get last row of the active sheet
if (sheet.getRange('BA'+lastRow).getValue() == "processed") { // Check if item is already processed. If so, don't do this.
}
else {
var status = sheet.getRange('AS'+lastRow).getValue(); // Get the value of the status
sheet.getRange('BA'+lastRow).setValue('processed'); // Set value to processed to avoid futher actions upon triggers
var rowchange = sheet.getRange('BB'+lastRow).getValue(); // Get the row of the unique number in the portfolio
var sheetrowchange = ss.getSheetByName('Portfolio'); // Set active sheet to Portfolio
sheetrowchange.getRange('E'+rowchange).setValue(status); // Change status of the equipment to match the job card
// Additionally we send a message to the person who has requested service to let them know it has been resolved.
if (sheet.getRange('AS'+lastRow).getValue() == "Functioning") // Double check whether or not the machine now functions.
{
var row = sheet.getRange('BB'+lastRow).getValue(); // Get the row of the portfolio in which this machine is.
var portfolio = ss.getSheetByName('Portfolio'); // Also make portfolio active to get information from there
if (sheet.getRange('U'+lastRow) == "Corrective") { // Make sure the work that has been done is corrective, to solve an issue.
if (portfolio.getRange('Z'+row).getValue() == "") // Check whether or not there is a requester for this job.
{} // If there is no requester, terminate this script
else { // If there is a requester we continue.
if (portfolio.getRange('AI'+row).getValue() == "") // Double check for Phone number
{}
else{
var phonenumber = portfolio.getRange('AI'+row).getValue() ; //get the phone number for the text message and other information
var requester = portfolio.getRange('Z'+row).getValue();
var asset = portfolio.getRange('C'+row).getValue();
var ID = portfolio.getRange('B'+row).getValue();
// TURN ON WHEN SMS SERVER IS WORKING!!!!
// With many thanks to Salim, this is a trial phone server we can use to send text messages to the responsible engineers. Should be replaced with the official one from the hospital.
// paybill number for africas talking: 525900 accountno: salim1990
//var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phonenumber+"&message=Dear "+requester+", service has been performed on "+asset+" with ID number "+ID+". The engineer has indicated the status of the machine is now set to Functioning and is ready to be used again. Kind regards, BioMedical Engineering MTRH");
/* var subject = "Test to see if a user will get a notification";
var message = "test to see if a user will get a notification";
var emailAddress = 'rmvanrooden@hotmail.com';
MailApp.sendEmail(emailAddress, subject, message,{ // send email --> sendEmail(recipient, subject, body, options)
name: 'Biomedical Engineering Department MTRH',
htmlBody: message,
}); */
}
}
}
}
CreateJobCard()
}
// also trigger the function download xlsx, thiss to update the xls file in the drive, which will then update the google drive file on the calibration computer,
// this xls is connected to the P-touch 900W printer, and can than be used to print a calibration and/or PPM sticker.
downloadXLSX()
}
// send a email the first of the month to all biomeds who have planned prevented maintenance for that month
function PPMmail_1stofMonth_firstTrigger() { // This function will run ~20 times in 20 different trigger, it took too long to combine all PPM messages in one go due to time limitations on google. Therefore we call on this script 20 times
var sheet = SpreadsheetApp.getActive(); // Select the right spreadsheet for this function, PPM.
var ss = sheet.getSheetByName("PPM schedule");
var combinations = ss.getRange('AH5').getValue() // # of unique combinations PPM which have PPM upcomming month
var counter = ss.getRange('AH1').getValue() // Rather than a for loop we have a counter on the sheet itself, works the same but due to time limitations we had to build a way around. Every time the script runs it will at 1 to the counter. Last one will reset the counter back to 1
if (counter>combinations){ // As soon as the counter is bigger than the amount of combinations the script will terminate
}
else {
var rows = ss.getRange('AP1').getValue(); // Gets the amount of rows (+1 for some reason but this is balanced in the for loop) in the PPM sheet
var No_devices = ss.getRange('AS1').getValue(); // gets the total number of devices that require PPM this month
var biomedcombi = ss.getRange('AG'+(counter+3)).getValue() // Gets the unique combination for the biomed with PPM, to make sure he will not get multiple emails but just the one
var devicerows = ss.getRange('AS5:AS'+(No_devices+4)).getValues() //gets the row number for each device, this step decreases computatation time, instead of running through the whole portal, we only go through the required rows.
var k=""; // this variable is used for speeding up the script
var content = ''; // start for the message content, will be added to in the for loop
var date = ss.getRange('AP5').getValue(); // Get the current date
var number = 0; // Number counter for the amount of machines a person has in PPM
for (var j=0;j<No_devices; j++) { // Loops through the entire sheet, to search for the unique value, thus this big loop
if (ss.getRange('AF'+devicerows[j]).getValue() == biomedcombi) { // This is the search criterium for the PPM unique value mentioned above
if(k == ""){ var k=j};
var number=number+1; // Counts the amount of machines for PPM
var uniquenr = ss.getRange('B'+devicerows[j]).getValue(); // Get values needed for email and text message
var assetName = ss.getRange('C'+devicerows[j]).getValue(); // Repeat
var location = ss.getRange('D'+devicerows[j]).getValue();
//ss.getRange('AJ'+j).setValue(date); // Sets the value of today as the last planned PPM date, for the follow up email
var content = content + "<p>Unique number:<b> "+ uniquenr +"</b><br/>"+"Asset Name:<b> "+ assetName+"</b><br/>Location:<b> "+location+"</b></p>" // This stores a machine in the message content every time one has been found that meets the criterium
}
}
var biomed = ss.getRange('A'+devicerows[k]).getValue();
var emailAddress = ss.getRange('AB'+devicerows[k]).getValue();
var phoneengineer = ss.getRange('AQ'+devicerows[k]).getValue();
var emaildept = ss.getRange('AR'+devicerows[k]).getValue();
var subject = "Att. "+biomed+" planned prevented Maintenance for this month " ; // Set the subject for the email
var message = "<p>Dear " +biomed+",</p>"+ // Starting the message content which will include the previously gathered information
"<p>For this month "+number+" devices require planned prevented maintenance. The machines which require planned preventative maintenance are: </p>" + content +"<p>If one or more of these devices is not scheduled for PPM this month, please update the PPM schedule at the calibration computer at your earliest convenience.<p>Kind regards,<br/> MTRH Biomedical Engineering.</p>";
MailApp.sendEmail(emailAddress, subject, message,{ // send email --> sendEmail(recipient, subject, body, options)
name: 'Biomedical Engineering Department MTRH',
htmlBody: message,
cc: emaildept+","+"hodbiomed@mtrh.go.ke",
});
// TURN ON WHEN SMS SERVER IS WORKING!!!!
// With many thanks to Salim, this is a trial phone server we can use to send text messages to the responsible engineers. Should be replaced with the official one from the hospital.
// paybill number for africas talking: 525900 accountno: salim1990
// var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phoneengineer+"&message=Dear "+biomed+", this month you have "+number+" machines that require Planned Preventative Maintenance. Please check your email or the calibration computer for more information. MTRH");
ss.getRange('AH1').setValue(counter+1); // Add 1 to the counter to make sure the next biomed will get an email on the next trigger/time this script runs.
}
}
// This function runs on the last trigger for PPM, and resets the value in the end back to 1.
function PPMmail_1stofMonth_lastTrigger() { // This function will run 1 time after the previous function has been performed ~20 times. And reset the counter back to 1
var sheet = SpreadsheetApp.getActive(); // Select the right spreadsheet for this function, PPM.
var ss = sheet.getSheetByName("PPM schedule");
var combinations = ss.getRange('AH5').getValue() // # of unique combinations PPM which have PPM upcomming month
var counter = ss.getRange('AH1').getValue() // Rather than a for loop we have a counter on the sheet itself, works the same but due to time limitations we had to build a way around. Every time the script runs it will at 1 to the counter. Last one will reset the counter back to 1
if (counter>combinations){ // As soon as the counter is bigger than the amount of combinations the script will terminate
ss.getRange('AH1').setValue(1);
}
else {
var rows = ss.getRange('AP1').getValue(); // Gets the amount of rows (+1 for some reason but this is balanced in the for loop) in the PPM sheet
var No_devices = ss.getRange('AS1').getValue(); // gets the total number of devices that require PPM this month
var biomedcombi = ss.getRange('AG'+(counter+3)).getValue() // Gets the unique combination for the biomed with PPM, to make sure he will not get multiple emails but just the one
var devicerows = ss.getRange('AS5:AS'+(No_devices+4)).getValues() //gets the row number for each device, this step decreases computatation time, instead of running through the whole portal, we only go through the required rows.
var k=""; // this variable is used for speeding up the script
var content = ''; // start for the message content, will be added to in the for loop
var date = ss.getRange('AP5').getValue(); // Get the current date
var number = 0; // Number counter for the amount of machines a person has in PPM
for (var j=0;j<No_devices; j++) { // Loops through the entire sheet, to search for the unique value, thus this big loop
if (ss.getRange('AF'+devicerows[j]).getValue() == biomedcombi) { // This is the search criterium for the PPM unique value mentioned above
if(k == ""){ var k=j};
var number=number+1; // Counts the amount of machines for PPM
var uniquenr = ss.getRange('B'+devicerows[j]).getValue(); // Get values needed for email and text message
var assetName = ss.getRange('C'+devicerows[j]).getValue(); // Repeat
var location = ss.getRange('D'+devicerows[j]).getValue();
//ss.getRange('AJ'+j).setValue(date); // Sets the value of today as the last planned PPM date, for the follow up email
var content = content + "<p>Unique number:<b> "+ uniquenr +"</b><br/>"+"Asset Name:<b> "+ assetName+"</b><br/>Location:<b> "+location+"</b></p>" // This stores a machine in the message content every time one has been found that meets the criterium
}
}
var biomed = ss.getRange('A'+devicerows[k]).getValue();
var emailAddress = ss.getRange('AB'+devicerows[k]).getValue();
var phoneengineer = ss.getRange('AQ'+devicerows[k]).getValue();
var emaildept = ss.getRange('AR'+devicerows[k]).getValue();
var subject = "Att. "+biomed+" planned prevented Maintenance for this month " ; // Set the subject for the email
var message = "<p>Dear " +biomed+",</p>"+ // Starting the message content which will include the previously gathered information
"<p>For this month "+number+" devices require planned prevented maintenance. The machines which require planned preventative maintenance are: </p>" + content +"<p>If one or more of these devices is not scheduled for PPM this month, please update the PPM schedule at the calibration computer at your earliest convenience.<p>Kind regards,<br/> MTRH Biomedical Engineering.</p>";
MailApp.sendEmail(emailAddress, subject, message,{ // send email --> sendEmail(recipient, subject, body, options)
name: 'Biomedical Engineering Department MTRH',
htmlBody: message,
cc: emaildept+","+"hodbiomed@mtrh.go.ke",
});
// TURN ON WHEN SMS SERVER IS WORKING!!!!
// With many thanks to Salim, this is a trial phone server we can use to send text messages to the responsible engineers. Should be replaced with the official one from the hospital.
// paybill number for africas talking: 525900 accountno: salim1990
// var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phoneengineer+"&message=Dear "+biomed+", this month you have "+number+" machines that require Planned Preventative Maintenance. Please check your email or the calibration computer for more information. MTRH");
ss.getRange('AH1').setValue(1); //Set the value back to 1, so the system wills tart anew when it runs the next month.
}
}
// send a email the first of the month to all biomeds who have Calibration scheduled for that month
function Calmail_1stofMonth_firstTrigger() { // This function will run ~20 times in 20 different trigger, it took too long to combine all PPM messages in one go due to time limitations on google. Therefore we call on this script 20 times
var sheet = SpreadsheetApp.getActive(); // Select the right spreadsheet for this function, PPM.
var ss = sheet.getSheetByName("Calibration Shedule");
var combinations = ss.getRange('AF5').getValue() // # of unique combinations Calibration which have Calibration upcomming month
var counter = ss.getRange('AF1').getValue() // Rather than a for loop we have a counter on the sheet itself, works the same but due to time limitations we had to build a way around. Every time the script runs it will at 1 to the counter. Last one will reset the counter back to 1
if (counter>combinations){ // As soon as the counter is bigger than the amount of combinations the script will terminate
}
else {
var rows = ss.getRange('AN1').getValue(); // Gets the amount of rows (+1 for some reason but this is balanced in the for loop) in the PPM sheet
var No_devices = ss.getRange('AQ1').getValue();
var biomedcombi = ss.getRange('AE'+(counter+3)).getValue() // Gets the unique combination for the biomed with PPM, to make sure he will not get multiple emails but just the one
var devicerows = ss.getRange('AQ5:AQ'+(No_devices+4)).getValues() //gets the row number for each device, this step decreases computatation time, instead of running through the whole portal, we only go through the required rows.
var k="";
var content = ''; // start for the message content, will be added to in the for loop
var date = ss.getRange('AN5').getValue(); // Get the current date
var number = 0; // Number counter for the amount of machines a person has in PPM
for (var j=0;j<No_devices; j++) { // Loops through the entire sheet, to search for the unique value, thus this big loop
if (ss.getRange('AD'+devicerows[j]).getValue() == biomedcombi) { // This is the search criterium for the Calibration unique value mentioned above
if(k == ""){ var k=j};
var number=number+1; // Counts the amount of machines for Calibration
var uniquenr = ss.getRange('B'+devicerows[j]).getValue(); // Get values needed for email and text message
var assetName = ss.getRange('C'+devicerows[j]).getValue(); // Repeat
var location = ss.getRange('D'+devicerows[j]).getValue();
sheet.toast(uniquenr)
// ss.getRange('AH'+j).setValue(date); // Sets the value of today as the last planned Calibration date, for the follow up email
var content = content + "<p>Unique number:<b> "+ uniquenr +"</b><br/>"+"Asset Name:<b> "+ assetName+"</b><br/>Location:<b> "+location+"</b></p>" // This stores a machine in the message content every time one has been found that meets the criterium
}
}
var biomed = ss.getRange('A'+devicerows[k]).getValue();
var emailAddress = ss.getRange('AA'+devicerows[k]).getValue();
var phoneengineer = ss.getRange('AO'+devicerows[k]).getValue();
var emaildept = ss.getRange('AP'+devicerows[k]).getValue();
var subject = "Att. "+biomed+" Calibration scheduled for this month " ; // Set the subject for the email
var message = "<p>Dear " +biomed+",</p>"+ // Starting the message content which will include the previously gathered information
"<p>For this month "+number+" devices require calibration. The machines which require calibration are: </p>" + content +"<p>If one or more of these devices is not scheduled for calibration this month, please update the calibration schedule at the calibration computer at your earliest convenience.<p>Kind regards,<br/> MTRH Biomedical Engineering.</p>";
MailApp.sendEmail(emailAddress, subject, message,{ // send email --> sendEmail(recipient, subject, body, options)
name: 'Biomedical Engineering Department MTRH',
htmlBody: message,
cc: emaildept+","+"hodbiomed@mtrh.go.ke",
});
// TURN ON WHEN SMS SERVER IS WORKING!!!!
// With many thanks to Salim, this is a trial phone server we can use to send text messages to the responsible engineers. Should be replaced with the official one from the hospital.
// paybill number for africas talking: 525900 accountno: salim1990
//var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phoneengineer+"&message=Dear "+biomed+", this month you have "+number+" machines that require calibration. Please check your email or the calibration computer for more information. MTRH");
ss.getRange('AF1').setValue(counter+1); // Add 1 to the counter to make sure the next biomed will get an email on the next trigger/time this script runs.
}
}
// This function runs on the last trigger for Calibration, and resets the value in the end back to 1.
function Calmail_1stofMonth_lastTrigger() { // This function will run 1 time after the previous function has been performed ~20 times. And reset the counter back to 1
var sheet = SpreadsheetApp.getActive(); // Select the right spreadsheet for this function, PPM.
var ss = sheet.getSheetByName("Calibration Shedule");
var combinations = ss.getRange('AF5').getValue() // # of unique combinations Calibration which have Calibration upcomming month
var counter = ss.getRange('AF1').getValue() // Rather than a for loop we have a counter on the sheet itself, works the same but due to time limitations we had to build a way around. Every time the script runs it will at 1 to the counter. Last one will reset the counter back to 1
if (counter>combinations){ // As soon as the counter is bigger than the amount of combinations the script will terminate
ss.getRange('AF1').setValue(1); //Set the value back to 1, so the system wills tart anew when it runs the next month.
}
else {
var rows = ss.getRange('AN1').getValue(); // Gets the amount of rows (+1 for some reason but this is balanced in the for loop) in the PPM sheet
var No_devices = ss.getRange('AQ1').getValue();
var biomedcombi = ss.getRange('AE'+(counter+3)).getValue() // Gets the unique combination for the biomed with PPM, to make sure he will not get multiple emails but just the one
var devicerows = ss.getRange('AQ5:AQ'+(No_devices+4)).getValues() //gets the row number for each device, this step decreases computatation time, instead of running through the whole portal, we only go through the required rows.
var k="";
var content = ''; // start for the message content, will be added to in the for loop
var date = ss.getRange('AN5').getValue(); // Get the current date
var number = 0; // Number counter for the amount of machines a person has in PPM
for (var j=0;j<No_devices; j++) { // Loops through the entire sheet, to search for the unique value, thus this big loop
if (ss.getRange('AD'+devicerows[j]).getValue() == biomedcombi) { // This is the search criterium for the Calibration unique value mentioned above
if(k == ""){ var k=j};
var number=number+1; // Counts the amount of machines for Calibration
var uniquenr = ss.getRange('B'+devicerows[j]).getValue(); // Get values needed for email and text message
var assetName = ss.getRange('C'+devicerows[j]).getValue(); // Repeat
var location = ss.getRange('D'+devicerows[j]).getValue();
sheet.toast(uniquenr)
// ss.getRange('AH'+j).setValue(date); // Sets the value of today as the last planned Calibration date, for the follow up email
var content = content + "<p>Unique number:<b> "+ uniquenr +"</b><br/>"+"Asset Name:<b> "+ assetName+"</b><br/>Location:<b> "+location+"</b></p>" // This stores a machine in the message content every time one has been found that meets the criterium
}
}
var biomed = ss.getRange('A'+devicerows[k]).getValue();
var emailAddress = ss.getRange('AA'+devicerows[k]).getValue();
var phoneengineer = ss.getRange('AO'+devicerows[k]).getValue();
var emaildept = ss.getRange('AP'+devicerows[k]).getValue();
var subject = "Att. "+biomed+" Calibration scheduled for this month " ; // Set the subject for the email
var message = "<p>Dear " +biomed+",</p>"+ // Starting the message content which will include the previously gathered information
"<p>For this month "+number+" devices require calibration. The machines which require calibration are: </p>" + content +"<p>If one or more of these devices is not scheduled for calibration this month, please update the calibration schedule at the calibration computer at your earliest convenience.<p>Kind regards,<br/> MTRH Biomedical Engineering.</p>";
MailApp.sendEmail(emailAddress, subject, message,{ // send email --> sendEmail(recipient, subject, body, options)
name: 'Biomedical Engineering Department MTRH',
htmlBody: message,
cc: emaildept+","+"hodbiomed@mtrh.go.ke",
});
// TURN ON WHEN SMS SERVER IS WORKING!!!!
// With many thanks to Salim, this is a trial phone server we can use to send text messages to the responsible engineers. Should be replaced with the official one from the hospital.
// paybill number for africas talking: 525900 accountno: salim1990
// var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phoneengineer+"&message=Dear "+biomed+", this month you have "+number+" machines that require calibration. Please check your email or the calibration computer for more information. MTRH");
ss.getRange('AF1').setValue(1); //Set the value back to 1, so the system wills tart anew when it runs the next month.
}
}
// send a email the 16th of the month to all biomeds who have not yet performed their Planned Preventative Maintenance
function PPMmail_16ofMonth_firstTrigger() { // This function will run ~20 times in 20 different trigger, it took too long to combine all PPM messages in one go due to time limitations on google. Therefore we call on this script 20 times
var sheet = SpreadsheetApp.getActive(); // Select the right spreadsheet for this function, PPM.
var ss = sheet.getSheetByName("PPM schedule");
var combinations = ss.getRange('AO5').getValue(); // # of unique combinations PPM which have PPM upcomming month
var counter = ss.getRange('AO1').getValue(); // Rather than a for loop we have a counter on the sheet itself, works the same but due to time limitations we had to build a way around. Every time the script runs it will at 1 to the counter. Last one will reset the counter back to 1
if (counter>combinations){ // As soon as the counter is bigger than the amount of combinations the script will terminate
}
else {
var rows = ss.getRange('AP1').getValue(); // Gets the amount of rows (+1 for some reason but this is balanced in the for loop) in the PPM sheet
var No_devices = ss.getRange('AS1').getValue(); // gets the total number of devices that require PPM this month
var biomedcombi = ss.getRange('AN'+(counter+3)).getValue() ; // Gets the unique combination for the biomed with PPM, to make sure he will not get multiple emails but just the one
var devicerows = ss.getRange('AS5:AS'+(No_devices+4)).getValues() //gets the row number for each device, this step decreases computatation time, instead of running through the whole portal, we only go through the required rows.
var k=""; // this variable is used for speeding up the script
var content = ''; // start for the message content, will be added to in the for loop
var number = 0; // Number counter for the amount of machines a person has in PPM
for (var j=0;j<No_devices; j++) { // Loops through the entire sheet, to search for the unique value, thus this big loop
if (ss.getRange('AM'+devicerows[j]).getValue() == biomedcombi) { // This is the search criterium for the PPM unique value mentioned above
if(k == ""){ var k=j};
var number=number+1; // Counts the amount of machines for PPM
var uniquenr = ss.getRange('B'+devicerows[j]).getValue(); // Get values needed for email and text message
var assetName = ss.getRange('C'+devicerows[j]).getValue(); // Repeat
var location = ss.getRange('D'+devicerows[j]).getValue();
var content = content + "<p>Unique number:<b> "+ uniquenr +"</b><br/>"+"Asset Name:<b> "+ assetName+"</b><br/>Location:<b> "+location+"</b></p>" // This stores a machine in the message content every time one has been found that meets the criterium
}
}
var biomed = ss.getRange('A'+devicerows[k]).getValue();
var emailAddress = ss.getRange('AB'+devicerows[k]).getValue();
var phoneengineer = ss.getRange('AQ'+devicerows[k]).getValue();
var emaildept = ss.getRange('AR'+devicerows[k]).getValue();
var subject = "Att. "+biomed+" reminder for planned prevented Maintenance for this month " ; // Set the subject for the email
var message = "<p>Dear " +biomed+",</p>"+ // Starting the message content which will include the previously gathered information
"<p>This is a reminder for your scheduled Planned Preventative Maintenance for this month. If you already performed this PPM please fill in the job card in the Biomed Portal. For this month "+number+" devices still require planned prevented maintenance. The machines which still require planned preventative maintenance are: </p>" + content +"<p>If one or more of these devices is not scheduled for PPM this month, please update the PPM schedule at the calibration computer at your earliest convenience.<p>Kind regards,<br/> MTRH Biomedical Engineering.</p>";
MailApp.sendEmail(emailAddress, subject, message,{ // send email --> sendEmail(recipient, subject, body, options)
name: 'Biomedical Engineering Department MTRH',
htmlBody: message,
cc: emaildept+","+"hodbiomed@mtrh.go.ke",
});
// TURN ON WHEN SMS SERVER IS WORKING!!!!
// With many thanks to Salim, this is a trial phone server we can use to send text messages to the responsible engineers. Should be replaced with the official one from the hospital.
// paybill number for africas talking: 525900 accountno: salim1990
// var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phoneengineer+"&message=Dear "+biomed+", this is a reminder that you have Planned Preventative Maintenance scheduled this month. You still have "+number+" machines that require PPM. In case you already performed the PPM, please fill in the job card. Please check your email or the calibration computer for more information. MTRH");
ss.getRange('AO1').setValue(counter+1); // Add 1 to the counter to make sure the next biomed will get an email on the next trigger/time this script runs.
}
}
// send a email the 16th of the month to all biomeds who have not yet performed their Planned Preventative Maintenance. This is the last trigger of the month
function PPMmail_16ofMonth_lastTrigger() { // This function will run 1 time after the previous function has been performed ~20 times.
var sheet = SpreadsheetApp.getActive(); // Select the right spreadsheet for this function, PPM.
var ss = sheet.getSheetByName("PPM schedule");
var combinations = ss.getRange('AO5').getValue(); // # of unique combinations PPM which have PPM upcomming month
var counter = ss.getRange('AO1').getValue(); // Rather than a for loop we have a counter on the sheet itself, works the same but due to time limitations we had to build a way around. Every time the script runs it will at 1 to the counter. Last one will reset the counter back to 1
if (counter>combinations){ // As soon as the counter is bigger than the amount of combinations the script will terminate
ss.getRange('AO1').setValue(1); //Set the value back to 1, so the system wills tart anew when it runs the next month.
}
else {
var rows = ss.getRange('AP1').getValue(); // Gets the amount of rows (+1 for some reason but this is balanced in the for loop) in the PPM sheet
var No_devices = ss.getRange('AS1').getValue(); // gets the total number of devices that require PPM this month
var biomedcombi = ss.getRange('AN'+(counter+3)).getValue() ; // Gets the unique combination for the biomed with PPM, to make sure he will not get multiple emails but just the one
var devicerows = ss.getRange('AS5:AS'+(No_devices+4)).getValues() //gets the row number for each device, this step decreases computatation time, instead of running through the whole portal, we only go through the required rows.
var k=""; // this variable is used for speeding up the script
var content = ''; // start for the message content, will be added to in the for loop
var number = 0; // Number counter for the amount of machines a person has in PPM
for (var j=0;j<No_devices; j++) { // Loops through the entire sheet, to search for the unique value, thus this big loop
if (ss.getRange('AM'+devicerows[j]).getValue() == biomedcombi) { // This is the search criterium for the PPM unique value mentioned above
if(k == ""){ var k=j};
var number=number+1; // Counts the amount of machines for PPM
var uniquenr = ss.getRange('B'+devicerows[j]).getValue(); // Get values needed for email and text message
var assetName = ss.getRange('C'+devicerows[j]).getValue(); // Repeat
var location = ss.getRange('D'+devicerows[j]).getValue();
var content = content + "<p>Unique number:<b> "+ uniquenr +"</b><br/>"+"Asset Name:<b> "+ assetName+"</b><br/>Location:<b> "+location+"</b></p>" // This stores a machine in the message content every time one has been found that meets the criterium
}
}
var biomed = ss.getRange('A'+devicerows[k]).getValue();
var emailAddress = ss.getRange('AB'+devicerows[k]).getValue();
var phoneengineer = ss.getRange('AQ'+devicerows[k]).getValue();
var emaildept = ss.getRange('AR'+devicerows[k]).getValue();
var subject = "Att. "+biomed+" reminder for planned prevented Maintenance for this month " ; // Set the subject for the email
var message = "<p>Dear " +biomed+",</p>"+ // Starting the message content which will include the previously gathered information
"<p>This is a reminder for your scheduled Planned Preventative Maintenance for this month. If you already performed this PPM please fill in the job card in the Biomed Portal. For this month "+number+" devices still require planned prevented maintenance. The machines which still require planned preventative maintenance are: </p>" + content +"<p>If one or more of these devices is not scheduled for PPM this month, please update the PPM schedule at the calibration computer at your earliest convenience.<p>Kind regards,<br/> MTRH Biomedical Engineering.</p>";
MailApp.sendEmail(emailAddress, subject, message,{ // send email --> sendEmail(recipient, subject, body, options)
name: 'Biomedical Engineering Department MTRH',
htmlBody: message,
cc: emaildept+","+"hodbiomed@mtrh.go.ke",
});
// TURN ON WHEN SMS SERVER IS WORKING!!!!
// With many thanks to Salim, this is a trial phone server we can use to send text messages to the responsible engineers. Should be replaced with the official one from the hospital.
// paybill number for africas talking: 525900 accountno: salim1990
// var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phoneengineer+"&message=Dear "+biomed+", this is a reminder that you have Planned Preventative Maintenance scheduled this month. You still have "+number+" machines that require PPM. In case you already performed the PPM, please fill in the job card. Please check your email or the calibration computer for more information. MTRH");
// change to engineers phone number
ss.getRange('AO1').setValue(1); //Set the value back to 1, so the system wills tart anew when it runs the next month.
}
}
// send a email the 17th of the month to all biomeds who have not yet performed scheduled calibration
function Calmail_17ofMonth_firstTrigger() { // This function will run ~20 times in 20 different trigger, it took too long to combine all calibration messages in one go due to time limitations on google. Therefore we call on this script 20 times
var sheet = SpreadsheetApp.getActive(); // Select the right spreadsheet for this function, calibration.
var ss = sheet.getSheetByName("Calibration Shedule");
var combinations = ss.getRange('AM5').getValue(); // # of unique combinations for calibration which have not done their calibration yet
var counter = ss.getRange('AM1').getValue(); // Rather than a for loop we have a counter on the sheet itself, works the same but due to time limitations we had to build a way around. Every time the script runs it will at 1 to the counter. Last one will reset the counter back to 1
if (counter>combinations){ // As soon as the counter is bigger than the amount of combinations the script will terminate
}
else {
var rows = ss.getRange('AN1').getValue(); // Gets the amount of rows (+1 for some reason but this is balanced in the for loop) in the PPM sheet
var No_devices = ss.getRange('AQ1').getValue();
var biomedcombi = ss.getRange('AL'+(counter+3)).getValue() ; // Gets the unique combination for the biomed with PPM, to make sure he will not get multiple emails but just the one
var devicerows = ss.getRange('AQ5:AQ'+(No_devices+4)).getValues() //gets the row number for each device, this step decreases computatation time, instead of running through the whole portal, we only go through the required rows.
var k="";
var content = ''; // start for the message content, will be added to in the for loop
var number = 0; // Number counter for the amount of machines a person has in PPM
for (var j=0;j<No_devices; j++) { // Loops through the entire sheet, to search for the unique value, thus this big loop
if (ss.getRange('AK'+devicerows[j]).getValue() == biomedcombi) { // This is the search criterium for the calibration unique value mentioned above
if(k == ""){ var k=j};
var number=number+1; // Counts the amount of machines for calibration reminder
var uniquenr = ss.getRange('B'+devicerows[j]).getValue(); // Get values needed for email and text message
var assetName = ss.getRange('C'+devicerows[j]).getValue(); // Repeat
var location = ss.getRange('D'+devicerows[j]).getValue();
sheet.toast(uniquenr)
var content = content + "<p>Unique number:<b> "+ uniquenr +"</b><br/>"+"Asset Name:<b> "+ assetName+"</b><br/>Location:<b> "+location+"</b></p>" // This stores a machine in the message content every time one has been found that meets the criterium
}
}
var biomed = ss.getRange('A'+devicerows[k]).getValue();
var emailAddress = ss.getRange('AA'+devicerows[k]).getValue();
var phoneengineer = ss.getRange('AO'+devicerows[k]).getValue();
var emaildept = ss.getRange('AP'+devicerows[k]).getValue();
var subject = "Att. "+biomed+" reminder for scheduled calibration for this month " ; // Set the subject for the email
var message = "<p>Dear " +biomed+",</p>"+ // Starting the message content which will include the previously gathered information
"<p>This is a reminder for your scheduled calibration for this month. If you already performed this calibration please fill in the job card in the Biomed Portal. For this month "+number+" devices still require their scheduled calibration. The machines which still require calibration: </p>" + content +"<p>If one or more of these devices is not scheduled for calibration this month, please update the calibration schedule at the calibration computer at your earliest convenience.<p>Kind regards,<br/> MTRH Biomedical Engineering.</p>";
MailApp.sendEmail(emailAddress, subject, message,{ // send email --> sendEmail(recipient, subject, body, options)
name: 'Biomedical Engineering Department MTRH',
htmlBody: message,
cc: emaildept+","+"hodbiomed@mtrh.go.ke",
});
// TURN ON WHEN SMS SERVER IS WORKING!!!!
// With many thanks to Salim, this is a trial phone server we can use to send text messages to the responsible engineers. Should be replaced with the official one from the hospital.
// paybill number for africas talking: 525900 accountno: salim1990
//var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phoneengineer+"&message=Dear "+biomed+", this is a reminder that you have calibration scheduled this month. You still have "+number+" machines that require calibration. In case you already performed the calibration, please fill in the job card. Please check your email or the calibration computer for more information. MTRH");
ss.getRange('AM1').setValue(counter+1); // Add 1 to the counter to make sure the next biomed will get an email on the next trigger/time this script runs.
}
}
// send a email the 17th of the month to all biomeds who have not yet performed scheduled calibration
function Calmail_17ofMonth_lastTrigger() { // This function will run 1 time and sets the counter back to 1 in the end.
var sheet = SpreadsheetApp.getActive(); // Select the right spreadsheet for this function, calibration.
var ss = sheet.getSheetByName("Calibration Shedule");
var combinations = ss.getRange('AM5').getValue(); // # of unique combinations for calibration which have not done their calibration yet
var counter = ss.getRange('AM1').getValue(); // Rather than a for loop we have a counter on the sheet itself, works the same but due to time limitations we had to build a way around. Every time the script runs it will at 1 to the counter. Last one will reset the counter back to 1
if (counter>combinations){ // As soon as the counter is bigger than the amount of combinations the script will terminate
ss.getRange('AM1').setValue(1); // if counter>combination it means everyone got the message, then we can just set it back to 1 for next month.
}
else {
var rows = ss.getRange('AN1').getValue(); // Gets the amount of rows (+1 for some reason but this is balanced in the for loop) in the PPM sheet
var No_devices = ss.getRange('AQ1').getValue();
var biomedcombi = ss.getRange('AL'+(counter+3)).getValue() ; // Gets the unique combination for the biomed with PPM, to make sure he will not get multiple emails but just the one
var devicerows = ss.getRange('AQ5:AQ'+(No_devices+4)).getValues() //gets the row number for each device, this step decreases computatation time, instead of running through the whole portal, we only go through the required rows.
var k="";
var content = ''; // start for the message content, will be added to in the for loop
var number = 0; // Number counter for the amount of machines a person has in PPM
for (var j=0;j<No_devices; j++) { // Loops through the entire sheet, to search for the unique value, thus this big loop
if (ss.getRange('AK'+devicerows[j]).getValue() == biomedcombi) { // This is the search criterium for the calibration unique value mentioned above
if(k == ""){ var k=j};
var number=number+1; // Counts the amount of machines for calibration reminder
var uniquenr = ss.getRange('B'+devicerows[j]).getValue(); // Get values needed for email and text message
var assetName = ss.getRange('C'+devicerows[j]).getValue(); // Repeat
var location = ss.getRange('D'+devicerows[j]).getValue();
sheet.toast(uniquenr)
var content = content + "<p>Unique number:<b> "+ uniquenr +"</b><br/>"+"Asset Name:<b> "+ assetName+"</b><br/>Location:<b> "+location+"</b></p>" // This stores a machine in the message content every time one has been found that meets the criterium
}
}
var biomed = ss.getRange('A'+devicerows[k]).getValue();
var emailAddress = ss.getRange('AA'+devicerows[k]).getValue();
var phoneengineer = ss.getRange('AO'+devicerows[k]).getValue();
var emaildept = ss.getRange('AP'+devicerows[k]).getValue();
}}/*
var subject = "Att. "+biomed+" reminder for scheduled calibration for this month " ; // Set the subject for the email
var message = "<p>Dear " +biomed+",</p>"+ // Starting the message content which will include the previously gathered information
"<p>This is a reminder for your scheduled calibration for this month. If you already performed this calibration please fill in the job card in the Biomed Portal. For this month "+number+" devices still require their scheduled calibration. The machines which still require calibration: </p>" + content +"<p>If one or more of these devices is not scheduled for calibration this month, please update the calibration schedule at the calibration computer at your earliest convenience.<p>Kind regards,<br/> MTRH Biomedical Engineering.</p>";
MailApp.sendEmail(emailAddress, subject, message,{ // send email --> sendEmail(recipient, subject, body, options)
name: 'Biomedical Engineering Department MTRH',
htmlBody: message,
cc: emaildept+","+"hodbiomed@mtrh.go.ke",
});
// TURN ON WHEN SMS SERVER IS WORKING!!!!
// With many thanks to Salim, this is a trial phone server we can use to send text messages to the responsible engineers. Should be replaced with the official one from the hospital.
// paybill number for africas talking: 525900 accountno: salim1990
// var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phoneengineer+"&message=Dear "+biomed+", this is a reminder that you have calibration scheduled this month. You still have "+number+" machines that require calibration. In case you already performed the calibration, please fill in the job card. Please check your email or the calibration computer for more information. MTRH");
ss.getRange('AM1').setValue(1); // Add 1 to the counter to make sure the next biomed will get an email on the next trigger/time this script runs.
}
}
*/
// send a email the 27th of the month to all biomeds who have not yet performed their Planned Preventative Maintenance
function PPMmail_27thofMonth_firstTrigger() { // This function will run ~20 times in 20 different trigger, it took too long to combine all PPM messages in one go due to time limitations on google. Therefore we call on this script 20 times
var sheet = SpreadsheetApp.getActive(); // Select the right spreadsheet for this function, PPM.
var ss = sheet.getSheetByName("PPM schedule");
var combinations = ss.getRange('AO5').getValue(); // # of unique combinations PPM which have PPM upcomming month
var counter = ss.getRange('AM1').getValue(); // Rather than a for loop we have a counter on the sheet itself, works the same but due to time limitations we had to build a way around. Every time the script runs it will at 1 to the counter. Last one will reset the counter back to 1
var k="";
if (counter>combinations){ // As soon as the counter is bigger than the amount of combinations the script will terminate
}
else {
var rows = ss.getRange('AP1').getValue(); // Gets the amount of rows (+1 for some reason but this is balanced in the for loop) in the PPM sheet
var No_devices = ss.getRange('AS1').getValue(); // gets the total number of devices that require PPM this month
var biomedcombi = ss.getRange('AN'+(counter+3)).getValue() ; // Gets the unique combination for the biomed with PPM, to make sure he will not get multiple emails but just the one
var devicerows = ss.getRange('AS5:AS'+(No_devices+4)).getValues() //gets the row number for each device, this step decreases computatation time, instead of running through the whole portal, we only go through the required rows.
var content = ''; // start for the message content, will be added to in the for loop
var number = 0; // Number counter for the amount of machines a person has in PPM
for (var j=0;j<No_devices; j++) { // Loops through the entire sheet, to search for the unique value, however only checks earlier mentioned rows
if (ss.getRange('AM'+devicerows[j]).getValue() == biomedcombi) { // This is the search criterium for the PPM unique value mentioned above
if(k == ""){ var k=j}; // this variable is used only once to keep the other parts out of the script and to speed it up a little
var number=number+1; // Counts the amount of machines for PPM
var uniquenr = ss.getRange('B'+devicerows[j]).getValue(); // Get values needed for email and text message
var assetName = ss.getRange('C'+devicerows[j]).getValue(); // Repeat
var location = ss.getRange('D'+devicerows[j]).getValue();
var content = content + "<p>Unique number:<b> "+ uniquenr +"</b><br/>"+"Asset Name:<b> "+ assetName+"</b><br/>Location:<b> "+location+"</b></p>" // This stores a machine in the message content every time one has been found that meets the criterium
}
}
//these are moved out of the for loop as they are only required once
var emailAddress = ss.getRange('AB'+devicerows[k]).getValue();
var location = ss.getRange('D'+devicerows[k]).getValue();
var biomed = ss.getRange('A'+devicerows[k]).getValue();
var phoneengineer = ss.getRange('AQ'+devicerows[k]).getValue();
var emaildept = ss.getRange('AR'+devicerows[k]).getValue();
var subject = "Att. "+biomed+" FINAL NOTIFICATION for PPM for this month " ; // Set the subject for the email
var message = "<p>Dear " +biomed+",</p>"+ // Starting the message content which will include the previously gathered information
"<p>This is a final notification for your scheduled Planned Preventative Maintenance for this month. If you already performed this PPM please fill in the job card in the Biomed Portal. For this month "+number+" devices still require planned prevented maintenance. The machines which still require planned preventative maintenance are: </p>" + content +"<p>If one or more of these devices is not scheduled for PPM this month, please update the PPM schedule at the calibration computer at your earliest convenience.<p>Kind regards,<br/> MTRH Biomedical Engineering.</p>";
MailApp.sendEmail(emailAddress, subject, message,{ // send email --> sendEmail(recipient, subject, body, options)
name: 'Biomedical Engineering Department MTRH',
htmlBody: message,
cc: emaildept+","+"hodbiomed@mtrh.go.ke",
});
// TURN ON WHEN SMS SERVER IS WORKING!!!!
// With many thanks to Salim, this is a trial phone server we can use to send text messages to the responsible engineers. Should be replaced with the official one from the hospital.
// paybill number for africas talking: 525900 accountno: salim1990
// var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phoneengineer+"&message=Dear "+biomed+", this is a reminder that you have Planned Preventative Maintenance scheduled this month. You still have "+number+" machines that require PPM. In case you already performed the PPM, please fill in the job card. Please check your email or the calibration computer for more information. MTRH");
sheet.toast("email sent")
ss.getRange('AM1').setValue(counter+1); // Add 1 to the counter to make sure the next biomed will get an email on the next trigger/time this script runs.
}
}
// send a email the 27th of the month to all biomeds who have not yet performed their Planned Preventative Maintenance --> set counter on 1 again
function PPMmail_27thofMonth_lastTrigger() { // This function will run ~20 times in 20 different trigger, it took too long to combine all PPM messages in one go due to time limitations on google. Therefore we call on this script 20 times
var sheet = SpreadsheetApp.getActive(); // Select the right spreadsheet for this function, PPM.
var ss = sheet.getSheetByName("PPM schedule");
var combinations = ss.getRange('AO5').getValue(); // # of unique combinations PPM which have PPM upcomming month
var counter = ss.getRange('AM1').getValue(); // Rather than a for loop we have a counter on the sheet itself, works the same but due to time limitations we had to build a way around. Every time the script runs it will at 1 to the counter. Last one will reset the counter back to 1
if (counter>combinations){ // As soon as the counter is bigger than the amount of combinations the script will terminate
ss.getRange('AM1').setValue(1); // if counter>combination it means everyone got the message, then we can just set it back to 1 for next month.
}
else {
var rows = ss.getRange('AP1').getValue(); // Gets the amount of rows (+1 for some reason but this is balanced in the for loop) in the PPM sheet
var No_devices = ss.getRange('AS1').getValue(); // gets the total number of devices that require PPM this month
var biomedcombi = ss.getRange('AN'+(counter+3)).getValue() ; // Gets the unique combination for the biomed with PPM, to make sure he will not get multiple emails but just the one
var devicerows = ss.getRange('AS5:AS'+(No_devices+4)).getValues() //gets the row number for each device, this step decreases computatation time, instead of running through the whole portal, we only go through the required rows.
var k="";
var content = ''; // start for the message content, will be added to in the for loop
var number = 0; // Number counter for the amount of machines a person has in PPM
for (var j=0;j<No_devices; j++) { // Loops through the entire sheet, to search for the unique value, however only checks earlier mentioned rows
if (ss.getRange('AM'+devicerows[j]).getValue() == biomedcombi) { // This is the search criterium for the PPM unique value mentioned above
if(k == ""){ var k=j}; // this variable is used only once to keep the other parts out of the script
var number=number+1; // Counts the amount of machines for PPM
var uniquenr = ss.getRange('B'+devicerows[j]).getValue(); // Get values needed for email and text message
var assetName = ss.getRange('C'+devicerows[j]).getValue(); // Repeat
var location = ss.getRange('D'+devicerows[j]).getValue();
var content = content + "<p>Unique number:<b> "+ uniquenr +"</b><br/>"+"Asset Name:<b> "+ assetName+"</b><br/>Location:<b> "+location+"</b></p>" // This stores a machine in the message content every time one has been found that meets the criterium
}
}
//these are moved out of the for loop as they are only required once
var emailAddress = ss.getRange('AB'+devicerows[k]).getValue();
var biomed = ss.getRange('A'+devicerows[k]).getValue();
var phoneengineer = ss.getRange('AQ'+devicerows[k]).getValue();
var emaildept = ss.getRange('AR'+devicerows[k]).getValue();
var subject = "Att. "+biomed+" FINAL NOTIFICATION for PPM for this month " ; // Set the subject for the email
var message = "<p>Dear " +biomed+",</p>"+ // Starting the message content which will include the previously gathered information
"<p>This is a final notification for your scheduled Planned Preventative Maintenance for this month. If you already performed this PPM please fill in the job card in the Biomed Portal. For this month "+number+" devices still require planned prevented maintenance. The machines which still require planned preventative maintenance are: </p>" + content +"<p>If one or more of these devices is not scheduled for PPM this month, please update the PPM schedule at the calibration computer at your earliest convenience.<p>Kind regards,<br/> MTRH Biomedical Engineering.</p>";
MailApp.sendEmail(emailAddress, subject, message,{ // send email --> sendEmail(recipient, subject, body, options)
name: 'Biomedical Engineering Department MTRH',
htmlBody: message,
cc: emaildept+","+"hodbiomed@mtrh.go.ke",
});
// TURN ON WHEN SMS SERVER IS WORKING!!!!
// With many thanks to Salim, this is a trial phone server we can use to send text messages to the responsible engineers. Should be replaced with the official one from the hospital.
// paybill number for africas talking: 525900 accountno: salim1990
// var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phoneengineer+"&message=Dear "+biomed+", this is a reminder that you have Planned Preventative Maintenance scheduled this month. You still have "+number+" machines that require PPM. In case you already performed the PPM, please fill in the job card. Please check your email or the calibration computer for more information. MTRH");
ss.getRange('AM1').setValue(counter+1); // Add 1 to the counter to make sure the next biomed will get an email on the next trigger/time this script runs.
}
}
// send a email the 28th of the month to all biomeds who have not yet performed scheduled calibration
function Calmail_28ofMonth_firstTrigger() { // This function will run ~20 times in 20 different trigger, it took too long to combine all calibration messages in one go due to time limitations on google. Therefore we call on this script 20 times
var sheet = SpreadsheetApp.getActive(); // Select the right spreadsheet for this function, calibration.
var ss = sheet.getSheetByName("Calibration Shedule");
var combinations = ss.getRange('AM5').getValue(); // # of unique combinations for calibration which have not done their calibration yet
var counter = ss.getRange('AM1').getValue(); // Rather than a for loop we have a counter on the sheet itself, works the same but due to time limitations we had to build a way around. Every time the script runs it will at 1 to the counter. Last one will reset the counter back to 1
if (counter>combinations){ // As soon as the counter is bigger than the amount of combinations the script will terminate
}
else {
var rows = ss.getRange('AN1').getValue(); // Gets the amount of rows (+1 for some reason but this is balanced in the for loop) in the PPM sheet
var No_devices = ss.getRange('AQ1').getValue();
var biomedcombi = ss.getRange('AL'+(counter+3)).getValue() ; // Gets the unique combination for the biomed with PPM, to make sure he will not get multiple emails but just the one
var devicerows = ss.getRange('AQ5:AQ'+(No_devices+4)).getValues() //gets the row number for each device, this step decreases computatation time, instead of running through the whole portal, we only go through the required rows.
var k="";
var content = ''; // start for the message content, will be added to in the for loop
var number = 0; // Number counter for the amount of machines a person has in PPM
for (var j=0;j<No_devices; j++) { // Loops through the entire sheet, to search for the unique value, thus this big loop
if (ss.getRange('AK'+devicerows[j]).getValue() == biomedcombi) { // This is the search criterium for the calibration unique value mentioned above
if(k == ""){ var k=j};
var number=number+1; // Counts the amount of machines for calibration reminder
var uniquenr = ss.getRange('B'+devicerows[j]).getValue(); // Get values needed for email and text message
var assetName = ss.getRange('C'+devicerows[j]).getValue(); // Repeat
var location = ss.getRange('D'+devicerows[j]).getValue();
sheet.toast(uniquenr)
var content = content + "<p>Unique number:<b> "+ uniquenr +"</b><br/>"+"Asset Name:<b> "+ assetName+"</b><br/>Location:<b> "+location+"</b></p>" // This stores a machine in the message content every time one has been found that meets the criterium
}
}
var biomed = ss.getRange('A'+devicerows[k]).getValue();
var emailAddress = ss.getRange('AA'+devicerows[k]).getValue();
var phoneengineer = ss.getRange('AO'+devicerows[k]).getValue();
var emaildept = ss.getRange('AP'+devicerows[k]).getValue();
var subject = "Att. "+biomed+" FINAL NOTIFICATION for scheduled calibration for this month " ; // Set the subject for the email
var message = "<p>Dear " +biomed+",</p>"+ // Starting the message content which will include the previously gathered information
"<p>This is a final notification for your scheduled calibration for this month. If you already performed this calibration please fill in the job card in the Biomed Portal. For this month "+number+" devices still require their scheduled calibration. The machines which still require calibration: </p>" + content +"<p>If one or more of these devices is not scheduled for calibration this month, please update the calibration schedule at the calibration computer at your earliest convenience.<p>Kind regards,<br/> MTRH Biomedical Engineering.</p>";
MailApp.sendEmail(emailAddress, subject, message,{ // send email --> sendEmail(recipient, subject, body, options)
name: 'Biomedical Engineering Department MTRH',
htmlBody: message,
cc: emaildept+","+"hodbiomed@mtrh.go.ke",
});
// TURN ON WHEN SMS SERVER IS WORKING!!!!
// With many thanks to Salim, this is a trial phone server we can use to send text messages to the responsible engineers. Should be replaced with the official one from the hospital.
// paybill number for africas talking: 525900 accountno: salim1990
//var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phoneengineer+"&message=Dear "+biomed+", this is a reminder that you have calibration scheduled this month. You still have "+number+" machines that require calibration. In case you already performed the calibration, please fill in the job card. Please check your email or the calibration computer for more information. MTRH");
ss.getRange('AM1').setValue(counter+1); // Add 1 to the counter to make sure the next biomed will get an email on the next trigger/time this script runs.
}
}
// send a email the 28th of the month to all biomeds who have not yet performed scheduled calibration
function Calmail_28ofMonth_lastTrigger() { // This function will run 1 time and sets the counter back to 1 in the end.
var sheet = SpreadsheetApp.getActive(); // Select the right spreadsheet for this function, calibration.
var ss = sheet.getSheetByName("Calibration Shedule");
var combinations = ss.getRange('AM5').getValue(); // # of unique combinations for calibration which have not done their calibration yet
var counter = ss.getRange('AM1').getValue(); // Rather than a for loop we have a counter on the sheet itself, works the same but due to time limitations we had to build a way around. Every time the script runs it will at 1 to the counter. Last one will reset the counter back to 1
if (counter>combinations){ // As soon as the counter is bigger than the amount of combinations the script will terminate
ss.getRange('AM1').setValue(1); // if counter>combination it means everyone got the message, then we can just set it back to 1 for next month.
}
else {
var rows = ss.getRange('AN1').getValue(); // Gets the amount of rows (+1 for some reason but this is balanced in the for loop) in the PPM sheet
var No_devices = ss.getRange('AQ1').getValue();
var biomedcombi = ss.getRange('AL'+(counter+3)).getValue() ; // Gets the unique combination for the biomed with PPM, to make sure he will not get multiple emails but just the one
var devicerows = ss.getRange('AQ5:AQ'+(No_devices+4)).getValues() //gets the row number for each device, this step decreases computatation time, instead of running through the whole portal, we only go through the required rows.
var k="";
var content = ''; // start for the message content, will be added to in the for loop
var number = 0; // Number counter for the amount of machines a person has in PPM
for (var j=0;j<No_devices; j++) { // Loops through the entire sheet, to search for the unique value, thus this big loop
if (ss.getRange('AK'+devicerows[j]).getValue() == biomedcombi) { // This is the search criterium for the calibration unique value mentioned above
if(k == ""){ var k=j};
var number=number+1; // Counts the amount of machines for calibration reminder
var uniquenr = ss.getRange('B'+devicerows[j]).getValue(); // Get values needed for email and text message
var assetName = ss.getRange('C'+devicerows[j]).getValue(); // Repeat
var location = ss.getRange('D'+devicerows[j]).getValue();
sheet.toast(uniquenr)
var content = content + "<p>Unique number:<b> "+ uniquenr +"</b><br/>"+"Asset Name:<b> "+ assetName+"</b><br/>Location:<b> "+location+"</b></p>" // This stores a machine in the message content every time one has been found that meets the criterium
}
}
var biomed = ss.getRange('A'+devicerows[k]).getValue();
var emailAddress = ss.getRange('AA'+devicerows[k]).getValue();
var phoneengineer = ss.getRange('AO'+devicerows[k]).getValue();
var emaildept = ss.getRange('AP'+devicerows[k]).getValue();
var subject = "Att. "+biomed+" FINAL NOTIFICATION for scheduled calibration for this month " ; // Set the subject for the email
var message = "<p>Dear " +biomed+",</p>"+ // Starting the message content which will include the previously gathered information
"<p>This is a final notification for your scheduled calibration for this month. If you already performed this calibration please fill in the job card in the Biomed Portal. For this month "+number+" devices still require their scheduled calibration. The machines which still require calibration: </p>" + content +"<p>If one or more of these devices is not scheduled for calibration this month, please update the calibration schedule at the calibration computer at your earliest convenience.<p>Kind regards,<br/> MTRH Biomedical Engineering.</p>";
MailApp.sendEmail(emailAddress, subject, message,{ // send email --> sendEmail(recipient, subject, body, options)
name: 'Biomedical Engineering Department MTRH',
htmlBody: message,
cc: emaildept+","+"hodbiomed@mtrh.go.ke",
});
// TURN ON WHEN SMS SERVER IS WORKING!!!!
// With many thanks to Salim, this is a trial phone server we can use to send text messages to the responsible engineers. Should be replaced with the official one from the hospital.
// paybill number for africas talking: 525900 accountno: salim1990
//var response = UrlFetchApp.fetch("https://api.africastalking.com/restless/send?username=salim1990&Apikey=3596a39ba0ea70acf5cade0dbb1b676d2c5888a5ba00d6b0a0e05962dc4d415e&to="+phoneengineer+"&message=Dear "+biomed+", this is a reminder that you have calibration scheduled this month. You still have "+number+" machines that require calibration. In case you already performed the calibration, please fill in the job card. Please check your email or the calibration computer for more information. MTRH");
ss.getRange('AM1').setValue(counter+1); // Add 1 to the counter to make sure the next biomed will get an email on the next trigger/time this script runs.
}
}
function Weekly_update(){
var sheet = SpreadsheetApp.getActive();
var ss = sheet.getSheetByName("Portfolio");
var rows = ss.getRange('AI1').getValue();
var content = '';
var number = 0;
for (var j=4; j<rows; j++) {
if (ss.getRange('AD'+j).getValue() <= 7){
var number = number+1
var uniquenr = ss.getRange('B'+j).getValue(); // Get values needed for email and text message
var assetName = ss.getRange('C'+j).getValue(); // Repeat
var location = ss.getRange('D'+j).getValue();
var biomed = ss.getRange('A'+j).getValue();
var status = ss.getRange('E'+j).getValue();
var content = content + "<p>Unique number:<b> "+ uniquenr +"</b><br/>"+"Asset Name:<b> "+ assetName+"</b><br/>Location:<b> "+location+"</b><br/>"+"Status:<b> "+ status+"</b><br/>"+"Responsible engineer:<b> "+ biomed+"</b></p>" // This stores a machine in the message content every time one has been found that meets the criterium
}
}
var subject = "Weekly update Service Request " ; // Set the subject for the email
var message = "<p>Dear Jacob, <p> This week for "+number+" devices service was requisted: </p>" + content +"<p>Kind regards,<br/> MTRH Biomedical Engineering.</p>";
MailApp.sendEmail("hodbiomed@mtrh.go.ke", subject, message,{ // send email --> sendEmail(recipient, subject, body, options)
name: 'Biomedical Engineering Department MTRH',
htmlBody: message,
cc: "markchebutuk@mtrh.go.ke",
});
}
// functions added by Gertrand
// Modified from http://stackoverflow.com/a/22200230/1027723
function emailCharts(){
var ss=SpreadsheetApp.getActiveSpreadsheet(); //makes the document active in this function
var sheet = ss.getSheetByName("Statistics"); //Goes to the sheet "statistics
var monthrange = sheet.getRange(1,12); // specifies the range and value that is the current month, this variable is currently not used, but can be used in the future for an if statement to check if the month is already saved
var month = monthrange.getValue();
var Costs = sheet.getRange("K5:K6").getValues(); //copies the range of values of the costs of this month (perhaps add highest expense)
var PPMcompletion = sheet.getRange("AG13").getDisplayValue();
var CALcompletion = sheet.getRange("AG26").getDisplayValue();
Logger.log(Costs[1][0])
var emails = 'hodbiomed@mtrh.go.ke';
var emailSubject = 'Montly statistical update Biomed Portal';
var charts = sheet.getCharts();
if(charts.length==0){
MailApp.sendEmail({
to: emails,
subject: "ERROR:"+emailSubject,
htmlBody: "No charts in the spreadsheet"});
return;
}
var chartBlobs=new Array(charts.length);
var emailBody="<p>Dear Jacob, <p> This is an update containing monthly statistical information from the biomed portal. <p> <p> For this month PPM work has been completed for "+PPMcompletion+", Calibration has been completed for "+CALcompletion+ "<p> If PPM and Calibration is not yet completed, please ask the engineers to fill in their PPM and/or calibration job cards. <p> In this email you will find the the charts of the month " +month+ "<p> This month " + Costs[0][0] +"KSH was spend in the department with "+Costs[1][0] +" KSH as the highest expense. <p> for more info regarding costs or other statistics, please consult the statistics page in the biomed portal. <p>Kind regards,<br/> MTRH Biomedical Engineering.</p>";
var emailImages={};
for(var i=0;i<charts.length;i++){
var builder = charts[i].modify();
builder.setOption('vAxis.format', '#');
var newchart = builder.build();
chartBlobs[i]= newchart.getAs('image/png');
emailBody= emailBody + "<p align='center'><img src='cid:chart"+i+"'></p>";
emailImages["chart"+i]= chartBlobs[i];
}
MailApp.sendEmail({
to: emails,
subject: emailSubject,
htmlBody: emailBody,
inlineImages:emailImages});
}
function saveData() {
/*function to save data, right now this is manually. However it will be updated to perform monthly automatic savings of data.
possibly add the function to send a monthly update to Jacob and/or Mark. However so far this is not yet implemented
further implementations could include protection against double saving of data, in the current configuration, if februari is already copied it will copy it again.
this should not matter as the function is activated only once a month, but it would be a good idea.
*/
var ss=SpreadsheetApp.getActiveSpreadsheet(); //makes the document active in this function
var sheet = ss.getSheetByName("Statistics"); //Goes to the sheet "statistics
var monthrange = sheet.getRange(1,12); // specifies the range and value that is the current month, this variable is currently not used, but can be used in the future for an if statement to check if the month is already saved
var month = monthrange.getValue();
var work_type = sheet.getRange("D2:D6"); // copies the range of values of work type that have been done this month. e.g. (corrective, calibration, PPM, Project)
var priority_type = sheet.getRange("F2:F6"); //copies the range of values of the priority that that were done this month e.g. (urgent, normal, emergency, routine)
var Costs = sheet.getRange("K3:K4"); //copies the range of values of the costs of this month (perhaps add highest expense)
// Define the row and location that will hold all monthly values, r encopasses the range, and rRow is the row number (A=1, B=2 etc.) that corresponds to range r
var r=ss.getRange("'Statistics'!A47:A48");
var rRow=r.getRow();
// define a range and get the cell value at the month location of range r called cell. the variable cell will help make sure that the data is stored in a new row
// IMPORTANT: at this point the script will produce consecutive months after eachother, perhaps it should renew each year or find a new location after a year.
var cellrange = sheet.getRange(rRow, 1);
var cell = cellrange.getValue();
Logger.log(cell)
// now perform a while loop determine the correct location to copy the monthly data to.
while (cell!=="") { // this means: if cell is not empty, so the loop continues untill cell is empty
rRow += 1; // add 1 to check the next row in the next iteration
var cellrange = sheet.getRange(rRow,1); // redefine the values for the range cellrange and corresponding month value in cell.
var cell = cellrange.getValue();
Logger.log(rRow)
Logger.log(cell)
}
sheet.getRange("D2:D6").copyTo(sheet.getRange(rRow,1), SpreadsheetApp.CopyPasteType.PASTE_VALUES,true); // the values have to be transposed for google sheet to be able to make the required graphs, i think this is bullshit, but its the way it is
sheet.getRange("F2:F6").copyTo(sheet.getRange(rRow,7), SpreadsheetApp.CopyPasteType.PASTE_VALUES,true); // the values have to be transposed for google sheet to be able to make the required graphs, i think this is bullshit, but its the way it is
sheet.getRange("K2:K4").copyTo(sheet.getRange(rRow,13), SpreadsheetApp.CopyPasteType.PASTE_VALUES,true); // the values have to be transposed for google sheet to be able to make the required graphs, i think this is bullshit, but its the way it is
}
// function to download the files as XLS file in order to automatically print a calibration and/or PPM Sticker. Acitvation times are still uncertain, probably once a day and/or after filling in a job card.
function downloadXLSX() {
var ssID = SpreadsheetApp.getActive().getId();
var URL = 'https://docs.google.com/spreadsheets/d/'+ssID+'/export?format=xlsx';
var blob;
var response = "";
var token = ScriptApp.getOAuthToken();
response = UrlFetchApp.fetch(URL, {
headers: {
'Authorization': 'Bearer ' + token
}
});
var filename = 'PtouchXLS.xlsx';
blob = response.getBlob().setName('PtouchXLS.xlsx');// Convert the response to a blob
// Find if files of the same name exist in that folder
var existing = DriveApp.getFilesByName(filename);
// Does file exist?
if (existing.hasNext()) {
var file = existing.next();
// Make sure the file name is exactly the same
if (file.getName() === filename) {
Drive.Files.update({
title: file.getName(), mimeType: file.getMimeType()
}, file.getId(), blob);
Logger.log("Kaas is lekker")
}
}
else{
DriveApp.createFile(blob); // Create a file with the blob and place it to Drive's root
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment