Join List > Integromat > Create CSV

Hey all!

I’m looking to create a CSV file from a Join List column. The Join List contains a comma-separated list of names and I’d like for each name to be on its own row of the CSV. Sounds simple enough, but I can’t figure it out.

Anyone know the magic?

1 Like

Can integromat help with this? They have a CSV module if I’m not wrong … In Atlanta already btw :wink:

1 Like

Right…I’m getting warmer!

I’ve been doing this for a while, and it works like a charm. The trick is to use a template column to create the row, using a delimiter. And then a joined list to stitch all the rows together using crlf as the joined list separator. Then pass that through the webhook as a single value.

It’s late here now, but I can post a more detailed example in the morning if you need it.

I should add… the method I use requires just two Integromat modules: webhook and gmail. That’s it - no messing around with CSV or aggregator modules or any of that garbage :wink:

6 Likes

Did you get this sorted?

Yes, I was able to generate a CSV of names…but then I realized what I really wanted was to have a list of names within a populate a PDF template…no idea how to do that effectively.

oh, PDF is a different animal.
I know that some people use PDFMonkey. I actually have a tried and tested method for generating PDF using apps script, that I’ve used on multiple projects. Essentially it involves creating a GDoc template, and then writing some code to plug in the data and convert the GDoc to PDF.

1 Like

Hello @Darren_Murphy s this technique quick to execute?
Because I use the same thing.
I made a webhook from glide to a google script, and I have a model under google sheet that I copy and replace the type values ​​{name} by the values ​​of the receipt by the webhook and finally I export to pdf.
But it takes him about 1 to 2 minutes to do this operation.

Creating dynamic PDFs in integromat

Hey @Robert_Petitto,
I am not sure if this is what you are looking for but I think it may help you along. At least I hope it does…

This may help

1 Like

Yes, the technique that I use is an adaptation of your method :slight_smile:

I wouldn’t say it’s lightning fast, but it generally takes several seconds (not minutes) to generate each PDF. I could probably share some code if you wanted to compare notes…?

Yes if possible,
I would like to see your technique because
my script is complicated finally not complicated but very personalized, with sorting of data and other stuff which greatly slows down the execution in addition to basic functions such as copying a sheet or exporting to pdf in google script.

Here is a somewhat generic example:

function create_pdf(data, obj) {
  console.time("Create PDF");
  var pdf_file_name = obj.user_name + ' - ' + obj.requested_at + '.pdf';
  pdf_file_name = pdf_file_name.replace(/,/g,"");
  var temp_folder = DriveApp.getFolderById(TEMP_FOLDER_ID);
  var saved_pdf_folder = DriveApp.getFolderById(SAVED_PDF_FOLDER_ID);
  var template = DriveApp.getFileById(PDF_TEMPLATE_ID);
  var temp_copy = template.makeCopy().moveTo(temp_folder);
  var temp_doc_id = temp_copy.getId();
  var temp_doc = DocumentApp.openById(temp_doc_id);
  var body = temp_doc.getBody();
  
  while (data.user_keys.length > 0) {
    body.replaceText('{' + data.user_keys.shift() + '}', data.user_values.shift());
  }

  var tables = body.getTables();
  var table = tables[0];
  var row = 1;
  while (data.shift_data.length > 0) {
    var row_data = data.shift_data.shift();
    var tr = table.insertTableRow(row);
    while (row_data.length > 0) {
      var text = row_data.shift();
      tr.appendTableCell(text);
    }
  }

  temp_doc.setName(pdf_file_name);
  temp_doc.saveAndClose();
  var temp_file_id = temp_doc.getId();
  var temp_file = DriveApp.getFileById(temp_file_id);
  temp_file.setName(pdf_file_name);
  var pdf = DriveApp.createFile(temp_file.getAs('application/pdf'));
  pdf.moveTo(saved_pdf_folder).setName(pdf_file_name);
  var pdf_file_id = pdf.getId();
  temp_file.setTrashed(true);
  console.timeEnd("Create PDF");
  return pdf_file_id;
}

Before creating the PDF, I collect the data from the GSheet and arrange it into a data structure, which is then used to inject the values into the PDF template.

These few lines:

while (data.user_keys.length > 0) {
    body.replaceText('{' + data.user_keys.shift() + '}', data.user_values.shift());
  }

basically do a search and replace through the entire template. The key to this is to match the GSheet column headers to the substitution variable names in the PDF template. For example, if I have a column in the GSheet called “Name”, it will have a matching substitution variable called “{Name}” in the template. So data.user_keys is essentially an array of my GSheet column headers, and data.user_values is an array of matching values from a specific row in the sheet.

And these few lines:

while (data.shift_data.length > 0) {
    var row_data = data.shift_data.shift();
    var tr = table.insertTableRow(row);
    while (row_data.length > 0) {
      var text = row_data.shift();
      tr.appendTableCell(text);
    }
  }

… build a table containing the values from several rows.

The rest of the code does the PDF conversion and saves it to a GDrive folder, and returns the generated File ID, which is written back into the GSheet, and becomes available to be used in Glide.

2 Likes

Yes great,
we have the same principle, but I’m going to do some tests with a google doc instead of a google sheet.

1 Like

The only big difference is that I use a webhook for the data and you the sheet linked to Glide.

Yes.
The most common use case that I have is that a PDF needs to be generated whenever a new row is added.

  • In such cases, I will have a “PDF Generated At” column in the GSheet
  • When a new row is added from Glide, that column is empty
  • A trigger script detects the new row (with the empty timestamp), and generates the PDF
  • When done, it sets the “PDF Generated At” column to the current time

One thing I found is that you can get a race condition when using a trigger if there are multiple rows added quickly, which sometimes results in multiple PDF’s being generated. To avoid this, I write a value “Processing…” to the timestamp column before generating the PDF. This prevents any subsequent script invocations from processing the same row before the first one is finished.

3 Likes

excellent
thank you for sharing :+1: :smiley: :wave:

Sorry @Robert_Petitto for diverting your original subject (for my benefit)

2 Likes

@Darren_Murphy ’s approach and code works a treat :star2: he wrote the backend functions for my telemedicine app, including PDF generation, and emailing. Wizard work 🪄:mage:

1 Like