Skip to main content
Member tips

Generate Google docs with Typeform data using Google Sheets & AppScript

Generate Google docs with Typeform data using Google Sheets & AppScript
Jsanchez204
Forum|alt.badge.img+1

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:

  1. 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();
    }
    
  2. 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.

    1. Docs template Unique ID:

    2. Folder Unique ID:

       

  3. Add all the unique ID and sheet name in our CreateNewGoogleDocs Function.

     

  4. 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.

    const placeholders = [
        '{{ProgramName}}', '{{ProgramD}}', '{{ProgramO}}', '{{ResourcesN}}'
      ];
    
    **Make sure your Placeholders match the ones in your google docs template. 
    sheets Columns Placeholder
    Program Name {{ProgramName}}
    Program Description {{ProgramD}}
    Program Outcomes {{ProgramO}}
    Resources Needed {{ResourcesN}}

     

  5. 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.

 

5 replies

James
Community Team
Forum|alt.badge.img+5
  • Community Team
  • 661 replies
  • August 28, 2023

Hey @Jsanchez204 - and welcome to the Typeform Community!! Thanks sooo much for sharing this solution in such a clear and easy-to-follow way. This seems like a very cool way of merging typeform data into Google Docs, I can think of loads of practical use cases beyond the ones you mentioned - great stuff!!

 

It’s actually the first time I’ve come across the idea of using App Script to send response data to Google Docs - I’m not much of a codemonkey but using your guide I was able to make it work!

In the past, I’ve used Google Document Studio for merging typeform data with Google Docs (and Slides) like this, though I feel App Script may enable even greater control. Our good friend @john.desborough is a huge fan of using Document Studio for auto-generating docs and reports from responses – in fact he’s  planning to run a live workshop on the topic here on the Community in September, so watch this space for details real soon (I can tag you when the registration is open if you’re interested in joining that @Jsanchez204 ).

 

@john.desborough would be very keen to hear thoughts on this solution (as and when you are back from your summer hols 😎)

 

Thanks again @Jsanchez204 - really appreciate you sharing, as will others, I’m sure! 


  • Navigating the Land
  • 2 replies
  • September 25, 2023

Hi @Jsanchez204 thank you for providing this code and explanation. I followed your advise and it worked really well. Some of my responses in google sheets are blank due to the way my typeform is set up. Is there a piece of code I could add which skips the Placeholder if the cell is blank? At the moment the cell is blank and leaving large spaces in my google doc

 

Thanks for your help :) 


Jsanchez204
Forum|alt.badge.img+1
  • Author
  • Sharing wisdom
  • 11 replies
  • September 25, 2023

Hey @kiyahwellness, do you think you can provide a screenshot of the Google Doc? And a row of the Google sheets. You can go ahead and blur any information I just need to see what I can do on my end :) 


  • Navigating the Land
  • 2 replies
  • September 25, 2023

Hi @Jsanchez204 That would be so helpful thank you. Please see screenshot below of the google doc template. The text in each placeholder is a paragraph of advise based on the users answer. It the user selects ‘never’ or ‘not often’ on the typeform no advise is shown. 

Please see the snippet from the google sheets below. You can see here that the second user needs advise in placeholder {{lh1}} and {{lh2}} but not in any other aspects of lifestyle habits. 

Currently what is happening in document studio is that if the cell is blank in google sheets the google doc is left with alot of blank space. I would like the code to skip any blank cells and move on to the next. 

e.g For the second user (from the google sheets above) they should see advice for LH1 and LH2 and then the document template moves on to the dietary advice section.

Please let me know if you need more information than this

Thank you!


Jsanchez204
Forum|alt.badge.img+1
  • Author
  • Sharing wisdom
  • 11 replies
  • September 26, 2023

HI @kiyahwellness sorry for the late response. I was caught up with work but I think I found a work around for this problem. We are going to need to add a table on our google docs template. 

Example:

 

We can go ahead and make the column lines white if we don’t want it to look like a table. It should look something like this.

 

Okay now we can paste this code on our appscript

 

Paste these const inside our createNewGoogleDocs function

const table = body.getTables()[0] ;
const numberRows = table.getNumRows();

Paste this code inside our createNewGoogleDocs function.

for (let x = numberRows - 1; x > 0 ; x--) {
     let row = table.getRow(x)
     let numberColumns = row.getNumCells() ;
     for (let y = 0 ; y < numberColumns ; y++) {
       if (row.getCell(y).getText() === "") {
         table.removeRow(x);
         break;
        }
      }
    }

This code will go through all the rows and delete the empty ones. Note that I added the heading to the first row in my template. This row will not be deleted.

 

Now when you click create new doc on google sheets it should populate your doc with your data and delete any blank rows.Hope this helps :) 

Note: p.s if you would like the rows to have less space you can edit the spacing in your google docs template.

 

Final Doc: