Link Search Menu Expand Document

Convert PDF Invoice to Google Sheet - GoogleAppScript

Document Parser sample in GoogleAppScript demonstrating ‘Convert PDF Invoice to Google Sheet’

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;
}



PDF.co Web API: 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.

Get your PDF.co API key here!

Download Source Code (.zip)

return to the previous page explore Document Parser endpoint