Merging PDF form fields with spreadsheet data using Google Apps Script

At work I sometimes have to fill in PDF forms which is boring and  time consuming. Today I found a way to fix that.

It needs PDF software that makes it possible to import (data)fields into a PDF form and to import and export fields data.  I used the pro version of PDF Studio.

It also needs a Google Spreadsheet-sheet with a first row with 'fieldnames'.

I imported (data)fields (the Google Spreadsheet-sheet fieldnames) into a PDF form.

To learn how to import data into the (data)fields of the PDF-form I first had to export data into a xfdf-file (the export option I choose for).

The (xml-like) xfdf-file has a head, a trunk and a tail,

The head contains (among other things) the name of the PDF file and a modified-something and a original something.

The trunk contains information about the fieldnames and the values.

The tail contains ... the tail.

function pdfFormMerging(){

var ss = SpreadsheetApp.openById("XXX");
var sheet = ss.getSheetByName("YYY");
var range = sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns());
var data = range.getValues();
var uniqueAnchor = "ZZZ";
var object = {};

object = makesObjectOfRowInSpreadsheetWithUniqueAnchor(object,uniqueAnchor,data);

var modified = "aaa";
var original = "bbb";
var formName = "ccc.pdf";
var fileName = "ddd.xfdf"

//the head
var text = '<?xml version="1.0" encoding="UTF-8"?>';
text += '<xfdf xml:space="preserve" xmlns="http://ns.adobe.com/xfdf/">';
text += '<f href="' + formName + '"/>';
text += '<ids modified="' + modified + '" original="' + original + '"/>';
text += '<fields>';

//the trunk
  for(var k in object){
    text += '<field name="' + k + '"><value>' + object[k] + '</value></field>';
  }

//the tail
text += '</fields>';
text += '</xfdf>';

DriveApp.createFile(fileName,text,"text/xml"); 
}


function makesObjectOfRowInSpreadsheetWithUniqueAnchor(rowObject,uniqueAnchor,data){
  
var fieldNames = data[0];
 
  for(var i=0;i<data.length;i++){
    
    if(data[i].indexOf(uniqueAnchor) !== -1){ 
     
      for(var j=0;j<fieldNames.length;j++){
      
        if(fieldNames[j] !== ""){
          rowObject[fieldNames[j]] = data[i][j];   
        }
      }
    }
  }
  return rowObject;
} 

Javascript method reduce()

• JavaScript Array reduce() Method, w3schools
• Simplify your JavaScript – Use .map(), .reduce(), and .filter(), Etienne Talbot
• Write JavaScript loops using map, filter, reduce and find, Flavop Copes
• JavaScript’s Reduce Method Explained By Going On a Diet
• One reduce() to rule them all — How to use reduce in JavaScript, Kristian Poslek
• Javascript `reduce` 101, Igor Irianto

It took some reading before I started understanding the Javascript method reduce ().

According to w3schools:

• The reduce() method reduces the array to a single value.
• The reduce() method executes a provided function for each value of the array (from left-to-right).
• The return value of the function is stored in an accumulator (result/total).

A simple example of the method reduce():

function testOne(){

var serviceFee = 1000;
var invoice = [serviceFee,21*serviceFee/100];

  var result = invoice.reduce(function(accumulator,currentValue){
    return accumulator + currentValue;
  },0)
//1210.0
}

The single value to which an array is reduced can also be a text.

function testTwo(){

var nameArray = ["Mariette","Timmer"];

  var result =  nameArray.reduce(function(accumulator,currentValue){
    return accumulator + currentValue;
  })
//MarietteTimmer
}

The single value to which an array is reduced can even be an array (updated 12-01-2020 13:17).

function testThree(){

var arrayOne = ["Aap","Noot","Mies","Wim","Zus","Jet"];

  var result =  arrayOne.reduce(function(accumulator,currentValue){
    if(currentValue.length > 3){
      accumulator.push(currentValue);
    }
    return accumulator;
  },[])
//[Noot, Mies]
}

My own imagination seems to be the limit.