PDF.co Web API - Document Parser API - GoogleAppScript - Convert PDF Invoice to Google Sheet

printable version:
PDF-co-Web-API-GoogleAppScript-Convert-PDF-Invoice-to-Google-Sheet.pdf

How to convert PDF invoice to google sheet for document parser API in GoogleAppScript with PDF.co Web API

PDF.co Web API is the Web API with a set of tools for documents manipulation, data conversion, data extraction, splitting and merging of documents. Includes image recognition, built-in OCR, barcode generation and barcode decoders to decode bar codes from scans, pictures and pdf.

On-demand (REST Web API) version:
Web API (on-demand version)

On-premise offline SDK for Windows:
60 Day Free Trial (on-premise)

program.gs

      /**
 * Initial Declaration and References
 */

// Get UI
const ui = SpreadsheetApp.getUi();

// Get the active spreadsheet and the active sheet
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ssid = ss.getId();

/**
 * Add PDF.co Menus in Google Spreadsheet
 */
function onOpen() {
  var menuItems = [
    {name: 'Get Invoice Information', functionName: 'getInvoiceInformation'} 
  ];
  ss.addMenu('PDF.co', menuItems);
}


/**
 * Function which gets Invoice Information using PDF.co
 */
function getInvoiceInformation() {
  
  let invoiceUrlPromptResp = ui.prompt("Please Provide Invoice URL:");
  let invoiceUrl = invoiceUrlPromptResp.getResponseText();
  
  if(invoiceUrlPromptResp.getSelectedButton() == ui.Button.OK && invoiceUrl && invoiceUrl.trim() !== ""){
      // Prepare Payload
      var data = {
        "url": invoiceUrl, //"https://bytescout-com.s3-us-west-2.amazonaws.com/files/demo-files/cloud-api/document-parser/sample-invoice.pdf",
        "outputFormat": "JSON",
        "templateId": "1",
        "async": false,
        "encrypt": "false",
        "inline": "true",
        "password": "",
        "profiles": "",
        "storeResult": false
      };

      // Prepare Request Options
      var options = {
        'method' : 'post',
        'contentType': 'application/json',
        'headers': {
          "x-api-key": "--enter-your-pdf-co-api-key-here--"
        },
        // Convert the JavaScript object to a JSON string.
        'payload' : JSON.stringify(data)
      };
      
      // Get Response
      // https://developers.google.com/apps-script/reference/url-fetch
      var pdfCoResponse = UrlFetchApp.fetch('https://api.pdf.co/v1/pdf/documentparser', options);

      var pdfCoRespText = pdfCoResponse.getContentText();
      var pdfCoRespJson = JSON.parse(pdfCoRespText);

      // Display Result
      if(!pdfCoRespJson.error){
        // Upload file to Google Drive
        showInvoiceResult(pdfCoRespJson.body);    
      }
      else{
        resultUrlCell.setValue(pdfCoRespJson.message);    
      }
  }
  else{
    ui.alert("Please Provide Invoice URL");
  }
}

/**
 * Render Invoice Data to Spreadsheet
 */
function showInvoiceResult(invResultBody){
  var cmpName = getObjectValue(invResultBody, "companyName");
  var invName = getObjectValue(invResultBody, "companyName2");
  var invoiceId = getObjectValue(invResultBody, "invoiceId");
  var issuedDate = getObjectValue(invResultBody, "dateIssued");
  var dueDate = getObjectValue(invResultBody, "dateDue");
  var bankAccount = getObjectValue(invResultBody, "bankAccount");
  var total = getObjectValue(invResultBody, "total");
  var subTotal = getObjectValue(invResultBody, "subTotal");
  var tax = getObjectValue(invResultBody, "tax");

  var tableData = getTableData(invResultBody, "table");

  var cellIndex = 1;

  if(cmpName && cmpName !== ""){
    ss.getRange(`A${cellIndex}`).setFontWeight("bold").setValue("Company Name");
    ss.getRange(`B${cellIndex}`).setValue(cmpName);
    cellIndex++;
  }

  if(invName && invName !== ""){
    ss.getRange(`A${cellIndex}`).setFontWeight("bold").setValue("Invoice Name");
    ss.getRange(`B${cellIndex}`).setValue(invName);
    cellIndex++;
  }

  if(invoiceId && invoiceId !== ""){
    ss.getRange(`A${cellIndex}`).setFontWeight("bold").setValue("Invoice #");
    ss.getRange(`B${cellIndex}`).setValue(invoiceId);
    cellIndex++;
  }

  if(issuedDate && issuedDate !== ""){
    ss.getRange(`A${cellIndex}`).setFontWeight("bold").setValue("Issued Date");
    ss.getRange(`B${cellIndex}`).setValue(issuedDate);
    cellIndex++;
  }

  if(dueDate && dueDate !== ""){
    ss.getRange(`A${cellIndex}`).setFontWeight("bold").setValue("Due Date");
    ss.getRange(`B${cellIndex}`).setValue(dueDate);
    cellIndex++;
  }

  if(bankAccount && bankAccount !== ""){
    ss.getRange(`A${cellIndex}`).setFontWeight("bold").setValue("Bank Account");
    ss.getRange(`B${cellIndex}`).setValue(bankAccount);
    cellIndex++;
  }

  if(total && total !== ""){
    ss.getRange(`A${cellIndex}`).setFontWeight("bold").setValue("Total");
    ss.getRange(`B${cellIndex}`).setValue(total);
    cellIndex++;
  }

  if(subTotal && subTotal !== ""){
    ss.getRange(`A${cellIndex}`).setFontWeight("bold").setValue("Sub Total");
    ss.getRange(`B${cellIndex}`).setValue(subTotal);
    cellIndex++;
  }

  if(tax && tax !== ""){
    ss.getRange(`A${cellIndex}`).setFontWeight("bold").setValue("Tax");
    ss.getRange(`B${cellIndex}`).setValue(tax);
    cellIndex++;
  }

  // Render Table
  if(tableData && tableData.length > 0){
    cellIndex++;

    ss.getRange(`A${cellIndex}`).setFontWeight("bold").setValue("Product Name");
    ss.getRange(`B${cellIndex}`).setFontWeight("bold").setValue("Item Price");
    ss.getRange(`C${cellIndex}`).setFontWeight("bold").setValue("Qty");
    ss.getRange(`D${cellIndex}`).setFontWeight("bold").setValue("Total Price");
    cellIndex++;

    for(var iTableData = 0; iTableData < tableData.length; iTableData++){
      ss.getRange(`A${cellIndex}`).setValue(tableData[iTableData].prodName);
      ss.getRange(`B${cellIndex}`).setValue(tableData[iTableData].itmPrice);
      ss.getRange(`C${cellIndex}`).setValue(tableData[iTableData].qty);
      ss.getRange(`D${cellIndex}`).setValue(tableData[iTableData].totalPrice);
      cellIndex++;
    }
  }
}

/**
 * Get Json Object Value
 */
function getObjectValue(jsonBody, fieldName){
  var oRet = "";
  if(jsonBody && jsonBody.objects && jsonBody.objects.length > 0){
    var findObjField = jsonBody.objects.filter(x => x.name === fieldName && x.objectType === "field");
    if(findObjField && findObjField.length > 0){
      oRet = findObjField[0].value;
    }
  }

  return oRet;
}

/**
 * Get Table formatted data from input Json
 */
function getTableData(jsonBody, fieldName){
  var oRet = [];

  if(jsonBody && jsonBody.objects && jsonBody.objects.length > 0){
    var findObjTable = jsonBody.objects.filter(x => x.name === fieldName && x.objectType === "table");
    if(findObjTable && findObjTable.length > 0 && findObjTable[0].rows && findObjTable[0].rows.length > 0){
      var tableRows = findObjTable[0].rows;

      for(var iRow = 0; iRow < tableRows.length; iRow++){
        var qty = tableRows[iRow].column1.value;
        var prodName = tableRows[iRow].column2.value;
        var itmPrice = tableRows[iRow].column3.value;
        var totalPrice = tableRows[iRow].column4.value;

        oRet.push({ qty: qty, prodName: prodName, itmPrice: itmPrice, totalPrice: totalPrice });
      }
    }
  }

  return oRet;
}