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

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:

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:

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'
        i++;
      }

      // 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);
      body.appendTable(table);

      document.saveAndClose();
    }
  }
}

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:

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:

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.

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.

Conclusion

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.