Weekly Wins Automation

Setup Guide

This guide sets up an automated system for collecting your team's weekly wins and sending them back out as a recap. Once it's running, it works on its own every week.

What you'll need

Make a copy of each template before you start:

Step 1 - Set up the submission form

This is the form your team uses to submit their successes, challenges, and shoutouts.

Make a copy of the Weekly Wins Submission Form.
Click Responses at the top of the form.
Click Link to Sheets.
Rename the spreadsheet and click Create. You'll come back to this sheet later, so note where it lives.
Back in the form, click Publish, then Manage.
Under Responder view, click Restricted, select Anyone with the link, then click Done.
Click Publish.
Copy the responder link. You'll need it later.

Step 2 - Set up the archive document

This Google Doc stores every past submission so they're easy to review. You can also embed it on a website.

Make a copy of the Archive - Successes, Challenges and Shoutouts document.
Note the doc ID from the URL, which is the long string between /d/ and /edit. You'll need it later.

Step 3 - Set up the Friday reminder email

This is the automated reminder that goes to your team every Friday morning.

Go to scripts.google.com and confirm you're on your Berkeley account.
Click New Project and name it sendWeeklyEmail.
Delete anything in the editor.
Copy and paste the entire script below into the editor.
Replace everything marked in BOLD.
Click Save.
Click Triggers in the left menu, then Add Trigger.
Match the settings in the image below. You can set the send time to whatever you prefer.
Click Save.
Click Editor, then Run. Approve the one-time authorization popup when it appears.

That's it. The reminder now sends every Friday, automatically, from your email.

Weekly Email Reminder Script

// BEFORE RUNNING, REPLACE:
// 1. The form link (in the first href)
// 2. The archive doc link (in the second href)
// 3. Your name (in the signature)
// 4. The recipient and cc email addresses

function sendWeeklyEmail() {
const subject = "[REMINDER] Weekly Wins";
const htmlBody = `
<div style="font-family: Arial, sans-serif; font-size: 12px; color: #002676;">
Happy Friday Team!
Below is a link to our Weekly Wins Form. Please complete by the end of day to be included in the Weekly Update. Thank you for your ongoing contributions and have a great weekend.

<a href="https://bpm.berkeley.edu/%3Cstrong%3E%5BPASTE%20THE%20SUBMISSION%20FORMS%20RESPONDER%20LINK%20HERE.%20MAKE%20SURE%20THE%20LINK%20IS%20INSIDE%20THESE%20QUOTATION%20MARKS%5D%3C/strong%3E"
style="color: #0000EE; text-decoration: underline;">
Link to Weekly Wins - BPMO Form
</a>


If you'd like to reference our past Successes, Challenges and Shoutouts, you can view them
<a href="https://bpm.berkeley.edu/%3Cstrong%3E%5BPASTE%20THE%20ENTIRE%20URL%20FROM%20THE%20ARCHIVE%20GOOGLE%20DOC%20HERE.%20MAKE%20SURE%20THE%20LINK%20IS%20INSIDE%20THESE%20QUOTATION%20MARKS%5D%3C/strong%3E"
style="color: #0000EE; text-decoration: underline;">here</a>.

Best, [INSERT YOUR NAME HERE]
 
</div>
`;

const recipient = "[INSERT THE EMAIL YOU WANT TO SEND THIS TO]"; // or distribution list
GmailApp.sendEmail(recipient, subject, "Please view this email in HTML format.", {
htmlBody: htmlBody,
cc: "[INSERT THE EMAIL YOU WANT TO SEND A CC:]"
});
}

Step 4 - Set up the response spreadsheet script

This script pulls the week's responses, adds them to the archive doc, formats the recap, and emails it to your team Monday morning.

Open the spreadsheet you created to capture form responses.
Click Extensions, then Apps Script.
Name the script processWeeklyUpdate.
Delete anything in the editor, then paste the script below.
Replace everything marked in BOLD.
Click Save.
Click Triggers, then Add Trigger.
Match the settings in the screenshot below.
Click Editor, then Run. Approve the one-time authorization popup.

Weekly Update Script for Google Spreadsheet

// Weekly Update Script
function processWeeklyUpdate() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1"); //Ensure this is the name of the tab on the response sheet!
const data = sheet.getDataRange().getValues();
const docId = "[PASTE THE ARCHIVE GOOGLE DOC ID HERE. This is NOT the full URL. Open the doc, look at the URL, and copy only the long string between /d/ and /edit. Example URL: docs.google.com/document/d/1a2B3cD4eF5gH6iJ7kL8mN9oP/edit - you would paste only: 1a2B3cD4eF5gH6iJ7kL8mN9oP]"; // NOTE: Ensure what you paste is inside the quotation marks!
const doc = DocumentApp.openById(docId);
const body = doc.getBody();

const today = new Date();
const currentDay = today.getDay(); // Sunday = 0, Monday = 1, ..., Saturday = 6

const lastWeekMonday = new Date(today);
lastWeekMonday.setDate(today.getDate() - currentDay - 6);
lastWeekMonday.setHours(0, 0, 0, 0);

const lastWeekFriday = new Date(lastWeekMonday);
lastWeekFriday.setDate(lastWeekMonday.getDate() + 4);
lastWeekFriday.setHours(23, 59, 59, 999);

const formattedWeekStart = Utilities.formatDate(lastWeekMonday, Session.getScriptTimeZone(), "MMMM dd");
const formattedWeekEnd = Utilities.formatDate(lastWeekFriday, Session.getScriptTimeZone(), "MMMM dd, yyyy");
const weekHeader = `${formattedWeekStart} - ${formattedWeekEnd}`;

// Prevent duplicate header
if (body.getText().includes(weekHeader)) {
Logger.log("Header already exists");
return;
}

const newEntries = [];
const successes = [];
const challenges = [];
const shoutouts = [];

for (let i = 1; i < data.length; i++) {
const row = data[i];
const rowDate = new Date(row[2]);
const status = (row[12] || '').toString().trim();

Logger.log(`Row ${i + 1}: rowDate = ${rowDate}, lastWeekMonday = ${lastWeekMonday}, lastWeekFriday = ${lastWeekFriday}`);

if (status !== "DONE" && rowDate >= lastWeekMonday && rowDate <= lastWeekFriday) {
const s = [row[3], row[4], row[5]].filter(Boolean);
const c = [row[6], row[7], row[8]].filter(Boolean);
const sh = [row[9], row[10], row[11]].filter(Boolean);
const submitter = row[1];

if (s.length + c.length + sh.length > 0) {
newEntries.push({ successes: s, challenges: c, shoutouts: sh, submitter });
successes.push(...s);
challenges.push(...c);
shoutouts.push(...sh);
sheet.getRange(i + 1, 13).setValue("DONE");
}
}
}

if (newEntries.length === 0) {
Logger.log("No new entries to include.");
return;
}

// Append to Google Doc
let insertIndex = 0;

const header = body.insertParagraph(insertIndex++, weekHeader)
.setHeading(DocumentApp.ParagraphHeading.HEADING1)
.setFontSize(14)
.setBold(true);
body.insertParagraph(insertIndex++, "");

if (successes.length > 0) {
const title = body.insertParagraph(insertIndex++, "Successes");
title.setFontSize(11).setBold(true);
successes.forEach(s => {
const item = body.insertListItem(insertIndex++, s).setGlyphType(DocumentApp.GlyphType.BULLET);
item.setBold(false).setFontSize(11);
});
body.insertParagraph(insertIndex++, "");
}

if (challenges.length > 0) {
const title = body.insertParagraph(insertIndex++, "Challenges");
title.setFontSize(11).setBold(true);
challenges.forEach(c => {
const item = body.insertListItem(insertIndex++, c).setGlyphType(DocumentApp.GlyphType.BULLET);
item.setBold(false).setFontSize(11);
});
body.insertParagraph(insertIndex++, "");
}

if (shoutouts.length > 0) {
const title = body.insertParagraph(insertIndex++, "Shoutouts");
title.setFontSize(11).setBold(true);
shoutouts.forEach(sh => {
const item = body.insertListItem(insertIndex++, sh).setGlyphType(DocumentApp.GlyphType.BULLET);
item.setBold(false).setFontSize(11);
});
body.insertParagraph(insertIndex++, "");
}

doc.saveAndClose();

// Compose email
const subject = `[INFORM] Weekly Wins: ${weekHeader}`;
const htmlBody = `
<div style="font-family: Arial, sans-serif; font-size: 14px; color: #003262;">
Hello Team,
Thanks for everyone's contributions this week. Below are our Successes, Challenges and Shoutouts for the week.

${successes.length > 0 ? `
Successes
<ul style="margin-top: 0;">${successes.map(s => `<li>${s}</li>`).join('')}</ul>
` : ''}

${challenges.length > 0 ? `
Challenges
<ul style="margin-top: 0;">${challenges.map(c => `<li>${c}</li>`).join('')}</ul>
` : ''}

${shoutouts.length > 0 ? `
Shoutouts
<ul style="margin-top: 0;">${shoutouts.map(sh => `<li>${sh}</li>`).join('')}</ul>
` : ''}

Keep them coming, your updates matter!
You can view past Successes, Challenges and Shoutouts <a href="https://bpm.berkeley.edu/%3Cstrong%3E%5BPASTE%20THE%20FULL%20ARCHIVE%20GOOGLE%20DOC%20URL%20HERE%20-%20the%20ENTIRE%20link%2C%20not%20just%20the%20ID.%20This%20is%20different%20from%20the%20docId%20at%20the%20top%2C%20which%20needs%20only%20the%20ID.%20Keep%20this%20inside%20the%20quotation%20marks%21%5D%3C/strong%3E" style="color: #003262; text-decoration: underline;">here</a>.

Best, 
[INSERT YOUR NAME HERE]

</div>
`;

GmailApp.sendEmail("[THIS IS WHERE YOU INSERT THE EMAIL THAT YOU WANT TO SEND THIS TO. MAKE SURE IT'S PASTED INSIDE THESE QUOTATION MARKS!]", subject, "View the email in an HTML-compatible client.", {
htmlBody: htmlBody,
cc: "[YOU CAN ADD AN EMAIL TO CC: HERE. MAKE SURE IT'S PASTED INSIDE THESE QUOTATION MARKS!]"
});

Logger.log("Weekly update sent and document updated.");
}

You're done

Share the responder link with your team. They submit by the end of day Friday. The Monday script archives everything and sends the recap between 6:00 and 7:00 AM, which you can change in the trigger settings. Submissions technically only need to be in before the script runs, but end of day Friday keeps it simple so no one forgets.