Gist for the Infinum's blog post Automate tedious tasks with Vertex AI and Google AppScript

processInvoices.gs

const LABEL_DONE = "done-✅"

const IS_PRODUCTION = false

var ACCOUNTANT_ADDRESSES = ""

const DIR_ID_INCOMING_INVOICES = "GoogleDriveInvoicesDirectoryID"


function processInvoices() {

  const threads = GmailApp.search('from:approved.sender@infinum.com to:montenegro.invoices.import@infinum.com AND NOT label:done-✅')

  if (IS_PRODUCTION) {
    ACCOUNTANT_ADDRESSES = "email.of.your@accountant.com, some.other.emails@accountant.com "
  } else {
    ACCOUNTANT_ADDRESSES = "your@email.com"
  }

  if (threads.length == 0) {
    Logger.log("No found threads")
  } else {
    Logger.log(`Number of therads ${threads.length}`)
  }

  for (var i = 0; i < threads.length; i++) {
    var thread = threads[i];

    if (thread.getMessages().length == 0) {
      Logger.log("No messages in thread")
      continue
    }

    var message = thread.getMessages()[0]
    Logger.log(message.getSubject())
    var attachments = message.getAttachments()
    if (attachments.length == 0) {
      Logger.log("Please send attachment")
      errorFormat(`AUTOMATIC RESPONSE: Hello, there is no attachment. Please send an email with attachment`, thread)
    } else {

      for (var j = 0; j < attachments.length; j++) {
        var attachment = attachments[j];

        API_ENDPOINT = "us-central1-aiplatform.googleapis.com"
        PROJECT_ID = "projectID"
        LOCATION_ID = "us-central1"
        MODEL_ID = "gemini-1.5-flash-001"
        var url = `https://${API_ENDPOINT}/v1/projects/${PROJECT_ID}/locations/${LOCATION_ID}/publishers/google/models/${MODEL_ID}:generateContent`;

        // Attempt to parse the response if it is JSON
        try {
          // Read the file from attachments
          var blob = attachment.copyBlob().getAs(MimeType.PDF);
          var pdfBase64 = Utilities.base64Encode(blob.getBytes());
          var jsonRequestBody = getGeminiRequest(pdfBase64)
          var options = getGeminiOptions(jsonRequestBody)

          // Make the API request
          var response = UrlFetchApp.fetch(url, options);
          var responseData = JSON.parse(response.getContentText());
          Logger.log(responseData)
          var combinedText = getInvoiceNameFromResponse(responseData)
          Logger.log('Combined Text: ' + combinedText);

          var subject = combinedText
          var copySubject = subject
          Logger.log('Subject: ' + subject);

          if (subject.split("-").length < 4) {
            errorFormat("Hello, we couldn't process the invoice, please try again. ", thread)
            continue
          }

          var year = subject.split("-")[1]
          var month = subject.split("-")[2]

          var googleDriveFolder = DriveApp.getFolderById(DIR_ID_INCOMING_INVOICES)

          var currentYearFolders = googleDriveFolder.getFoldersByName(year)

          if (currentYearFolders.hasNext() === false) {
            googleDriveFolder.createFolder(year)
            Logger.log("No existing year folder")
          }

          Logger.log(`year ${year}`)
          Logger.log(`month ${month}`)

          var currentMonthFolders = currentYearFolders.next().getFoldersByName(month)
          if (!currentMonthFolders.hasNext()) {
            currentYearFolders.createFolder(`${month}`)
            Logger.log("No existing month folder")
          }

          Logger.log(`${subject}`)

          blob.setName(`${combinedText}`)
          var file = currentMonthFolders.next().createFile(blob)
          Logger.log(`File name: ${file.getName()}`)

          GmailApp.sendEmail('your@email.com', copySubject, ``, {
            name: 'Your Name',
            attachments: [file],
            htmlBody: `AUTOMATIC DELIVERY: Hello, you can find following <b>${copySubject}</b> invoice in the attachments`,
            cc: `${ACCOUNTANT_ADDRESSES}`
          })

          if (IS_PRODUCTION) {
            thread.replyAll(``, {
              name: 'Your name',
              htmlBody: `AUTOMATIC RESPONSE: Invoice <b> ${copySubject} </b> is processed successfully.`,
              cc: `some.email@email.com`
            })

            thread.addLabel(GmailApp.getUserLabelByName(LABEL_DONE))
            thread.markRead()
          }

        } catch (e) {
          Logger.log('Error parsing response: ' + e.message);
          errorFormat(`Hello, we couldn't process the invoice, please try again. ${subject}`, thread)
        }
      }
    }
  }
}

function errorFormat(addionalMassage, thread) {
  if (IS_PRODUCTION) {
    thread.replyAll(`Hello, we couldn't process the invoice, please try again.${addionalMassage}`, {
      name: 'Mladen Rakonjac'
    })
    console.error(`Hello, we couldn't process the invoice, please try again.  ${addionalMassage}`)
  } else {
    console.error(`Hello, we couldn't process the invoice, please try again.  ${addionalMassage}`)
  }

  if (IS_PRODUCTION) {
    thread.addLabel(GmailApp.getUserLabelByName(LABEL_DONE))
  }

}

function getGeminiRequest(pdfBase64) {
  return {
    contents: [
      {
        role: "user",
        parts: [
          {
            inlineData: {
              mimeType: "application/pdf",
              data: pdfBase64
            }
          },
          {
            text: `You are a document entity extraction specialist. Given a document, your task is to extract the text value of the following entities:
                COMPANY_NAME is the name of the company issuing the invoice, and it should not be Infinum d.o.o.
                YYYY is the year of the invoice transaction in the format YYYY
                MM is the month of the invoice transaction in the format MM
                DATE_ISSUED is the date of the invoice issuance in the format DD.MM.YYYY (German format)
                TOTAL_AMOUNT_TO_PAY is in the format used in Germany without currency symbol or sign (examples "0.000,00" or "00,00", or "0,00" ) and it should never be in format "00.00"
               - The values must only include text found in the document, but you can format it
               - Do not normalize any entity value
               - If an entity is not found in the document, set the entity value to null.
               - Give just one answer and format it: COMPANY_NAME-YYYY-MM-DATE_ISSUED-TOTAL_AMOUNT_TO_PAY`
          }
        ]
      }
    ],
    generationConfig: {
      maxOutputTokens: 8192,
      temperature: 1,
      topP: 0.95
    }
  };
}

function getGeminiOptions(jsonRequestBody) {
  return {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(jsonRequestBody),
    headers: {
      Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
    },
    muteHttpExceptions: true
  };
}

function getInvoiceNameFromResponse(responseData) {
  if (!responseData.candidates || !Array.isArray(responseData.candidates)) {
    throw new Error('Invalid response structure: candidates is missing or not an array');
  }

  for (let candidate of responseData.candidates) {
    if (candidate.content && Array.isArray(candidate.content.parts)) {
      for (let part of candidate.content.parts) {
        if (part.text) {
          return part.text.trim();
        }
      }
    }
  }

  throw new Error('No text found in the response');
}