I would like to use a Tadabase app (MyApp) to automatically save emails and attachments, and links them to the extent possible to a client account. The incoming email would be sent to x@myapp.com in the Cc line or directly to x+clientlabel@myapp.com.
I know it is possible to achieve with a combination of Tadabase and Gmail (with rules and an App Script), but I don’t have enough skills to do this.
Ivan, this sounds very interesting. We’ll see if anyone else in the community has already done this. If not, if i have some time I’ll investigate a bit and report back.
In the email, what determines who the client is?
For example, if someone send it to “Moe+Tadabase@myapp.com”. Is the unique client name “Tadabase?”
Yes, you got it: the “Tadabase” here would be the explicit label in the email address that is used by a Gmail filter for assigning a label (“tadabase”). Then the idea is to match the email label with the unique client label in the database (in tadabase).
Here is a solution with a Google App Script that does this. It scans emails with a “NEW” label and changes the label to “SAVED” once saved in Tadabase.
If the email has attachments, these are saved as separate linked records in Tadabase. If the email includes in-line images, these are saved as well and the body of the email is modified to point to the saved file (typically with an URL like src=“https://8232-application-data-2273.s3.amazonaws.com/X9Qo01sAwY/1591223345-image.png”).
If the email has another label, this other label is used to create a connection with a “Company” (i.e. a client account).
The script (in particular globals.gs) needs to be adapted to the particular data model/use case and to use the proper API Keys. The script can be scheduled to run every hour for instance.
code.gs
function main() {
const emailManager = new EmailManager();
const tadabase = new TadabaseCaller(globals);
const {
newThreads,
getDetailsFromMessage,
getDetailsFromAttachment,
removeNewLabelFromAllThreads,
replaceInlineImageLinks,
savedLabel
} = emailManager;
newThreads.forEach((thread) => {
const otherLabels = thread
.getLabels()
.filter((label) => label.getName() !== "NEW")
.map((label) => label.getName());
const messages = thread
.getMessages();
const companyRecord = getCompanyRecord(otherLabels,tadabase.companyIds);
messages.forEach((message) => {
const messageDetails = getDetailsFromMessage(message);
messageDetails.clientId = companyRecord ? companyRecord.id : '';
const companyFieldName = globals.maps.company.companyName;
const companyName = companyRecord ? companyRecord.name : undefined;
const companyLabel = companyRecord ? companyRecord.label : undefined;
try {
const messageRecordResponse = tadabase.saveNewRecord({
table: "message",
record: messageDetails,
});
const messageRecordId = messageRecordResponse.recordId;
const attachments = message.getAttachments({
includeInlineImages: false,
});
const inlineImages = message.getAttachments({
includeInlineImages: true,
includeAttachments: false,
});
const attachmentRecordResponses = createRecordsForAttachments(
attachments,
messageRecordId,
getDetailsFromAttachment,
tadabase
);
const inlineImagesRecordResponses = createRecordsForAttachments(
inlineImages,
messageRecordId,
getDetailsFromAttachment,
tadabase
);
if (inlineImages.length) {
const imageRecords = inlineImagesRecordResponses.map(img => tadabase.getRecordById('attachment', img.recordId));
const messageRecord = tadabase.getRecordById('message', messageRecordId);
const updatedMessageRecord = replaceInlineImageLinks(
messageRecord,
imageRecords
);
const updateResponse = tadabase.updateRecord({
table: "message",
id: messageRecordId,
payload: JSON.stringify(updatedMessageRecord),
});
}
message.getThread().addLabel(savedLabel);
//send email confirmation;
try {
const senderName = messageDetails.from.replace(/<([\w\W]+?)>/,'');
const replyTo = messageDetails.from;
const attachmentCount = attachments.length + inlineImages.length;
const fromAddress = emailManager.generateFromAddress(message, companyLabel);
const subjectLine = emailManager.generateSubject(messageDetails.subject);
if (senderName) {
const emailBody = populateEmailText(senderName,attachmentCount,companyName);
GmailApp.sendEmail(replyTo, subjectLine,emailBody, {from:fromAddress});
}
} catch (err){
Logger.log('Sending confirmation email:',err);
}
} catch (err) {
Logger.log(err);
}
});
});
removeNewLabelFromAllThreads();
}
function createRecordsForAttachments(
attachments,
messageRecordId,
getDetailsFromAttachment,
tadabase
) {
const responses = [];
attachments.forEach((attachment) => {
const attachmentDetails = getDetailsFromAttachment(attachment);
attachmentDetails.messageId = messageRecordId;
const attachmentRecordResponse = tadabase.saveNewRecord({
table: "attachment",
record: attachmentDetails,
});
responses.push(attachmentRecordResponse);
});
return responses;
}
function getFromDatabaseById({ ids, tadabase, table }) {
const responses = ids.map((id) => {
const tadabaseResponse = tadabase.getRecordById(table, id);
return tadabaseResponse.error ? undefined : tadabaseResponse;
});
return responses;
}
function getCompanyRecord(labels, companyRecords){
for (let i = 0; i < labels.length; i++){
const labelArray = labels[i].split('/');
for (let j = 0; j < labelArray.length; j++){
const label = labelArray[j];
if (companyRecords[label]) return {
label:label,
id:companyRecords[label].id,
name:companyRecords[label].name,};
}
}
}
appsscript.json
{
"timeZone": "America/Los_Angeles",
"dependencies": {
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"executionApi": {
"access": "ANYONE"
},
"oauthScopes":[
"https://www.googleapis.com/auth/gmail.labels",
"https://www.googleapis.com/auth/gmail.readonly",
"https://www.googleapis.com/auth/gmail.modify",
"https://mail.google.com/",
"https://www.googleapis.com/auth/script.external_request",
"https://www.googleapis.com/auth/drive",
"https://www.googleapis.com/auth/drive.readonly"
]
}
globals.gs
var globals = {
API_KEY: "API_KEY_HERE",
API_SECRET: "API_SECRET_HERE",
APP_ID: 'YOU_APP_ID_HERE',
BASE_URL: "https://api.tadabase.io/api/v1/",
FROM_ALIAS:"x",
FROM_DOMAIN:"myapp.com",
table_ids:{
company:'ENTER_TABLE_ID_FOR_COMPANIES',
message:'ENTER_TABLE_ID_FOR_MESSAGES',
attachment:'ENTER_TABLE_ID_FOR_ATTACHMENTS',
},
maps:{
company:{
companyName: 'field_36',
companyLabel:'field_42',
companyId: 'id',
},
message: {
id: "id",
from:"field_37",
date:"field_46",
subject:"field_47",
body:"field_48",
snippet:"field_49",
clientId:"field_50",
},
attachment: {
fileName: "field_51",
file:"field_57",
fileType:"field_53",
messageId:"field_54",
}
}
}
classes/EmailManager.gs
class EmailManager {
constructor() {
this.newLabels = this.getAllNewLabels();
this.savedLabel = this.getLabel("SAVED");
this.newThreads = this.getAllNewThreads();
this.removeNewLabelFromAllThreads = this.removeNewLabelFromAllThreads.bind(
this
);
this.aliases = GmailApp.getAliases();
}
getAllNewLabels(){
const allLabels = GmailApp.getUserLabels();
const newLabels = allLabels.filter(label => label.getName().indexOf('NEW') > -1);
return newLabels;
}
getAllNewThreads(){
const labels = this.newLabels;
const newThreads = [];
for (let i = 0; i < labels.length; i++){
const label = labels[i];
newThreads.push(...label.getThreads());
}
return newThreads;
}
generateSubject(original) {
if (original.indexOf("Re:") > -1 || original.indexOf("Fwd:") > -1) return original;
return "Re: " + original;
}
generateFromAddress(message, label) {
const { FROM_ALIAS, FROM_DOMAIN } = globals;
const globalAlias = FROM_ALIAS + '@' + FROM_DOMAIN;
Logger.log("Working out from address for confirmation email...");
Logger.log("Your aliases are:", this.aliases);
Logger.log("Alias from globals file:", globalAlias);
if (this.aliases.indexOf(globalAlias) > -1 ){
Logger.log(globalAlias, "found in your aliases");
if(label){
return FROM_ALIAS + '+' + label + '@' + FROM_DOMAIN;
} else {
return globalAlias;
}
} else {
Logger.log(globalAlias, "not found in your aliases");
}
const ccAddresses = message.getCc().split(",") || [];
if (ccAddresses.length) {
Logger.log("CC addresses from email", ccAddresses);
const aliases = ccAddresses.filter((address) => {
if (this.aliases.indexOf(address) > -1){
Logger.log(address, "found in your aliases");
} else {
Logger.log(address, "not found in your aliases");
}
});
if (aliases.length){
const alias = aliases[0];
const splitAlias = alias.split('@');
return `${splitAlias[0]}+${label}@${splitAlias[1]}`;
}
return;
}
}
getDetailsFromAttachment(attachment) {
return {
file: attachment.copyBlob(),
fileType: attachment.getContentType(),
fileName: attachment.getName(),
};
}
getDetailsFromMessage(message) {
const plainBody = message.getPlainBody();
const snippet = plainBody ? plainBody.slice(0,100) : "";
const attachments = message.getAttachments();
return {
from: message.getFrom(),
subject: message.getSubject(),
body: message.getBody(),
snippet: snippet,
date: message.getDate(),
attachmentCount: attachments ? attachments.length : 0,
};
}
getLabel(name){
try {
const label = GmailApp.getUserLabelByName(name) || GmailApp.createLabel("SAVED");
return label
} catch(err) {
Logger.log("Couldn't access gmail app", err);
return 'error';
}
}
removeNewLabelFromAllThreads() {
this.newLabels.forEach(label => label.removeFromThreads(this.newThreads));
}
replaceInlineImageLinks(messageRecord, imageRecords) {
// Dynamically setting the field names to keep all variables in globals
const findFileField = (imgRecord) => {
for (let key in imgRecord) {
if (typeof imgRecord[key] === "object" && imgRecord[key].url) return key;
}
};
const getImageTags = (htmlBody) => {
const result = {};
const htmlImgTagStrings = htmlBody.match(/<img([\w\W]+?)>/g);
if (htmlImgTagStrings){
htmlImgTagStrings.forEach((tag) => {
result[tag] = {};
const attrStrings = tag.match(/(\S+)=[\'"]?((?:(?!\/>|>|"|\'|\s).)+)/g);
if (attrStrings) {
attrStrings.forEach((attr) => {
const splitAttr = attr.split("=");
if (splitAttr.length) {
const attrObj = { fullString: attr, value: splitAttr[1] };
result[tag][splitAttr[0]] = attrObj;
}
});
} else {
Logger.log('No attrStrings in', tag);
}
});
} else {
Logger.log('No img tags in', htmlBody);
}
return result;
};
const bodyField = globals.maps.message.body;
const filenameField = globals.maps.attachment.fileName;
const fileField = findFileField(imageRecords[0]);
let body = messageRecord[bodyField];
body = body.replace(/<br>/g, "<br/>");
const imgTags = getImageTags(body);
/* There should always be a file field for these image records but if not,
we can't replace the links anyway */
if (!fileField) return;
imageRecords.forEach((image) => {
const fileDetails = image[fileField];
const originalFileName = image[filenameField];
for (let str in imgTags) {
if (str.indexOf(originalFileName) > -1) {
try {
const tagObj = imgTags[str];
const oldSrcAttr = tagObj.src.fullString;
const urlToReplace = tagObj.src.value;
const newSrcAttr = oldSrcAttr.replace(
urlToReplace,
'"' + fileDetails.url
);
body = body.replace(oldSrcAttr, newSrcAttr);
Logger.log(body);
} catch (err) {
Logger.log(err);
}
}
}
});
messageRecord[bodyField] = body;
return messageRecord;
}
}
** classes/TadabaseCaller.gs **
class TadabaseCaller {
constructor(globals){
this.headers = this.createHeaders(globals);
this.baseUrl = globals.BASE_URL;
this.tableIds = globals.table_ids;
this.companyIds = this.getCompanyIds();
this.createUrl = this.createUrl.bind(this);
this.getRecordById = this.getRecordById.bind(this);
}
createHeaders(globals) {
return {
'X-Tadabase-App-id':globals.APP_ID,
'X-Tadabase-App-Key':globals.API_KEY,
'X-Tadabase-App-Secret':globals.API_SECRET,
}
}
createUrl(suffix,id){
if (id && suffix.indexOf('#ID#') > -1) suffix = suffix.replace('#ID#',id);
return this.baseUrl + suffix;
}
getCompanyIds(){
const json = this.getRecordById('company');
const obj = {};
if (json && json.items){
json.items.forEach(item => {
const labelField = globals.maps.company.companyLabel;
const nameField = globals.maps.company.companyName;
const idField = globals.maps.company.companyId;
const name = item[nameField];
const id = item[idField];
const label = item[labelField];
obj[label] = {
id:id,
name:name
};
});
}
return obj;
}
getRecordById(tableName,recordId){
const {
baseUrl,
headers,
tableIds,
} = this;
const tableId = tableIds[tableName];
const suffix = `data-tables/${tableId}/records/${recordId || ''}`;
const url = this.createUrl(suffix);
const options = {
headers:headers
};
const response = UrlFetchApp.fetch(url, options);
Logger.log(response);
try {
const json = JSON.parse(response);
if (json.type === "success"){
const value = json.item || json;
return value;
} else {
return {error:json};
}
} catch (err) {
return {error:err, json:json};
}
}
saveNewRecord({table,record}){
const {
baseUrl,
createUrl,
headers,
tableIds,
} = this;
const tableId = tableIds[table];
const suffix = `data-tables/${tableId}/records`;
const url = createUrl(suffix,undefined,baseUrl);
let jsonPayload = formatData(record,table);
let payload;
if (table !== 'attachment') {
headers['Content-Type'] = 'application/json';
payload = JSON.stringify(jsonPayload);
} else {
payload = jsonPayload;
if (headers.hasOwnProperty('Content-Type')) delete headers['Content-Type'];
}
const options = {
method: 'POST',
headers:headers,
payload:payload,
muteHttpExceptions:true,
};
const response = UrlFetchApp.fetch(url, options);
Logger.log(response);
try {
const json = JSON.parse(response);
return json;
} catch (e) {
return {
error: e,
response: response,
}
}
}
updateRecord({table,id,payload}){
const {
headers,
tableIds,
} = this;
const tableId = tableIds[table];
const suffix = `data-tables/${tableId}/records/#ID#`;
const url = this.createUrl(suffix, id);
headers['Content-Type'] = 'application/json';
const options = {
method: 'POST',
headers:headers,
payload:payload,
muteHttpExceptions:true,
};
const response = UrlFetchApp.fetch(url, options);
Logger.log(response);
try {
const json = JSON.parse(response);
return json;
} catch (e) {
return {
error: e,
response: response,
}
}
}
}
function formatData(record, type) {
const { maps } = globals;
if (!maps.hasOwnProperty(type)) {
Logger.log(type, 'not in maps', record);
return;
}
const map = maps[type];
const payload = {};
for (let key in record) {
const value = record[key];
const newKey = map[key];
if (newKey) {
payload[newKey] = value;
} else {
Logger.log(`${key} not in map for ${type}`);
}
}
return payload;
}
fileManager.gs
function writeJsonToFile(folderName, fileName, j) {
const text = typeof j === 'string' ? j : JSON.stringify(j);
const folder = DriveApp.getFoldersByName(folderName).next();
const file = folder.createFile(fileName + ".json", text);
return file.getId();
}
function createFolderIfNotFound(folderName) {
if (!DriveApp.getFoldersByName(folderName).hasNext()){
DriveApp.createFolder(folderName);
}
}
emailText.gs
function populateEmailText(sender, attachmentCount, clientName) {
return `
Dear ${sender.trim()},
We are acknowledging receipt of your email with thanks. ${
attachmentCount
? `The ${attachmentCount} attachments from your email were saved.`
: ""
} ${clientName ? `${clientName.trim()} will be notified.` : ""}
Thank you for your patience.
Sincerely,
MyApp Team
`;
}