Use cases:
- Generate customized contracts for clients automatically from Typeform data
- Create invoices directly from Typeform responses
- Generate personalized reports for each survey respondent
- Automatically create project proposals based on client needs gathered through Typeform surveys.
Connecting our Typeform to google sheets:
To connect our Typeform to google sheets click on the connect and select google sheets.
you will then be prompted to give Typeform permission to access your account. Click Accept.
Once you finish creating or setting up your Typeform with a new or an existing sheets. you can click on “View your spreadsheet’ to be taken there.
Now we can get started working on the App Script. On the google sheets menu bar click on the “Extensions” dropdown and then click “App Script.”
Paste the following code in the terminal: This Google Apps Script code is meant to automate the process of creating Google Docs based on data from a Google Sheets spreadsheet. Here's a breakdown of what the code does:
function onOpen() {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu('AutoFill Docs');
menu.addItem('Create New Docs', 'createNewGoogleDocs')
menu.addToUi();
}
function createNewGoogleDocs() {
const googleDocTemplate = DriveApp.getFileById('1dF0PrC7M1GhpjVfGACwARqYEQNeZTjzWXVx_mxgrcbk');
const destinationFolder = DriveApp.getFolderById('1Zc5d27QkS4jdGowUj45vStdV09xjG0X3');
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Lesson Plan Form');
const rows = sheet.getDataRange().getValues();
const placeholders = [
'{{ProgramName}}', '{{ProgramD}}', '{{ProgramO}}', '{{ResourcesN}}'
];
rows.forEach(function(row, index){
if (index === 0 || row[8]) return; // Skip headers and already generated docs. will place a link on the 8th column.
const copy = googleDocTemplate.makeCopy(`${row[0]} ,Lesson Plan`, destinationFolder);
const doc = DocumentApp.openById(copy.getId());
const body = doc.getBody();
placeholders.forEach(function(placeholder, columnIndex){
body.replaceText(placeholder, row[columnIndex]);
});
doc.saveAndClose();
const url = doc.getUrl();
sheet.getRange(index + 1, 8).setValue(url); // replace the number 8 with the column you want the linked doc to be. for example, if I want my link to be added to the 7th column, then I would replace the 8 with 7.
});
}
Breakdown:
This Google Apps Script code is meant to automate the process of creating Google Docs based on data from a Google Sheets spreadsheet. Here's a breakdown of what the code does:
-
Setting Up the Menu on google sheets: When you open the Google Sheets document, a menu called "AutoFill Docs" appears at the top. This menu has an option called "Create New Docs." When you choose this option, it triggers a process to make new Google Docs.
function onOpen() { const ui = SpreadsheetApp.getUi(); const menu = ui.createMenu('AutoFill Docs'); menu.addItem('Create New Docs', 'createNewGoogleDocs') menu.addToUi(); }
-
Linking Docs from Template and Folder: The process starts by picking a template Google Doc using its unique ID. Then, it selects a folder where all the finished documents will be stored. To do this we first need to get our Unique IDs for our folder and google docs template. The url link will have our unique IDs. copy this and add you your code.
-
Docs template Unique ID:
-
Folder Unique ID:
-
-
Add all the unique ID and sheet name in our CreateNewGoogleDocs Function.
-
Getting Data from Spreadsheet: The script looks at the active sheet, which has information about a program's participants. It gathers all the information from the sheet in a structured way.
**Make sure your Placeholders match the ones in your google docs template.const placeholders = [ '{{ProgramName}}', '{{ProgramD}}', '{{ProgramO}}', '{{ResourcesN}}' ];
sheets Columns Placeholder Program Name {{ProgramName}} Program Description {{ProgramD}} Program Outcomes {{ProgramO}} Resources Needed {{ResourcesN}} -
Processing Each Row: For every row in the list (except the header), the script does the following steps:
a. If a document hasn't been made for them yet (based on the 'Document Link' column), it continues. Otherwise, it moves to the next row.
b. A new Google Doc is made by copying the template and giving it a new name based on the rows name and other details. This new document is placed in the destination folder.
c. The new document is opened, and its content (body) is prepared for editing.
d. Placeholder text like '{{ProgramName}}' in the document is replaced with the actual data from the Program name row in the sheet.
e. Once all replacements are done, the document is saved and closed.
f. The URL of the newly made document is saved in the 'Document Link' column next to the person's details in the sheet.
That's it! In summary, this script automates the process of generating customized Google Docs from a template based on data in a Google Sheets spreadsheet. It reads values from the spreadsheet, replaces placeholders in the template with these values, and saves the generated documents in a specified folder while updating the spreadsheet with the URLs of the generated documents. This could be useful for tasks such as generating registration forms, certificates, or other types of documents based on data in a spreadsheet.
Generate a docs:
to generate a doc after receiving a submission just click on the Autofill Docs.