By Aryan Irani
Small to mid-size businesses receive PDF invoices from vendors and need to store details like invoice number, date of invoice, total amount, and vendor name. Doing this manually for all your vendors is a tedious and time-consuming process. In this blog we are going to be automating the process of extracting details from PDFs and populating the Google Sheet using the Gemini 1.5 Flash model and Google Apps Script.
The Problem with Manual Invoice Processing
Manually processing invoices is a tedious and inefficient task. It involves poring over countless PDF documents, meticulously extracting data, and entering it into spreadsheets or databases. This process is not only time-consuming but also prone to human errors, which can lead to costly consequences.
Imagine a scenario where your accounts payable team spends hours each week manually processing invoices. This not only delays payments but also increases the risk of errors, potentially damaging relationships with your vendors. Moreover, manual data entry ties up your team’s time, preventing them from focusing on more strategic tasks that drive business growth.
What can this Automation do?
This function is designed to automate the process of manual data entry inside of Google Sheets. This function uses the Gemini 1.5 Flash model to extract required/ specific details from the Google Sheet and populate the Google Sheet. Here’s a high-level overview of how the solution works:
- PDF Conversion: The Google Apps Script converts your PDF invoices into a format that Gemini 1.5 Flash can easily understand.
- Data Extraction: The script interacts with Gemini 1.5 Flash, providing it with the converted invoice data and instructions on what information to extract.
- Spreadsheet Update: The extracted data is seamlessly populated into a Google Sheet, ready for further processing or analysis.
Now that we have understood the problem and the possible solution, let’s get coding.
Sample Google Sheet
The Google Sheet that I will be using for this blog contains the following details:
Write the Automation Script
While you are in the Google Sheet, let’s open up the Script Editor to write some Google Apps Script. To open the Script Editor, follow these steps:
Click on Extensions and open Apps Script:
This brings up the Script Editor:
Now, enter the code:
function processPdfToSheet() {
var archiveFolderId = "YOUR ARCHIVE FOLDER ID";
const folderId1 = "YOUR FOLDER ID";
var folder = DriveApp.getFolderById(folderId1);
var files = folder.getFiles();
while (files.hasNext()) {
var file = files.next();
if (file.getMimeType() === MimeType.PDF) { // Filter PDF files
var fileId = file.getId();
var pdfContent = convertPdfToGoogleDoc(fileId, folder);
var responseData = sendToGemini(pdfContent);
var details = extractFields(responseData);
// Update Google Sheet with extracted details
updateSheet(details);
// Move the original PDF and the converted Google Doc to the archive folder
var archiveFolder = DriveApp.getFolderById(archiveFolderId);
moveFileToArchive(file, archiveFolder);
}
}
}The convertPdfToGoogleDoc() function processes a PDF file by retrieving its content, creating a new Google Doc with extracted text, and then deleting the converted Google Doc to avoid clutter. We use the Drive API and DocumentApp to perform these operations, ensuring accurate text extraction.
We then return the extracted text content, which can be further processed or used in applications.
function sendToGemini(pdfData) {
const GEMINI_KEY = 'YOUR_GEMINI_KEY';
const GEMINI_ENDPOINT = `https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-flash-latest:generateContent?key=${GEMINI_KEY}`;
var headers = {
"Content-Type": "application/json",
"Accept": "application/json"
};
var requestBody = {
"contents": [
{
"parts": [
{
"text": `extract the following details: Vendor Name: Invoice Number: Amount Due: Due Date: Description Tax: \n${pdfData}`
}
]
}
]
};
var options = {
"method": "POST",
"headers": headers,
"payload": JSON.stringify(requestBody)
};
try {
var response = UrlFetchApp.fetch(GEMINI_ENDPOINT, options);
var datanew = JSON.parse(response.getContentText());
return datanew;
} catch (error) {
Logger.log('Error calling Gemini API: ' + error);
return null;
}
}We then use the sendToGemini() function to interact with Gemini 1.5 Flash to process and extract data from the text content. We construct a request that contains our prompt that specifies the details we want to extract from the content and then send the request to Gemini 1.5 Flash.
In case of errors, it logs an error message and returns null. If successful, it returns the extracted details that we received from Gemini 1.5 Flash.
function extractFields(datanew) {
if (!datanew || !datanew.candidates || !datanew.candidates.length) {
Logger.log('No valid data returned from Gemini.');
return {};
}
var textContent = datanew.candidates[0].content.parts[0].text;
textContent = textContent.replace(/- /g, '').trim();
var lines = textContent.split('\n');
var details = {};
lines.forEach(function (line) {
var parts = line.split(':');
if (parts.length === 2) {
var key = parts[0].replace(/\*\*/g, '').trim();
var value = parts[1].replace(/\*\*/g, '').trim();
details[key] = value;
}
});
return details;
}We use the extractFields() function to process the response from Gemini 1.5 Flash to extract relevant details. We check the validity of the response, extract the text content, split it into lines and iterate through each line to identify key-value pairs.
These extracted details, such as Vendor Name, Invoice Number, and others, are returned in a structured object for further use.
function updateSheet(details) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Invoices");
var range = sheet.getDataRange();
var values = range.getValues();
var vendorName = details['Vendor Name'];
var nameFound = false;
var currentDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'MM/dd/yy');
var formattedDateTime = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");
for (var i = 1; i < values.length; i++) {
if (values[i][2].toLowerCase() === vendorName.toLowerCase()) { // Compare by Vendor Name
nameFound = true;
sheet.getRange(i + 1, 1).setValue(details['Invoice Number']); // Column A
sheet.getRange(i + 1, 6).setValue(details['Amount Due']); // Column F
sheet.getRange(i + 1, 7).setValue(details['Due Date']); // Column G
sheet.getRange(i + 1, 9).setValue("Last updated at: " + formattedDateTime); // Column I
Logger.log("Updated Row " + (i + 1));
break;
}
}
if (!nameFound) {
Logger.log("Vendor not found: " + vendorName);
var newRow = values.length + 1;
sheet.getRange(newRow, 1).setValue(details['Invoice Number']); // Column A
sheet.getRange(newRow, 3).setValue(vendorName); // Column C
sheet.getRange(newRow, 4).setValue(details['Description']); // Column D
sheet.getRange(newRow, 5).setValue(vendorName); // Column E
sheet.getRange(newRow, 6).setValue(details['Amount Due']); // Column F
sheet.getRange(newRow, 7).setValue(details['Due Date']); // Column G
sheet.getRange(i + 1, 9).setValue("Last updated at: " + formattedDateTime); // Column I
Logger.log("New Row Added");
}
}The updateSheet() function is responsible for managing entries within the Google Sheet. It first checks if the client or vendor already exists in the sheet. If a matching entry is found, the function updates the existing record with the new details. However, if no existing record is found, a new entry is created to store the information.
function moveFileToArchive(file, archiveFolder) {
file.moveTo(archiveFolder);
}The moveFileToArchive() function is a simple help function that takes the file and moves it to the archive folder after the data extraction and the Google Sheet is updated.
Our code is complete and good to go.
Check the Output
It’s time to see if the code is able to access the invoices, extract details and update the Google Sheet successfully.
Conclusion
In today’s digital age, automation is key to staying competitive. By harnessing the power of Gemini 1.5 Flash and Google Apps Script, you can transform your invoice processing workflow, saving time, reducing errors, and empowering your team to focus on what truly matters.
We encourage you to explore the possibilities of this solution and discover how it can revolutionize your data extraction processes. Remember, automation is not just about efficiency; it’s about unlocking new opportunities for growth and innovation.
To see the full code in one place, click here.
