Skip to content

Instantly share code, notes, and snippets.

@dfop02
Created July 12, 2023 13:29
Show Gist options
  • Save dfop02/aee6e1f811d817982083cb4aa6f3c974 to your computer and use it in GitHub Desktop.
Save dfop02/aee6e1f811d817982083cb4aa6f3c974 to your computer and use it in GitHub Desktop.
Automatically create a google docs based on template when google sheets new lines
// ENV START
const docTemplateId = '';
const docFolderId = '';
const sheetName = '';
// ENV END
function onOpen(){
initializeMenu();
}
function initializeMenu(){
SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
.addItem('👉 Generate Document For All Without Doc Link', 'autoFillAllDocs')
.addToUi();
}
function autoFillAllDocs(){
const googleDocTemplate = DriveApp.getFileById(docTemplateId);
const destinationFolder = DriveApp.getFolderById(docFolderId);
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
const rows = sheet.getDataRange().getDisplayValues();
let new_docs_count = 0;
// ALL ROWS
rows.forEach(function(row, index){
let linkCol = row.length - 1;
if (index == 0) return;
if (row[linkCol] != '') return;
const copy = googleDocTemplate.makeCopy(`${row[0]} Doc`, destinationFolder);
const doc = DocumentApp.openById(copy.getId());
let body = doc.getBody();
body = replaceBody(body, row);
doc.saveAndClose();
const url = doc.getUrl();
sheet.getRange(index + 1, row.length).setValue(url);
new_docs_count += 1
})
if (new_docs_count == 0) {
Browser.msgBox("There isn't any new documents to be created.");
} else {
Browser.msgBox(`${new_docs_count} new documents created successfully!`);
}
}
function initializeTrigger(){ // run this only once to create a trigger
var sheet = SpreadsheetApp.getActive();
ScriptApp.newTrigger('autoFillDocs')
.forSpreadsheet(sheet)
.onChange()
.create();
}
function autoFillDocs(e){
const sheet = e.source.getActiveSheet();
if (sheet.getName() != sheetName) return;
if (e.changeType == 'INSERT_ROW'){
const googleDocTemplate = DriveApp.getFileById(docTemplateId);
const destinationFolder = DriveApp.getFolderById(docFolderId);
const lastRow = sheet.getLastRow();
const lastCol = sheet.getLastColumn();
const lastCell = sheet.getRange(lastRow, lastColumn).getValue();
const linkCol = lastCell.length - 1;
// Only last row
if (lastCell[linkCol] != '') return;
const copy = googleDocTemplate.makeCopy(`${lastCell[0]} Doc`, destinationFolder);
const doc = DocumentApp.openById(copy.getId());
let body = doc.getBody();
body = replaceBody(body, lastCell);
doc.saveAndClose();
const url = doc.getUrl();
sheet.getRange(lastRow + 1, lastCell.length).setValue(url);
} else if (e.changeType == 'EDIT') {
const activeRange = sheet.getActiveRange();
const range = sheet.getRange(activeRange.getRow(), 1, 1, sheet.getLastColumn())
const editRow = range.getValues().flat();
let title, msg = ''
if (editRow[editRow.length - 1] != '') {
title = 'Update Link';
msg = "You edit a line that already has a generated document, should I generate again? (I won't delete current)";
} else {
title = 'Generate Link';
msg = "You edit a line that doesn't have a generated doc, should I generate?";
}
response = Browser.msgBox(title, msg, Browser.Buttons.YES_NO)
if (response == 'yes') {
const googleDocTemplate = DriveApp.getFileById(docTemplateId);
const destinationFolder = DriveApp.getFolderById(docFolderId);
const copy = googleDocTemplate.makeCopy(`${editRow[0]} Doc`, destinationFolder);
const doc = DocumentApp.openById(copy.getId());
let body = doc.getBody();
body = replaceBody(body, editRow);
doc.saveAndClose();
const url = doc.getUrl();
sheet.getRange(activeRange.getRow(), editRow.length).setValue(url);
e.source.toast('Document created successfully!');
}
}
}
function replaceBody(body, row){
// Replace with your sheets columns here
// On Doc Template you must use same syntax to refer the info like -> Name: {{Name}}
body.replaceText('{{Name}}', row[0])
body.replaceText('{{Last_Name}}', row[1])
body.replaceText('{{Social_ID}}', row[2])
body.replaceText('{{Date_Time}}', row[3])
body.replaceText('{{Company}}', row[4])
body.replaceText('{{Email}}', row[5])
return body
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment