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:
Input information about them into tables
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
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:
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!
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.
@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…
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.
@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.
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!
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:
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.
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:
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.
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:
(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.
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:
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.
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:
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.
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!