AI for Quotation Generation: A Guide with Google Apps Script and ChatGPT Prompt

Discover how to automate quotation generation using Google Apps Script and ChatGPT. This guide offers a step-by-step approach to streamlining business document creation and enhancing efficiency with AI. Ideal for businesses leveraging Google Workspace and considering adopting a more efficient BPM tool like Jodoo.

Author: Daniel Lee | Global Growth Product Manager @ Jodoo

Recent Update: February 23, 2024

In the digital age, automation is a key driver of efficiency and productivity. Today, we’re going to explore how you can use ChatGPT, an advanced language model developed by OpenAI, to automate business processes and workflow. We’ll use the generation of quotation sheets as an example. In the final part, we will also demonstrate why Jodoo is also a solid tool for you to generate business documents.

Many businesses prefer to store data in Google Sheets and create documents in Google Docs. However, creating a sales quotation template manually in these applications can be a time-consuming and error-prone process, especially when dealing with large amounts of data.

This is where Google Apps Script comes in to elevate the efficiency.

What Is Google Apps Script?

Google Apps Script is a rapid application development platform that allows you to build web applications and automate tasks. Here are some key features:

  • Integration with Google Workspace: You can create business applications that integrate with Google Workspace.
  • Cloud-based JavaScript Platform: It's a cloud-based JavaScript platform that lets you integrate with and automate tasks across Google products.
  • Automations: You can write code that programmatically performs tasks across Google products. These automations can be triggered by custom menus, buttons, user actions, or a time-based schedule.
  • Custom Functions: You can write Google Sheets functions in Apps Script and call them from your spreadsheet just like built-in functions.
  • Add-ons: You can build an app that automates tasks or connects to third-party services from within Google Workspace.
  • Chat App: You can provide a conversational interface that lets Google Chat users interact with services as if the service were a person.

How to Automate The Workflow of Creating a Sales Quotation from Google Sheets?

Step 1: Preparing the Data

First, we need to prepare our data. For this example, let’s say we’re a small business selling handmade crafts. We’ll create a Google Sheet with the following columns: Company Name, Company ID, Contact Person, Contact Email, Item, Price, Quantity, Total Price.

Here’s a sample of what our data might look like:

Company Name

Company ID

Contact Person

Contact Email




Total Price

Craft Co.


John Doe





Craft Co.


John Doe





Craft Co.


John Doe

Bath Bomb




Acme Co.


Emily Marks





Step 2: Generating the Apps Script with ChatGPT

Next, we’ll use ChatGPT to generate a Google Apps Script that will read the data from our Google Sheet and generate a quotation sheet in a Google Document.

To do this, we’ll provide ChatGPT with a prompt that describes our task and requirements. For example:

"Can you please create a Google Apps Script that reads data from a Google Sheet and generates quotation documents in Google Docs? The script should do the following:

Read data from a Google Sheet containing the following columns: Company Name, Company ID, Contact Person, Contact Email, Item, Price, Quantity, and Total Price.

Create separate quotation documents for each company found in the sheet.

In each document, include the company information (Company Name, Company ID, Contact Person, Contact Email) once at the beginning.

Include the product data (Item, Price, Quantity, Total Price) in a table format within the document.

Process one row at a time and mark each processed row with 'done' in a new column.

Make the script accessible from a custom menu in the Google Sheet for easy execution.

Please ensure that the script handles cases where multiple rows belong to the same company and creates a single quotation document for that company.

Additionally, make sure the script can be executed directly from the Google Sheet using a custom menu."

ChatGPT will then generate a script based on our prompt. Here’s the sample code:

function generateQuotation() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();
  var numRows = values.length;
  var headers = values[0];
  var doneColumn = headers.indexOf('Done');
  if (doneColumn === -1) {
    sheet.getRange(1, headers.length + 1).setValue('Done'); // Add 'Done' column if it doesn't exist
    doneColumn = headers.length;

  for (var i = 1; i < numRows; i++) {
    if (values[i][doneColumn] !== 'done') { // Check if the row is not marked as 'done'
      var companyData = {};
      var companyName = values[i][headers.indexOf('Company Name')];
      var companyID = values[i][headers.indexOf('Company ID')];
      var contactPerson = values[i][headers.indexOf('Contact Person')];
      var contactEmail = values[i][headers.indexOf('Contact Email')];
      var products = [];

      // Create company data object
      companyData['Company Name'] = companyName;
      companyData['Company ID'] = companyID;
      companyData['Contact Person'] = contactPerson;
      companyData['Contact Email'] = contactEmail;

      // Iterate through rows with the same company information
      while (i < numRows && values[i][headers.indexOf('Company Name')] === companyName) {
        var item = values[i][headers.indexOf('Item')];
        var price = values[i][headers.indexOf('Price')];
        var quantity = values[i][headers.indexOf('Quantity')];
        var totalPrice = values[i][headers.indexOf('Total Price')];

        products.push([item, price, quantity, totalPrice]);
        sheet.getRange(i + 1, doneColumn + 1).setValue('done'); // Mark the row as 'done'

      // Create a new Google Document for the quotation
      var doc = DocumentApp.create('Quotation for ' + companyName);
      var document = DocumentApp.openById(doc.getId());
      var body = document.getBody();

      // Add company data to the document
      body.appendParagraph('Company Name: ' + companyName);
      body.appendParagraph('Company ID: ' + companyID);
      body.appendParagraph('Contact Person: ' + contactPerson);
      body.appendParagraph('Contact Email: ' + contactEmail);

      // Add product data in a table format
      var table = [];
      table.push(['Item', 'Price', 'Quantity', 'Total Price']);
      table = table.concat(products);


Step 3: Implementing the Script

Once we have our script, we’ll implement it in our Google Sheet. This involves copying the script into the script editor in Google Sheets (Extensions > Apps Script) and running the script.

To run this script, follow these steps:

  1. Open your Google Sheet and fill in the quotation data.
  2. Click on "Extensions" -> "Apps Script" to open the script editor.
  3. Paste the sample code into the script editor.
  4. In the Apps Script editor, click the disk icon to save your changes.
  5. Run the generateQuotation function by clicking “Run” with the play button ▶️.
  6. Complete the authorization process to let the App Script process your data.

Step 4: Reviewing the Results

Finally, we’ll review the results. Once the script is successfully executed, you'll see a "Custom Menu" added to your Google Sheet menu bar. Click on the "Custom Menu," and you'll find the "Generate Quotation" option.

If you click on "Generate Quotation" to execute the script, and it will create separate quotation documents for each company with the specified structure.

If everything has been set up correctly, we should now have a Google Document that contains a quotation sheet based on the data in our Google Sheet. Each processed row in the Google Sheet should be marked with ‘done’ in a new column.

A More Efficient Way: Creating Quotation with Jodoo’s Custom Print Template

Creating a sales quotation is a crucial part of any business process. That’s why Jodoo has supported creating quotations or any BPM document by providing the custom print template function. With Jodoo, this process is streamlined and made more efficient.

How to create a custom print template in Jodoo?

Here's a step-by-step guide on how to generate a sales quotation using Jodoo:

  1. Select a form that collects your quotation data and click Edit. If you don't have one, try our Order and Inventory Management template.
  2. Go to Extension > Print Templates and click New Print Template.
  3. On the template design page, customize the template name to something like "Sales Quotation Template."
  4. Add the necessary fields, such as product name, quantity, price, and total. These fields will be automatically filled with the data from your form.
  5. Once your template is ready, you can generate a sales quotation by simply selecting the data and clicking on the print button on PC or mobile.

What are the benefits of using Jodoo to create business documents like quotations and invoices?

Now, let's compare this process with using Google Sheets and Google Docs. While these tools are versatile and widely used, they lack the automation and customization features offered by Jodoo.

Google Apps Script indeed allows for automation within Google Workspace, making Google Sheets a more powerful tool. However, even with this automation, there are still areas where Jodoo stands out.

  1. Ease of Use: While Google Apps Script is powerful, it requires coding knowledge to set up and maintain. This could be a barrier for non-technical users. On the other hand, Jodoo's interface is intuitive and user-friendly, allowing anyone to create and manage sales quotations without needing to write a single line of code.
  2. Integration: Jodoo is designed to work seamlessly with other parts of your business process. Data from forms, databases, and other sources can be easily linked and updated in real-time. While Google Apps Script can be used to create similar integrations, it requires more effort and technical expertise.
  3. Customization: Jodoo's custom print templates offer a high level of customization. You can easily adjust the layout, add images, and include custom fields. While Google Apps Script allows for customization, it's often more complex and time-consuming to achieve the same level of detail.
  4. Scalability: As your business grows, so does the complexity of your processes. Jodoo is built to handle this growth, allowing you to easily manage and automate large amounts of data. While Google Apps Script can be scaled, it often requires more maintenance and troubleshooting as the complexity increases.

In conclusion, while Google Apps Script enhances the capabilities of Google Sheets and ChatGPT can help us to write the Apps Script, Jodoo still offers a more user-friendly, integrated, customizable, and scalable solution for generating sales quotations and other business documents.


In conclusion, the advent of AI and its integration into business processes has revolutionized the way we work. The use of advanced language models like ChatGPT in conjunction with Google Apps Script has shown promising results in automating tasks such as generating quotation documents from Google Sheets data.

However, platforms like Jodoo have taken this a step further, offering a more user-friendly, integrated, customizable, and scalable solution. The future of business process automation is here, and it's clear that AI will play a pivotal role in shaping it. As we continue to explore and harness the potential of AI, we can look forward to even more efficient, streamlined, and innovative ways to conduct business.