英文:
Time Off Request and Setting date to mm/dd/yyyy format
问题
我想要运行的是已经填写的单元格。无论是在列F和K中填写全天的情况，还是在列I中填写迟到的情况。
另外，一个不错的附加功能是在发送电子邮件后将该行移动到“已批准”选项卡。
英文:
To start with I am a total novice at GAS. Im trying to a digital time-off request using google forms and google sheets that sends out an email when approved.
I have it working but I am running into an issue because I want to print the time in a format without the extra time at the end.
I was able to get it to work by adding .toLocaleDateString("en-US") to the variables but I realized it wont work if the the cell is blank.
I would like that to run on the cell that is filled. Either column f&k when a full day is taken or column I if late.
A nice extra would be after the email is sent the row is moved to the "approved" tab.
A copy of the google sheet is here: https://docs.google.com/spreadsheets/d/1HdFERMvFtgcmqoPOe4yvD7i-HyFgo132wCnTaY-IGwA/edit?usp=sharing
function sendEmailedit(e){
if (e.range.columnStart != 12 || e.value != "TRUE") return;
const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,11).getValues();
let y = rData\[0\]\[10\];
let late = rData\[0\]\[8\]
let beg = rData\[0\]\[5\];
let end = rData\[0\]\[6\];
let ESLP = "https://docs.google.c"
let Subject = "Staff absence/time off request for";
let cc = "pn, td, ma"
let msg = "Your staff absence/late request for" + " " + beg + "-" + end + " " + late + " " + "has been approved by ." + "<p>" +
"<b>\<u\>All Staff:</b>\</u\>" + "<br>" +
"Please add a banner reminder (all day) onto Google Calendar and include both es and es" + " <p>" +
"<b>\<u\>Teachers:</b>\</u\>" + "<br>" +
"Please email your lesson plans to ma and pn by 8:00am the day you will be out" + "<p>" +
"<b>\<u\>Sub plans must include:</b>\</u\>" + "<br>" +
"classroom procedures, clear directions for the scholars/ substitute, and an agenda with time stamps. You can use your own template or the" + " " + '\<a href="' + ESLP + '"\>ESAT Sub Lesson Plan</a>' + "<p>" +
"Thank you!";
Logger.log(msg);
GmailApp.sendEmail( y , "Staff absence/late request", msg,{
htmlBody: msg});
}
Thank you for any help!
I would like that to run on the cell that is filled. Either column f&k when a full day is taken or column I if late.
A nice extra would be after the email is sent the row is moved to the "approved" tab.
答案1
得分: 0
const rData = e.source.getActiveSheet().getRange(e.range.rowStart, 1, 1, 11).getDisplayValues();
Then you may also use this code to move that row to "Approved" sheet:
var sourceSheet = e.source.getActiveSheet();
var row = e.range.getRow();
var rowRange = sourceSheet.getRange(row, 1, 1, sourceSheet.getLastColumn());
var rowValues = rowRange.getDisplayValues()[0];
var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Approved");
targetSheet.appendRow(rowValues);
sourceSheet.deleteRow(row);
Final code:
function sendEmailedit(e) {
if (e.range.columnStart != 12 || e.value != "TRUE") return;
const rData = e.source.getActiveSheet().getRange(e.range.rowStart, 1, 1, 11).getDisplayValues();
let y = rData[0][10];
let late = rData[0][8];
let beg = rData[0][5];
let end = rData[0][6];
let ESLP = "https://docs.google.com/document/";
let Subject = "Staff absence/time off request for";
let cc = "test1@email.com, test2@email.com, test3@email.com";
let msg = "Your staff absence/late request for" + " " + beg + "-" + end + " " + late + " " + "has been approved by Ms." + "<p>" +
"<b><u>All Staff:</b></u>" + "<br>" +
"Please add a banner reminder (all day) onto Google Calendar and include both test4@email.com and test5@email.com" + "<p>" +
"<b><u>Teachers:</b></u>" + "<br>" +
"Please email your lesson plans to test@email.com and test2@email.com by 8:00am the day you will be out" + "<p>" +
"<b><u>Sub plans must include:</b></u>" + "<br>" +
"classroom procedures, clear directions for the scholars/ substitute, and an agenda with time stamps. You can use your own template or the" + " " + '<a href="' + ESLP + '">ESAT Sub Lesson Plan</a>' + "<p>" +
"Thank you!";
Logger.log(msg);
GmailApp.sendEmail(y, "Staff absence/late request", msg, {
htmlBody: msg
});
//Move this row
var sourceSheet = e.source.getActiveSheet();
var row = e.range.getRow();
var rowRange = sourceSheet.getRange(row, 1, 1, sourceSheet.getLastColumn());
var rowValues = rowRange.getDisplayValues()[0];
var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Approved");
targetSheet.appendRow(rowValues);
sourceSheet.deleteRow(row);
}
Reference:
<details>
<summary>英文:</summary>
**From:**
const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,11).getValues();
**To:**
const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,11).getDisplayValues();
Then you may also use this code to move that row to "Approved" sheet:
var sourceSheet = e.source.getActiveSheet();;
var row = e.range.getRow();
var rowRange = sourceSheet.getRange(row, 1, 1, sourceSheet.getLastColumn());
var rowValues = rowRange.getDisplayValues()[0];
var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Approved");
targetSheet.appendRow(rowValues);
sourceSheet.deleteRow(row);
Final code:
-----------
**Try:**
function sendEmailedit(e) {
if (e.range.columnStart != 12 || e.value != "TRUE") return;
const rData = e.source.getActiveSheet().getRange(e.range.rowStart, 1, 1, 11).getDisplayValues();
let y = rData[0][10];
let late = rData[0][8]
let beg = rData[0][5];
let end = rData[0][6];
let ESLP = "https://docs.google.com/document/"
let Subject = "Staff absence/time off request for";
let cc = "test1@email.com, test2@email.com, test3@email.com"
let msg = "Your staff absence/late request for" + " " + beg + "-" + end + " " + late + " " + "has been approved by Ms." + "<p>" +
"<b><u>All Staff:</b></u>" + "<br>" +
"Please add a banner reminder (all day) onto Google Calendar and include both test4@email.com and test5@email.com" + "<p>" +
"<b><u>Teachers:</b></u>" + "<br>" +
"Please email your lesson plans to test@email.com and test2@email.com by 8:00am the day you will be out" + "<p>" +
"<b><u>Sub plans must include:</b></u>" + "<br>" +
"classroom procedures, clear directions for the scholars/ substitute, and an agenda with time stamps. You can use your own template or the" + " " + '<a href=\"' + ESLP + '">ESAT Sub Lesson Plan</a>' + "<p>" +
"Thank you!";
Logger.log(msg);
GmailApp.sendEmail(y, "Staff absence/late request", msg, {
htmlBody: msg
});
//Move this row
var sourceSheet = e.source.getActiveSheet();;
var row = e.range.getRow();
var rowRange = sourceSheet.getRange(row, 1, 1, sourceSheet.getLastColumn());
var rowValues = rowRange.getDisplayValues()[0];
var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Approved");
targetSheet.appendRow(rowValues);
sourceSheet.deleteRow(row);
}
**Reference:**
- [Copy/Move row to another sheet][1]
[1]: https://sheetautomation.com/blog/copy-move-row/
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论