Automation: Make an Agile mood board with daily emails + API to store and read responses into a private BubbleChart
What is the mood of your team?
Install NPM depdencencies
npm i -S nodemailer@6.9.10
npm i -S express@4.18.2
npm i -S google-spreadsheet&4.1.1
npm i -S google-auth-library@9.6.3
Open the file explorer into zenv folder to inspect credential files created
open .
Create an application password for your gmail account: https://support.google.com/accounts/answer/185833
Prepare your credentials file
{
"gmail_app_password": "MY GMAIL APP PASSWORD"
}
Save your gmail app password into credentials-mood-properties.json
file
_fs.writeFileSync(
__dirname + "credentials-mood-properties.json",
loadBlock('gmail-credentials')
);
print("DONE")
https://theoephraim.github.io/node-google-spreadsheet/#/guides/authentication?id=service-account Once your service account created, don't forget to copy file into zenv folder and share your Google spreadsheet with your service account email
Create a daily recurring task to send an email to each team member
const nodemailer = require('nodemailer');
const creds = require('./credentials-mood-properties.json');
const members = ["MEMBER1@gmail.com"]; // YOUR MEMBERS LIST
const transporter = nodemailer.createTransport({
service: 'gmail',
auth: {
user: 'SENDER@gmail.com', // REPLACE WITH YOUR SENDER EMAIL
pass: creds.gmail_app_password,
},
});
// REPLACE WIth YOUR JOB URL
const apiURL = "https://job.znote.io/posts/XXXXX/node-mood-api";
transporter.sendMail({
from: '"Anthony" <SENDER@gmail.com>', // sender address
to: members.join(","), // list of receivers (,)
subject: "Just to know how you feel today?", // Subject line
html: `Hi 👋, <br/> How are you today? Your feedback is important to improve project management!<br/><br/>
<a href="${apiURL}/mood?mood=1">Very good day ☀️</a><br/><br/>
<a href="${apiURL}/mood?mood=2">Almost good ⛅️</a><br/><br/>
<a href="${apiURL}/mood?mood=3">It's ok ☁️</a><br/><br/>
<a href="${apiURL}/mood?mood=4">I saw better 🌧️</a><br/><br/>
<a href="${apiURL}/mood?mood=5">Should be better ⛈️</a><br/><br/>
`, // html body
}).then(info => {
printJSON({info});
});
//exec: node
const {JWT} = require('google-auth-library');
const express = require('express');
const { GoogleSpreadsheet } = require('google-spreadsheet');
const creds = require('./google-spreadsheet-credentials.json');
const app = express()
const SCOPES = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive.file',
];
const jwt = new JWT({
email: creds.client_email,
key: creds.private_key,
scopes: SCOPES,
});
// Initialize the sheet - doc ID is the long id in the sheets URL
//const doc = new GoogleSpreadsheet(creds.spreadsheet_id, jwt);
// YOUR SPREADSHEET ID (visible in URL: https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0)
const doc = new GoogleSpreadsheet("SPREADSHEET_ID", jwt);
await doc.loadInfo();
const sheet = doc.sheetsByIndex[0];
app.get('/list', async (req, res) => {
const rows = await sheet.getRows();
return res.json(rows.map(r=> {
return {
date: r.get("date").split("T")[0],
mood: parseInt(r.get("mood"))
}
}));
})
app.get('/mood', async (request, response) => {
try {
const mood = request.query.mood;
await sheet.addRow({ date: new Date(), mood: mood });
print(`Reponse added ${mood}`)
return response.send("👍 Thank you");
} catch(err) {
return response.send("😓 error");
}
})
app.listen(4000)
const result = await fetch("https://job.znote.io/posts/YOUR_JOB_ID/node-mood-api/list");
const json = await result.json();
const xFormatter = (value) => {
if (value === 1) return "☀️";
if (value === 2) return "⛅️";
if (value === 3) return "☁️";
if (value === 4) return "🌧️";
if (value === 5) return "⛈️";
}
const serieFormatter = (value) => {
if (value === 1) return "Good";
if (value === 2) return "Almost Good";
if (value === 3) return "It's ok";
if (value === 4) return "I saw better";
if (value === 5) return "Should be better";
}
//printJSON(json)
bubbleChart({
options: {
chart: {
width: '800px',
},
yaxis: {
labels: {
formatter: function (value) {
return xFormatter(value);
}
},
},
},
series: toBubbleSeries({
data:json,
x:'date',
y:'mood',
serieFormatter:serieFormatter
})
});