Check out our complete how-to-guides, documentation, and resources below

Empowering developers to build document workflows in minutes.

Create Document from a Google Sheet

Example of how Google Sheets can be used to create a PandaDoc document.

Google Script applications can be a great way to quickly get a project off the ground. This Example shows how a short script app can be used to create a PandaDoc from within a Google Sheet.

This is perfect for workflows with complex pricing that requires custom logic to handle pricing and product selection. Token, Recipient, and Product data is merged into the PandaDoc document in this example.

📘

Requires PandaDoc API

PandaDoc's API is an Enterprise feature and creating documents via the API is subject to a per-document charge.

Copy Google Sheet

Make a copy of the Google Sheet by clicking this link below.

Copy Google Sheet

Authorize Script

After the sheet loads, a PandaDoc dropdown should be available. This sometimes takes 30 seconds to show up. If it does not refresh the webpage. Click the drop-down and click create the document

A popup will appear. Click continue and follow the prompts.

The app will be new to your account. You will need to click Advanced and scroll to the bottom.

Click the "Go to create Doc from Google Sheet Example(unsafe). This will approve the script app to run. The script can be reviewed by clicking Tools->Script editor from the main menu.

Generate Access Token

A PandaDoc Acess Token is required. To generate an access token you will need to follow our Authentication Process.

Configure Google Sheet

  • Add Access token to sheet in cell B2
  • Add template ID to cell B3
  • Add desired document name to cell B5
  • Add Tokens to the Key: value pairs in Columns F&G
  • Add recipient information in column E
  • Add products to the Products sheet

👍

Template ID

The template ID can be found by opening a template in PandaDoc and looking at the URL.

It should be in the format of https://app.pandadoc.com/a/#/template/v1/editor/XhBBumC52gCjsGVXAQKGYW/content.

In this case, the Id is: XhBBumC52gCjsGVXAQKGYW

Create a document

Once the template ID and Access token are added you can try to create a document. This is done by Clicking Create the "Create Document" button in the PandaDoc menu or by calling the createSampleDocuments() function through a trigger.

Upon a successful document creation the URL, doc_id, Document name, and created date will be filled in Column B8-11.

Sample Code

If you have an existing Google sheet your working with the code snippet below can be modified for your sheet.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('PandaDoc')
  .addItem('Create Document', 'createSampleDocuments')
  .addToUi();  
}

function createSampleDocuments() {
  
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Setup");
  var data = sheet.getDataRange().getValues();
  
  var payload = {};
  payload.name = sheet.getRange('B5').getValue();
  payload.template_uuid = sheet.getRange('B3').getValue();
  
  
  //we are only collecting one recipient on the sheet but it can easily be extended
  //by adding another recipient object into the array
  var recipient = {};
  recipient.email = sheet.getRange('E2').getValue();
  recipient.first_name = sheet.getRange('E3').getValue();
  recipient.last_name = sheet.getRange('E4').getValue();
  recipient.role = sheet.getRange('E5').getValue();
  payload.recipients = [];
  
  if ( recipient.email != "" ) 
  {
    payload.recipients.push(recipient);
  }
  
  
  //build tokens array from provided fields 
  payload.tokens = [];
  
  for(var n=1;n < data.length;++n)
  {
    var token = {};
    if(data[n][5])
    {
      token.name = data[n][5]; 
      token.value = data[n][6];
      payload.tokens.push(token);
    }
  }
  
  //build pricing table array from products list
  payload.pricing_tables = [];
 
  var pt = {};
  pt.name = "PricingTable1"; //This is default name for first pricing table. If you want to save products to a different table you will have to change this!
  pt.options = {currency:"USD"};
  pt.sections = []; // you can have mutliple sections. We are just using one.
  
  var section = {};
  section.title = "Sample Section";
  section["default"] = true; //first section in table Title is not displayed
  section.rows = [];

  
  // Need to get row data from sheet data starts at row 15 COl a-e
  var productSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Products");
  var products = productSheet.getRange(1, 1, productSheet.getLastRow(),4).getValues();
  
  for(var n=1;n < products.length;++n)
  {
    var row = {};
    row.data ={};
    if(products[n][0])
    {
      row.data.name = products[n][0]; 
      row.data.descripiton = products[n][1];
      row.data.price = products[n][2];
      row.data.qty = products[n][3];
      row.options = {}; // Options object is required
      section.rows.push(row);
    }
  }
  
  // Add section to sections array
  pt.sections.push(section);
  
  //add pricing table objec to pricing table array
  payload.pricing_tables.push(pt);
  
  var access_token = sheet.getRange('B2').getValue();
  
  var createOptions = {
    'method': 'post',
    'headers': {
      'Authorization': 'Bearer ' + access_token,
      'Content-Type': 'application/json;charset=UTF-8'
    },
    'payload': JSON.stringify(payload)
  };
  
  var response = UrlFetchApp.fetch( "https://api.pandadoc.com/public/v1/documents", createOptions);
  var responseJson = JSON.parse(response);
  
  sheet.getRange('B8').setValue("https://app.pandadoc.com/a/#/document/v1/editor/"+ responseJson.id);
  sheet.getRange('B9').setValue(responseJson.id);
  sheet.getRange('B10').setValue(responseJson.name);
  sheet.getRange('B11').setValue(responseJson.date_created);
  
}

Errors

Errors will be displayed via a red bar at the top of your sheet. Clicking the "Details" button will provide details.

Possible errors include

  • authorization_errors: which will be related to your access token
  • validation errors: which will be related to template_id, recipient, tokens, or products. The error should provide the specific section.

Updated 6 months ago


Create Document from a Google Sheet


Example of how Google Sheets can be used to create a PandaDoc document.

Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.