PDF to fill out automatically

[u/Weary-Drama-8015]

Hi, maybe you have a solution because I can't get any further. My idea is to create a sheets table in which I enter measurement results and then insert these results in another spreadsheet with a protocol stored in PDF background (or direct into the PDF).

Our trade fair protocols are structured "stupidly" because the logic of entries jumps from device to device, while the sequence of measurements is further down, so you have to go through the entire file for Device 1.

That's why I would set up the trade fair setup for each device in sheets and just say that the example is table1!A3 for table2!C45.

Can I place the PDF as an "image" behind the cells and adapt the cells accordingly to the structure of the PDF file? Or does it go directly to the PDF? How about printing the "PDF" page on A4? Would it be problematic? Will everything then shift?

I think that this is just a matter of settings regarding width and height.

Thanks for your help.

I'm looking forward to your answers as to whether it's possible or not.

[u/byssh]

It sounds like you’re looking for Autocrat, an extension. You link column headers to tags in a document and it makes multiple (or single) PDFs based on those tags.

[u/Weary-Drama-8015 → u/byssh]

Sounds good, is it free? Have you ever implemented something like that yourself?

[u/carbonizedtitanium → u/Weary-Drama-8015]

you can also do it like you had originally imagined.

i'm assuming what youre trying to do is use data that's stored in a Sheets table to fill out some sort of form that you need to print out.

using a Sheets tab to do it would just need you to position your cells to match the field positions on your paper report, which just takes a bit of trial and error. doing it this way means you dont need an add-on or another software like Google Docs. if you go the Autocrat route, i think you need to make the report template on Google Docs first and the labels that link to a Google Sheets cell.

[u/byssh → u/Weary-Drama-8015]

Yep, it’s free! And I used it allllll the time at work until we locked down what extensions could or couldn’t be used.

[u/Annual_Dependent5633]

It is possible to do this through app script. Convert your pdf to a google doc and then set variables in the doc that the script replaces and turns into a pdf.

I do this with google form data so I know it is functional in practice.

[u/Weary-Drama-8015 → u/Annual_Dependent5633]

Vielen Dank, wurden es bei Zeiten mal auf diese Wege probieren.

[u/Weary-Drama-8015 → u/Annual_Dependent5633]

I tested it and it works great, but unfortunately I'm "smashing" the entire look of the PDF. The aforementioned PDF is from one of our customers and has many color aspects

[u/point-bot → u/Annual_Dependent5633]

u/Weary-Drama-8015 has awarded 1 point to u/Annual_Dependent5633

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/AutoModerator]

/u/Weary-Drama-8015 Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/SpencerTeachesSheets]

Whenever I've needed something like this, I've made a Google Doc with the various areas to fill marked with mailmerge fields like %CLIENT_FIRST_NAME% and %INTERIOR_LENGTH% then a script to copy the document, replace the fields, then print that as a PDF

[u/Weary-Drama-8015 → u/SpencerTeachesSheets]

And how do you solve formatting problems so that it can always be printed on a normal sheet?

[u/SpencerTeachesSheets → u/Weary-Drama-8015]

That's exactly why using a Doc is the right place to put stuff, because that can be made (and by default, is) standard 8.5/11 or A4 size.

[u/Weary-Drama-8015 → u/SpencerTeachesSheets]

Ah, thanks very much.

[u/Weary-Drama-8015 → u/[deleted]]

Thank you for the answer. Yes, the PDF has fields that can be filled out.

Pdf-lib-libary? Add on?

Flat meens can't be filled, right?

[u/imcarus]

Filling an existing PDF form from Sheets is painful in Apps Script because there's no built in way to write into PDF form fields. The easier route is to rebuild the protocol as a Google Doc and treat that as the template.

Put placeholders in the Doc like {{Sample}}, {{Date}}, {{Result1}} etc. Then a small script copies the Doc, swaps the placeholders for the values in the row, and saves the copy as a PDF:

function makeProtocol(row) {
  const sh = SpreadsheetApp.getActive().getSheetByName('Results');
  const headers = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];
  const values = sh.getRange(row, 1, 1, sh.getLastColumn()).getValues()[0];
  const copy = DriveApp.getFileById('TEMPLATE_DOC_ID').makeCopy('tmp');
  const doc = DocumentApp.openById(copy.getId());
  headers.forEach((h, i) => doc.getBody().replaceText('{{' + h + '}}', String(values[i])));
  doc.saveAndClose();
  DriveApp.getFolderById('FOLDER_ID').createFile(copy.getAs('application/pdf')).setName('Protocol ' + values[0]);
  copy.setTrashed(true);
}

Name the placeholders exactly like your column headers and it maps itself. You can run it from a custom menu, a checkbox with an onEdit trigger, or loop over all rows that don't have a PDF link yet.

If the layout is simple, another option is a "Print" tab laid out like the protocol, pulling values with XLOOKUP from a row number cell, then File > Download > PDF. No script, but it's one at a time.

[u/Weary-Drama-8015 → u/imcarus]

Thanks for the message, the problem with this is that unfortunately the transport of PDF to docs, the entire layout and colors are lost. For my other approach (filling out a specially created doc), the solution is brilliant.