Form field data to word file template using java script?

[Chris Ju]

Hello,

has anyone tried to fill a fields in a Word file (template in Finder) with data from form fields using java script and adding this Word file to a record as file attachment?

Is that even possible?

Thanks.

Chris

[Sam Moffatt]

I thought I’d replied to this but I guess I didn’t hit submit or something.

My immediate thought would be to use the clipboard to shuttle data around, encode it in Tap Forms and then use VBA to grab the clipboard and splice it into the template, if you put the file in the script directory for Tap Forms then you might be able to attach it via a script as well though it’s a little janky all told.

Some other folk have had success with AppleScript and also Keyboard Maestro that might be able to automate things.

I’m not sure any of it is a good solution for automating Word but that might help you along.

[Chris Ju]

My idea was to replace text/field (e.g. “nName”, “aAddress”, …) in the docx (=xml) file with the content from Tap Forms fields (name, address, …) using javascript. I know, that it is possible with apple script in other apps…

[Brendan]

I don’t know how you would open a docx XML file within JavaScript, then update it and save it back. I don’t think the JavaScriptCore framework I use in Tap Forms would allow that. So Sam’s solution of copy and paste might work. Tap Forms can set values on the clipboard from a Script.

But if you don’t need a Word file for this task, you could use Tap Forms with a custom layout and a static Text object on the layout. Within the text object, insert the field names with square brackets around the names and when you print, Tap Forms will substitute the values.

For example:

Dear [First Name] [Last Name],

   We have come to the realization that you need to pay $[Amount Due] by [Due Date] or you will be charged an additional [Penalty Percent]% interest on your loan.

Sincerely,

The Lending Company

You can even stylize the text within the Text object.

To add a Text object, click the Layout button, then click the A button above the layout inspector panel. Then click on your layout and type in your form. You can even paste from Word into there. But I would probably paste as plain text to remove any formatting and then use Tap Forms to format. Word might add all kinds of crap into the formatting that you don’t want.

If you need more than one page though, you can add another layout and then chain the layouts together.

Thanks!

Brendan

[Chris Ju]

Thanks for the hint.

I’m already using layouts as you described. But the main problem is, using layouts with long texts (so one have to use many layouts)… That is the reason i want to transfer field data from Tap Forms to the docx file …

Thanks!
Chris

[Brendan]

Ah ok. I understand. I would like to enhance the layout engine to allow multiple pages per layout and have a text block span multiple pages automatically. On my wish list.

[Chris Ju]

That is on my wish list, too!

Do you know someone who can grant wishes? ;-)

Thanks!
Chris

[Sam Moffatt]

I figured I’d have an attempt at getting this to work and I wrote a VBA script. I got it to work to parse out a URL encoded key/value string and replace stuff and I sort of got it to work based on a template. I say sort of because if I cold load the template in Word, click “Enable Macros”, it gives me a “Object doesn’t support this action” and if I click “End”, close it out then re-open the template (and “Enable Macros” again) from Finder it seems to work properly.

It works to the point that it takes input from the clipboard (sort of) and then replaces the document. If you look at the raw file you can see that I wrap the placeholders with two underscores and this is mapped back to the keys in the query string pattern.

Sample text to copy:

FirstNamePlaceholder=Tap&LastNamePlaceholder=Forms&PrizePlaceholder=one+million+dollars&TransferAmount=$200&AddressPlaceholder=1%20Infinite%20Loop%0ACupertino%2C%20CA%2095014%0AUSA&SignatureName=Steve+Jobs&

To create the string to copy from an array, something like this should work (borrowed from elsewhere):

var queryString = Object.keys(params).map((key) => {
    return encodeURIComponent(key) + '=' + encodeURIComponent(params[key])
}).join('&');

It’s janky but it is sort of functional.

I’ve wanted a HTML type layout but printing that is oddly hard in the Apple ecosystem. There is printing support for WKWebView but apparently it’s private on the Mac and it looks like maybe it works on iOS. I found a QT based WebKit HTML to PDF library that might work on the Mac but I’m not sure if it’d pass App Store review because you’re embedding WebKit again. I found some code to create a PDF from a WKWebView using Swift that is a little newer but not sure if that’s functional. HTML layout wouldn’t replace a fully fledged multipage aware report generator that would let you do stuff like headers and footers but it might help provide some more basic use cases.

Attachments:

You must be logged in to view attached files.

[Brendan]

When you print in the iOS version the standard layout, I am generating HTML and then printing that. But as you see, there’s no proper paging support and no headers and footers. I’ve tried all the CSS I could muster to try and get headers and footers and paging support to work, but haven’t found anything to work on iOS or Mac with WebKit.

[Sam Moffatt]

I saw this the other day that when I was looking that seemed promising through using tables: https://stackoverflow.com/a/51371081

Not sure if it’s applicable, didn’t test it but it’s a little different to most of the pure CSS solutions.

Tangential idea, could you get a copy of the PDF’s generated from the HTML and then use something to overlay the headers/footers afterwards?

[Sam Moffatt]

One other thing might be to leverage something like Pandoc to generate PDF’s. It’s relatively small though it’s GPL. Not a solution for iOS but does work on MacOS. None of these are great options unfortunately.

[Chris Ju]

I think we’re getting off the topic a bit ;-) …

My only problem is that long texts cannot simply be printed (or saved as PDF). If there are multiple pages I have to work with different fields and layouts.

It would be ideal if a note field could be spanned over several pages (optionally with page numbers) as a PDF / Print.

It is also possible – this is the subject of this thread – to include the field information (address, case number, other information, …) from Tap Forms to a Word document (via Java Script). The Word document could then simply be added to the record in Tap Forms as an attachment.

…

Unfortunately, that is a very difficult topic …

Thanks.
Chris

[Sam Moffatt]

I’m not sure it’s off topic, the problem is building a layout system that handles field elements breaking across multiple pages. Doing things on a single page is comparatively simple (still a bit of effort to make look good), breaking pages is where things start to get more complicated because you need to decide where to split, how to put follow on items and handle that dynamically. If we can figure out a better third party layout engine powered by something like HTML that also adds in the header/footer capability then some of the complexity around breaking elements is deferred to the browser implementation which has already done the heavy lifting to get printing to work. Something like HTML also would lead to a more flexible engine that could support custom rendering of elements, more flexible rendering of linked records and I could see as being closer to a full fledge reporting engine. In a sense all bundled together :)

Thinking a little more laterally you could potentially use a bridge to do a mail merge into Word, doesn’t automate pulling the document in though if there were a data source maybe it could be useful to export the data. Unfortunately I don’t think Word really helps here either.

In the earlier post, I showed you a templated Word document you could use to get something sort of working. I’ll record a video going through how to get it setup.

[Chris Ju]

You are most likely right. Unfortunately, I am completely inexperienced when it comes to such things in the background.

Maybe I am wrong. But in my opinion it would make more sense to enable the transfer of data from Tap Forms to a standard software / document such as MS Word / docx file (even without Java script as a native function and perhaps with the option of an existing Word document to update). Then one would not have to deal with the problems mentioned by Sam and the user would have the many possibilities of standard software (e.g. editing texts in MS Word).

It would be very helpful if you could provide the video so that I can test your suggestion here.

[Sam Moffatt]

Ok, so I did the video recording and uploaded it to YouTube which walks through creating a new form, creating the script for it, a short diversion to talk about script folder access for scripting, checks out the Word template mentioned above and shows how to grab the template file to populate data from the clipboard.

Here’s the script used in the video:

function Word_Template() {
	var first_name_id = 'fld-02f5c873bbdf499595b11624a525d616';
	var last_name_id = 'fld-b79a9795b4b4426594be58027c21f9a8';
	var prize_id = 'fld-b620679ff7024a309ef67624bb8e5ea0';
	var transfer_amount_id = 'fld-30891550250d41d7aa087c87baa7ee9c';
	var transfer_method_id = 'fld-3151d0fa67b342f0ad2c9e4bfbc98197';
	var address_id = 'fld-b424d8e5a1d041baa91ed2823bcc2727';
	var signature_name_id = 'fld-a84db7c9468b41689e6b102938b3c391';

	var fieldMap = {
		"FirstNamePlaceholder": first_name_id,
		"LastNamePlaceholder": last_name_id,
		"PrizePlaceholder": prize_id,
		"TransferAmount": transfer_amount_id,
		"TransferMethod": transfer_method_id,
		"AddressPlaceholder": address_id,
		"SignatureName": signature_name_id
	};

	var queryString = Object.keys(fieldMap).map((key) => {
	    return encodeURIComponent(key) + '=' + encodeURIComponent(record.getFieldValue(fieldMap[key]))
	}).join('&');
	
	Utils.copyTextToClipboard(queryString);
	
	Utils.openUrl("file:///Users/pasamio/Library/Containers/com.tapzapp.tapforms-mac/Data/Documents/TFTest.dotm");
}

Word_Template();

Attached is also a copy of the exported Tap Forms archive with the Word Contacts form with Word Template script plus a bonus sample record included. Not the greatest integration point but I think should be more or less functional.

I think if we were on Windows, it’d be a little easier to interact with tooling because you could use COM to invoke an instance of Word and manipulate it more directly, instruct it to save somewhere and then pull the document in. Microsoft’s tooling on the Mac has lagged and one can’t entirely blame them as it is the competition. I’m not aware of any direct mechanism for integrating with Word on Mac which doesn’t require use of either AppleScript or Microsoft’s own Visual Basic for Applications.

Riffing on this, one platform that I have interacted with before is OpenOffice/LibreOffice UNO which allows programatic access to documents stored in it’s format. The downside of this is that you need to ship or have accessible on the network an instance of OpenOffice/LibreOffice to do the heavy lifting (~250MB download vs ~60MB for Tap Forms uncompressed). That would allow the creation of Word documents that could be attached but would obviously require users to download it.

[Chris Ju]

Wow! That works great so far! Thanks a lot, Sam.

The placeholders do not (yet) work in the header / footer and in text fields of the Word document. But one can place them in tables.

A bigger “problem” is the encoding of the text. Unfortunately we have the characters “ß, ü, ä, ö” in the German alphabet. These are transferred incorrectly.

ß -> √ü
Ä Ö Ü -> √Ñ √ñ √ú
ä ö ü -> √Ñ √ñ √ú

I have not yet found a solution. Maybe you have an idea to these issues.

[Sam Moffatt]

It feels like something is munging the decode of the UTF-8 characters, the fact it’s taking a single character and turning it into two makes me think it’s a 2 byte UTF-8 character and when it’s being decoded that isn’t handled well. Unicode on Windows usually means UTF-16 and I’d expect internally VBA is expecting something similar. Doing a quirk search finds this isn’t an uncommon problem but most of the solutions involve calling into a Windows kernel (!!) API which when I tested it didn’t work properly on the Mac complaining of kernel32 being missing, unsurprisingly. I’ll see if I can figure out a way to coax Word for Mac to decode the Unicode characters properly.

As a fallback, do you have a more or less complete list of characters you care about? I can probably special case decode them with a hard coded translation table.

[Sam Moffatt]

Ok, ended up figuring out an approach to this but it’s a little slow (I can see the copy and replace operations as they happen). I think this is due to leveraging AppleScript to process the query string pieces.

To solve this I went digging for a more comprehensive URL decode method that handles the strings properly and ended up settling on using NSString’s stringByRemovingPercentEncoding however that requires creating an AppleScript file and put it into ~/Library/Application Scripting/com.microsoft.Word so that we can get to it from Word VBA.

Here’s the AppleScript I saved as ~/Library/Application Scripting/com.microsoft.Word/TFIntegration.scpt (you might need to create the directory, it didn’t exist already for me):

use framework "Foundation"

on decodeURIComponent(encodedString)
	set tempValue to stringWithString_(encodedString) of NSString of current application
	set decodedValue to tempValue's stringByRemovingPercentEncoding
	return (decodedValue as string)
end decodeURIComponent

Basically all we’re doing here is creating a NSString object, run it through NSString’s stringByRemovingPercentEncoding, turning it back into an AppleScript string and handing it back to the caller (in this case our Word VBA script).

Calling this from VBA ends up looking like this:

Public Function ASURLDecode(inputValue As String) As String
    ASURLDecode = AppleScriptTask("TFIntegration.scpt", "decodeURIComponent", inputValue)
End Function

Again this is expecting a file named TFIntegration.scrpt to be in ~/Library/Application Scripts/com.microsoft.Word to work properly. You might want to open it with Script Editor on your Mac to make sure MacOS doesn’t grumble about it being from somewhere else it doesn’t like.

I found another example of finding and replacing text that’s more comprehensive and seems to solve the header/footer problem in my testing. I did some changes to it and I’ve updated the autoscript.bas file with the changes. I did some clean up to remove the older method which if someone is only dealing with ASCII characters are much quicker.

In the attached zip is an updated copy of the template including header/footer placeholders and the VBA script, as well as a copy of the TFIntegration.scpt file though you might need to recreate this because of how Apple’s sandboxing code works (scripts are protected). You might need to open it or do something funky. In my searches there was references to AppleScript files not being picked up properly unless there was a restart of the entire Mac, I didn’t have to do that but as a part of this I did see the error and restarted Word a few times which seemed sufficient.

Attachments:

You must be logged in to view attached files.

[Chris Ju]

Ok, that sounds great. I’ll test that as soon as possible and let you know about the results…

[Chris Ju]

I tested the files, but i get an error (see screenshot).

Are you sure i have to put TFIntegration.scrpt into

~/Library/Application Scripting/com.microsoft.Word?

The correct location is maybe

~/Library/Application Scripts/com.microsoft.Word…

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

Yes, it should be ~/Library/Application Scripts/, not sure where that got rewritten in the first part of the reply, later on it has the correct path.

I guess I cleaned up one too many things, just add this back after the End Sub:

Public Function GetLength(a As Variant) As Integer
   If IsEmpty(a) Then
      GetLength = 0
   Else
      GetLength = UBound(a) - LBound(a) + 1
   End If
End Function

That should fix that other error up as well.

[Chris Ju]

Works great now! Thanks!

[Sam Moffatt]

Glad to hear it’s working for you. Not perhaps the perfect solution you’re aiming for but good enough for the time being.

[Author]

Posts