How best to generate text (a document) from a table?

[Connor_McCormick]

Hello everyone,

My current goal is to use Coda to automate the creation of Scope of Work documents.

For each of my prospective customers I want to:

1. Input information about them into tables
2. Select a particular customer to populate a Section that contains their scope of work which should contain:
   a. One off items like their name, the date, and my contact information
   b. FormulaMap generated items. For example, a customer may have multiple security locations, and each location may require multiple security cameras. Ideally, all of this is nicely formatted
3. Export the section to PDF/Word or another format that can be saved, emailed, and opened by the customer. It’s potentially ok if it’s only emailable, but it should retain nice formatting.

While these are the criteria for my own project, I imagine they may broadly cover many other usecases for other users. I know this is already on Coda’s radar and some other conversations about other users needing more robust tools for text can be found here:

• Can Coda replace a Wiki?
• Add a new format column: "the Canvas"

While Coda works out the details of the enhanced text editing abilities we all have stuff to build, so I’m hoping that this can be a place to share your tips for how to build dynamically generated text!

---

Some caveats:

• Any format is better than no format. I’m not committed to populating a Section. If it would be better to build an SVG (I’m looking at you @Paul_Danyliuk) then that’s fine. At this point, having something hacky that works is better than having nothing.
• Beautiful counts. The documents we generate need to go to customers, managers, and coworkers, so let’s make them look nice.

In an ideal world our solutions are data-structure agnostic (so anyone with a preexisting table layout can get this to work for them with not too much difficulty) and drop-in (so that someone can just copy a couple of tables and formulas from a shared document and become imbued with the awesome power of document generation)

---

Example doc with example output:

---

I’ll follow this up with my own learnings as I go.

[Johg_Ananda]

@Connor_McCormick One way I’ve approached this is to use Coda to generate rows with all the field data, then link that to a Zapier which will connect to SendRequest (electronic signature app) which will allow you to map the fields into the document. Its a specific use case but may get the almonds activated…

[BenLee]

We haven’t had a chance to dig into nice printing just yet, so email might be your best bet at the moment. When I had to submit my timesheets at REI, instead of creating a new spreadsheet every time, I created a Coda doc that was loaded with some HTML email code to send the tables every pay period. It’s a little involved to set up, but might offer some ideas for what can be done. I really just logged my time and clicked “Send Email”. After setup, it was super easy.

Clocking In a Timesheet + Gmail

[Connor_McCormick]

@Johg_Ananda thank you for idea! I think that the integrations (especially with Zapier) may be the ticket, although I was kinda hoping I could avoid the Zapier integration since it costs money. I wish Pro Coda accounts could come with a higher tier of Zapier access because that would really add to the app. I don’t know if Zapier would do a deal like that but it certainly would be nice.

[Connor_McCormick]

@BenLee Thank you so much for the response and the example!

Looking forward to playing around with it!

[Connor_McCormick]

Hi Ben,

Looks like there may be a bug in your coda doc at the moment. This is seen under the Send Email sheet

[BenLee]

Thanks for the note!

That’s an old template. I’ll revisit and update soon!

[Bill_French]

@Connor_McCormick

Beautiful counts. The documents we generate need to go to customers, managers, and coworkers, so let’s make them look nice.

If beauty and precision is important, perhaps this will give you some ideas.

If the solution needs to be simple and fully Coda-bound, this might be a good read.

[Connor_McCormick]

Thank you Bill, I have been taking a look at these, they may indeed be the solution I am looking for, albeit with a lot more setup that I was hoping for. If I can get something more general running for others I’ll be sure to share it here. Thank you!

[Connor_McCormick]

@Bill_French your work here has also been helpful to me. I’m linking it so others can find it:

Community Hackathon: Charts in Email - Nov. 8th, 2019

Reply to this topic to post your “Charts in Email” Hackathon docs! More info here:

[Connor_McCormick]

Ok! I learned a lot about trying to generate sections dynamically. Most of it has to do with getting over some of the current shortcomings of Coda. As usual, we know how hard they are working and that they are likely to address this in the near future. This is for those that would like to see what pitfalls and limitations exist for document generation when attempting a pure Coda approach.

This is a long post, so if you’d like to just play with the doc you can find it here:

Learnings:

Newline doesn’t work as expected in Format and Concatenate, neither in the Character(10) nor the =newline nor the Shift + Enter methods.

Example to come

---

The best way to build nested FormulaMap formulas is by starting with the innermost component you want to render and then work outward. It’s noticeably more difficult to reason about the output of FormulaMap than it is to reason about for loops (or perhaps it’s just unfamiliar).

The important concept to get is that FormulaMap returns text, which can then be passed to an enclosing FormulaMap where Format or Concatenate is function performed on the CurrentValue.

FormulaMap(Summary, Concatenate(Format("{1}", CurrentValue.Name), FormulaMap(CurrentValue.Subitem, Format("
{1}", CurrentValue.Name))))

Output:

First Name in Summary
First Subitem in First Name
Second Subitem in First Name

Second Name in Summary
First Subitem in Second Name

---

Format retains all the formatting of the underlying text, Concatenate only retains the color, weight, and highlighting.

Example to come

---

Formulas are not at all modular, unlike functions might be in a programming language. Therefore, building them can become tedious, repetitive, and confusing. The small cell for editing doesn’t help this (a larger cell, similar to the one used for editing text would certainly help).

Here is the whole final formula to dynamically generate the Section. It’s messy, hard to read, and very annoying to edit in the cell:

FormulaMap(Summary, Concatenate(Format("{1}                                                                                                                                                                                                           
", CurrentValue.Name), FormulaMap(CurrentValue.Locations, Format("{1}
{2}
{3}  

   
", CurrentValue.Name, If(CurrentValue.[Image URL].IsBlank(), ' ', Image(CurrentValue.[Image URL], 800, 600)),
Concatenate(
Format("The intended installations are:
{1}
", CurrentValue.Devices.Name.FormulaMap(ToText(CurrentValue)).BulletedList()),

FormulaMap(CurrentValue.Devices, 
Concatenate(Format("
{1}
{2}
", CurrentValue.Name, Concatenate(If(CurrentValue.[Image URL].IsNotBlank(), Image(CurrentValue.[Image URL].ToText(), 800, 600), ""),
Format("
{1}
    Service Type: {2}
    Color: {3}
",[Output Formatting].Details.First(), CurrentValue.[Service Type].ToText(),
CurrentValue.Color.ToText())))))
)))))

---

Default Text formatting for a column would be really helpful, right now I have to manually format each row (which my users won’t know to do, and won’t be able to do at all on mobile). Alternatively, a formula that formats the text display could do the trick. Perhaps FormatDisplay or extending the Format syntax could work.
E.g.

Format("#Example Header 1 ##{1}", "Example Header 2")

output:

Example Header 1

Example Header 2

This approach could do the trick too, though it currently does not work.

---

Lack of access to external namespaces is surprisingly not limiting.

In a standard loop you might instantiate a variable and then access it later. With Coda, you build it into the row that you’re accessing (by way of CurrentValue) with this method you can access any data (so long as your tables are linked with Lookups or cross-populated with Filter formulas).

---

Images are very difficult to work with in formulas. The Images function (inexplicably) only works with external images. Whereas if you’re trying to access an image that is already within your document it appears in a uselessly small thumbnail format.

As a potentially important additional constraint for any product developers looking at this, the image also needs to be able to be Concatenated with the prior and subsequent text.

The additional strangeness is that converting the image to a url with imageName.Url() and then putting that into the Image formula Image(imageName.Url(), 100, 100) doesn’t work. Just shows a blank image:

Hacky Solution
It is possible to access the direct link to the aws image. It requires uploading the image, manually copying the url, then pasting it in a new “ImageUrl” column, which can then be opened with the Image formula. Your url will look something like:
https://coda.io/contentProxy/TTUWqb1vdS/blobs/sn-G5RznG-B5I/b86f53a52af8c31399274e22a5d4b46533c2f28b8201fc353ee071838954a906c6d0c0269b5fdd0d856f3e7e68d5c9efcbe93e5941750de88b31d104e76e33f7a24126d0acfd06170205872c80a098b4c66f2cd0c1927502496f526ef5a21c4a68e87d5f

(this one has been modified for privacy, but that’s the general format). There is currently no way to access this directly in a formula. But a formula that gives access to the image could be another workaround.

Output with Hacky solution:

It looks ok! You just have to be careful to resize your images to the proper size before using them (which is annoying but workable).

See this thread to follow this issue:

Images embedded inside text in Text Columns, is there an easy solution?

So after trying various approaches, so far I don’t see a way to have an image sit inside text inside a Text Column. The ironic part is that you can have a single Image in a Text Column, and you can combine Rich Text with an Image column using Concatenate to produce an inlined image. I’ve noted these approaches and others that failed in this document: Example: Related threads: Rich text column type Add a new format column: "the Canvas" Notes in a task

---

Use Table.FormattedTextColumn.FormulaMap(ToText(CurrentValue)) to remove formatting from the contents of columns. Don’t use Table.FormattedTextColumn.ToText().Split(',') as it will add unsightly random spaces.

Example to come

---

The Image function formatting parameters (like width, height, and style) don’t work at all with the image urls I’m providing to the function. Not sure why this is the case.

They do work outside of Concatenate formulas:

But once you include them in a Concatenate formula it breaks everything but the style formatting (i.e. it gets too big):

Same is true for images passed to the Format formula.

Embed, on the other hand, does seem to work both inside Concatenate and inside FormulaMap loops. One small problem is that it is very slow to load.

One big problem is that it seems it’s impossible to Concatenate an Embed with other text. If I could do this, then I could get this thing working, but it doesn’t. I’ll keep looking for a way around it.

Never mind, this does work. Concatenate Embeds for the win (they do have too small of sizes, but better than the Image function which was massive). Here’s what they look like:

---

There is not a way to easily do the equivalent of a python zip() function, which makes CSVs very hard to generate. A toCSV would also work.

Got around it with:

Is it possible to merge lists?

Is it possible in Coda to “merge” the items of two lists? In the below example, I want to merge the blue column with the yellow column to get the (hard coded) result in the green column. Is this possible in Coda? (I’ve tried FormulaMap(CurrentValue) every way I can think to try it, but unable to get result in green column.) [merge_lists_2018-11-02_1005]

---

Printing my page (to save to pdf) seems to be impossible:

It used to work, so I’ll have to debug what exactly triggered this encoding error.

---

All told, all of these issues can be overcome for a decent outcome. I have some remaining complaints about how pretty the output is, but those will have to come in an updated/revised post.

[Connor_McCormick]

The grey box around formula generated text is also quite ugly:

[MrChrisRodriguez]

Yeah - has anyone figured out how to get rid of that gray background?

[Connor_McCormick]

Yeah, you can get rid of it for one session (i.e. you can’t reload)

If we start with this example formula:

1. Entering Developer Mode (CMD+OPT+I)
   

   

2. Selecting the selector tool in the top left of your developer console
   

3. Clicking on the edge of a formula (the green part that appears when hightlighting)
   

4. Expanding the section called “Styles”
   

   

5. Unchecking the box next to background-color
   

   

Should now be white:

As I mentioned, this will reset when you reload the page.

[MrChrisRodriguez]

Thanks for sharing! I think there may be a long-term (post-refresh) way to solve this and will update you if I figure this out.

[MrChrisRodriguez]

Connor_McCormick it looks like if you have styled text that you’re referencing (i.e. includes bulleted list, checkboxes, etc.) and you add .join("") to the end so that it’s text instead of a text array, that the gray background goes away.

This doesn’t work for every text string, but helps somewhat.

[Connor_McCormick]

That’s a great tip! I would recommend adding it to a Tips and Tricks post because I’m sure many other people will find it helpful! Don’t forget to link this thread to that post when you do so that if people find this thread they also find that one!