Copy button to copy the code snippet.// =====================
// RUN ON SHEET OPEN
//dataUpdateScript.gs
// =====================
// DATA UPDATE SCRIPT
// =====================
// === MAIN FUNCTION ===
function updateSelectedCells(dateValue) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var rangeList = sheet.getActiveRangeList();
if (!rangeList) {
return "⚠️ No cells selected!";
}
var formattedDate = new Date(dateValue);
var updatedCount = 0;
var ranges = rangeList.getRanges();
ranges.forEach(function(range) {
var values = range.getValues();
// === UPDATE CELLS ===
for (var i = 0; i < values.length; i++) {
for (var j = 0; j < values[i].length; j++) {
var cellValue = values[i][j];
if (cellValue === "" || cellValue instanceof Date) {
values[i][j] = formattedDate;
updatedCount++;
}
}
}
// === WRITE BACK UPDATED VALUES ===
range.setValues(values);
});
// === RETURN STATUS ===
if (updatedCount === 0) {
return "No date fields found in selected range!";
}
return "✅ Updated " + updatedCount + " date cell(s) successfully!";
}
// === customEmail.html ===function emailSidebarScript(data) {
try {
var subject = data.subject;
var message = data.message;
var attachments = data.attachments || [];
if (!subject || !message) {
throw new Error("Subject and message are required!");
}
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
// Detect "Email" and "Name" columns from header
var emailColIndex = headerRow.findIndex(h => h.toString().toLowerCase().includes("email")) + 1;
var nameColIndex = headerRow.findIndex(h => h.toString().toLowerCase().includes("name")) + 1;
if (emailColIndex === 0 || nameColIndex === 0) {
throw new Error("Could not find 'Email' or 'Name' column in header row!");
}
// Convert attachments to blobs and generate HTML list
var blobs = [];
var attachmentListHtml = "";
if (attachments.length > 0) {
attachmentListHtml = '<div class="attachments"><h3>Attachments</h3><ul>';
attachments.forEach(function(file) {
var blob = Utilities.newBlob(
Utilities.base64Decode(file.content),
file.type,
file.name
);
blobs.push(blob);
attachmentListHtml += "<li>" + file.name + "</li>";
});
attachmentListHtml += "</ul></div>";
}
// Load email template
var template = HtmlService.createTemplateFromFile("customEmail").getRawContent();
var rangeList = sheet.getActiveRangeList();
if (!rangeList) {
throw new Error("⚠️ No cells selected!");
}
var sentCount = 0;
var processedRows = new Set();
// Process each selected range
rangeList.getRanges().forEach(function(range) {
var rowStart = range.getRow();
var numRows = range.getNumRows();
var numCols = range.getNumColumns();
for (var i = 0; i < numRows; i++) {
for (var j = 0; j < numCols; j++) {
var rowNumber = rowStart + i;
if (!processedRows.has(rowNumber)) {
var email = sheet.getRange(rowNumber, emailColIndex).getValue();
var name = sheet.getRange(rowNumber, nameColIndex).getValue() || "there";
if (email && email.toString().includes("@")) {
var htmlMessage = template
.replace("{{NAME}}", name)
.replace("{{MESSAGE}}", message)
.replace("{{ATTACHMENTS_SECTION}}", attachmentListHtml || "");
GmailApp.sendEmail(email, subject, "", {
htmlBody: htmlMessage,
attachments: blobs
});
sentCount++;
}
processedRows.add(rowNumber);
}
}
}
});
if (sentCount === 0) {
return "⚠️ No valid emails found in selected rows!";
}
return "✅ Sent " + sentCount + " email(s) successfully!";
} catch (error) {
throw new Error("Failed to send: " + error.message);
}
}
// =====================
// Adds a custom menu to launch sidebars
// =====================
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu("✨ My Tools")
.addItem("Open Date Updater", "showDateSidebar")
.addItem("Send Email", "showEmailSidebar")
.addToUi();
}
// Show the "Update Last Contact" sidebar
function showDateSidebar() {
var html = HtmlService.createHtmlOutputFromFile("dateUpdateSidebar")
.setTitle("Update Last Contact");
SpreadsheetApp.getUi().showSidebar(html);
}
// Show the "Send Email" sidebar
function showEmailSidebar() {
var html = HtmlService.createHtmlOutputFromFile("emailSidebar")
.setTitle("Send Email");
SpreadsheetApp.getUi().showSidebar(html);
}
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<style>
body {
font-family: "Segoe UI", Roboto, Helvetica, Arial, sans-serif;
background: #f4f7fb;
margin: 0;
padding: 0;
color: #333;
}
.container {
max-width: 600px;
margin: 30px auto;
background: #ffffff;
border-radius: 16px;
box-shadow: 0 6px 18px rgba(0,0,0,0.1);
overflow: hidden;
animation: fadeInUp 0.6s ease;
}
.header {
background: linear-gradient(135deg, #6a11cb, #2575fc);
padding: 20px;
text-align: center;
color: white;
}
.header h1 {
margin: 0;
font-size: 22px;
letter-spacing: 1px;
}
.body {
padding: 30px;
line-height: 1.6;
font-size: 15px;
color: #444;
}
.body h2 {
font-size: 18px;
margin-bottom: 15px;
color: #6a11cb;
}
.attachments {
margin-top: 25px;
padding: 15px;
background: #f9f9ff;
border-left: 4px solid #6a11cb;
border-radius: 10px;
font-size: 14px;
color: #555;
}
.attachments h3 {
margin-top: 0;
margin-bottom: 10px;
font-size: 16px;
color: #2575fc;
}
.attachments ul {
padding-left: 20px;
margin: 0;
}
.attachments li {
margin-bottom: 6px;
}
.button {
display: inline-block;
background: linear-gradient(45deg, #6a11cb, #2575fc);
color: white !important;
padding: 12px 24px;
border-radius: 8px;
text-decoration: none;
font-size: 15px;
margin-top: 20px;
transition: transform 0.2s;
}
.button:hover {
transform: scale(1.05);
}
.footer {
background: #f4f7fb;
padding: 15px;
text-align: center;
font-size: 12px;
color: #777;
}
@keyframes fadeInUp {
from { transform: translateY(20px); opacity: 0; }
to { transform: translateY(0); opacity: 1; }
}
</style>
</head>
<body>
<div class="container">
<div class="header">
<h1>✨ Company Updates</h1>
</div>
<div class="body">
<h2>Hello {{NAME}},</h2>
<p>{{MESSAGE}}</p>
{{ATTACHMENTS_SECTION}}
</div>
<div class="footer">
© 2025 Your Company. All rights reserved.
</div>
</div>
</body>
</html> border: 2px solid #6a11cb;
border-radius: 8px;
outline: none;
}
.btn {
background: linear-gradient(45deg, #6a11cb, #2575fc);
color: white;
border: none;
padding: 12px 24px;
font-size: 16px;
border-radius: 8px;
cursor: pointer;
transition: transform 0.2s, box-shadow 0.2s;
margin-top: 20px;
}
.btn:hover {
transform: scale(1.1);
box-shadow: 0px 4px 12px rgba(0,0,0,0.3);
}
#message {
margin-top: 15px;
font-size: 14px;
font-weight: bold;
}
.success { color: green; }
.error { color: red; }
</style>
</head>
<body>
<div>
<h3>📅 Update Last Contact</h3>
<input type="date" id="selectedDate" />
<br>
<button class="btn" onclick="updateDate()">Update Date</button>
<div id="message"></div>
</div>
<script>
function updateDate() {
var dateValue = document.getElementById("selectedDate").value;
var msg = document.getElementById("message");
if (!dateValue) {
msg.innerHTML = "<span class='error'>⚠️ Please select a date first!</span>";
return;
}
msg.innerHTML = "⏳ Updating...";
google.script.run.withSuccessHandler((res) => {
msg.innerHTML = "<span class='success'>✅ " + res + "</span>";
}).updateSelectedCells(dateValue);
}
</script>
</body>
</html><!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
body {
font-family: Arial, sans-serif;
padding: 20px;
text-align: center;
}
h3 {
color: #333;
}
input[type="date"] {
padding: 10px;
font-size: 16px;
border: 2px solid #6a11cb;
border-radius: 8px;
outline: none;
}
.btn {
background: linear-gradient(45deg, #6a11cb, #2575fc);
color: white;
border: none;
padding: 12px 24px;
font-size: 16px;
border-radius: 8px;
cursor: pointer;
transition: transform 0.2s, box-shadow 0.2s;
margin-top: 20px;
}
.btn:hover {
transform: scale(1.1);
box-shadow: 0px 4px 12px rgba(0,0,0,0.3);
}
#message {
margin-top: 15px;
font-size: 14px;
font-weight: bold;
}
.success { color: green; }
.error { color: red; }
</style>
</head>
<body>
<div>
<h3>📅 Update Last Contact</h3>
<input type="date" id="selectedDate" />
<br>
<button class="btn" onclick="updateDate()">Update Date</button>
<div id="message"></div>
</div>
<script>
function updateDate() {
var dateValue = document.getElementById("selectedDate").value;
var msg = document.getElementById("message");
if (!dateValue) {
msg.innerHTML = "<span class='error'>⚠️ Please select a date first!</span>";
return;
}
msg.innerHTML = "⏳ Updating...";
google.script.run.withSuccessHandler((res) => {
msg.innerHTML = "<span class='success'>✅ " + res + "</span>";
}).updateSelectedCells(dateValue);
}
</script>
</body>
</html><!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
body {
font-family: "Segoe UI", Arial, sans-serif;
padding: 20px;
text-align: center;
background: #f9f9fb;
}
h3 {
color: #333;
margin-bottom: 15px;
}
input[type="date"] {
padding: 12px;
font-size: 16px;
border: 2px solid #6a11cb;
border-radius: 8px;
outline: none;
transition: border-color 0.2s, box-shadow 0.2s;
}
input[type="date"]:focus {
border-color: #2575fc;
box-shadow: 0px 0px 4px rgba(37,117,252,0.3);
}
.btn {
background: linear-gradient(45deg, #6a11cb, #2575fc);
color: white;
border: none;
padding: 12px 24px;
font-size: 16px;
border-radius: 8px;
cursor: pointer;
transition: transform 0.2s, box-shadow 0.2s;
margin-top: 20px;
}
.btn:hover {
transform: scale(1.05);
box-shadow: 0px 4px 12px rgba(0,0,0,0.3);
}
#message {
margin-top: 15px;
font-size: 14px;
font-weight: bold;
}
.success { color: green; }
.error { color: red; }
</style>
</head>
<body>
<div>
<h3>📅 Update Last Contact</h3>
<input type="date" id="selectedDate" />
<br>
<button class="btn" onclick="updateDate()">Update Date</button>
<div id="message"></div>
</div>
<script>
function updateDate() {
const dateValue = document.getElementById("selectedDate").value;
const msg = document.getElementById("message");
if (!dateValue) {
msg.innerHTML = "<span class='error'>⚠️ Please select a date first!</span>";
return;
}
msg.innerHTML = "⏳ Updating...";
google.script.run.withSuccessHandler(res => {
msg.innerHTML = "<span class='success'>✅ " + res + "</span>";
}).updateSelectedCells(dateValue);
}
</script>
</body>
</html>
<head>
<base target="_top">
<style>
body {
font-family: Arial, sans-serif;
padding: 20px;
text-align: center;
}
h3 { color: #333; }
input[type="date"] {
padding: 10px;
font-size: 16px;
border: 2px solid #6a11cb;
border-radius: 8px;
outline: none;
}
.btn {
background: linear-gradient(45deg, #6a11cb, #2575fc);
color: white;
border: none;
padding: 12px 24px;
font-size: 16px;
border-radius: 8px;
cursor: pointer;
transition: transform 0.2s, box-shadow 0.2s;
margin-top: 20px;
}
.btn:hover {
transform: scale(1.1);
box-shadow: 0px 4px 12px rgba(0,0,0,0.3);
}
#message {
margin-top: 15px;
font-size: 14px;
font-weight: bold;
}
.success { color: green; }
.error { color: red; }
</style>
</head>
<body>
<div>
<h3>📅 Update Last Contact</h3>
<input type="date" id="selectedDate" />
<br>
<button class="btn" onclick="updateDate()">Update Date</button>
<div id="message"></div>
</div>
<script>
function updateDate() {
var dateValue = document.getElementById("selectedDate").value;
var msg = document.getElementById("message");
if (!dateValue) {
msg.innerHTML = "<span class='error'>⚠️ Please select a date first!</span>";
return;
}
msg.innerHTML = "⏳ Updating...";
google.script.run.withSuccessHandler((res) => {
msg.innerHTML = "<span class='success'>✅ " + res + "</span>";
}).updateSelectedCells(dateValue);
}
</script>
</body>
</html>