Created
July 12, 2023 13:29
-
-
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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