Excel Worksheet Templates for Email Attachments and SaveFile

We have enhanced workflow to let you use Microsoft Excel templates to create Excel workbook .xlsx files. The resulting Excel .xlsx file can be included as an email attachment or archived using the SaveFile workflow action.

For more details see https://help.appsheet.com/en/articles/3404478-microsoft-excel-templates

The Microsoft Excel template always produces an .xlsx file. You cannot use an Excel template to create a PDF file.

Because the feature is very new you may encounter problems when using it. Please feel free to report them.

5 Likes

Very interesting @Phil! Any thoughts to allow creation a google sheets as well? That way you are sending a sheet link instead of physical files? (Yes… would need to create a label/folder structure to support). Just wondering.

1 Like

Dear Phil,

First of all, thank you for all the hard works to make this happen!

I tested and working fine in general. One observation I found could be valuable to share with you and community here is ‘marge cells’ on excel template not working on the final output.
For example, to generate the ‘list’ (Table) using list/select expressions on the template body, the single line of the row of the table contain the expression. To design such table, user may want to get the broader width, so they may ‘marge’ adjacent cells to combine together.

This end up with output looking like this.

Very first row maintains the marge as designed on the template, but the subsequent rows does not.

So conclusion and lesson learn from here should be 'Don’t marge adjacent cells within list/table expression on the template’, as it will not generate the expected results while the workflow itself is run-able.

Secondly, the deal with ‘image’ type of columns.

I (assume many of other users as well) may need to show the image on the excel output rather than displaying URL path for the image type column.

Excel does not have native function or capability like GoogleSheet to display actual image being retrieved from the URL to the image. (Google does have image() expression to show URL image) To achieve that, we may need to write Excel VBA which is not perfectly ideal solution for non-coder.

To get the similar result, ie. to get the output of workflow in either Excel or Google Sheet form, I run following workflow which does not involve any coding apart from involving Zapier.

First to generate new workflow using excel template. Apply action to send the output excel file to Gmail. For the excel expression, add ‘function’ which does not work on excel, but works on GoogleSheet.

Expression to be constructed like this. See the document for this topics.

=IMAGE(CONCATENATE(“https://www.appsheet.com/template/gettablefileurl?appName=”, ENCODEURL(" AppName-Account# “), “&tableName=”, ENCODEURL” TableName "), “&fileName=”, ENCODEURL(+ ImageColumnCell )))

This is sample expression

=IMAGE(CONCATENATE(“https://www.appsheet.com/template/gettablefileurl?appName=”, ENCODEURL(“Nicom_MOL_CoilPorter_App-539559”), “&tableName=”, ENCODEURL(“vanning report”), “&fileName=”, ENCODEURL(+A15)))

In this sample, the ‘A15’ cell contains the relative path for image which is generated by the workflow. Within this expression, I anchored to refer to this cell dynamically.

Next. Set up new ZAP, first event to trigger the flow. New email to gmail with the attachment. Then set the action to save the attachment to Google drive once gmail account receive new email.

The last action is to send email with body including the shared link to the saved file (excel file saved in google drive)

When the appsheet action is triggered, the email is send with attachment to gmail account. Once gmail receive this, it will trigger the ZAP and send email back to this named users. This email contain the body with the link to saved file.

By clicking the hyperlink, user will be prompted to new browser tab and show the google sheet. Actually we are seeing the excel workflow output, but in the form of googlesheet. The image files are displayed within the cell as demanded.

See the screenshot to see how the final output looks like.

User can keep working on this googlesheet (excel file opened as google spreadsheet) to working further (edit the format etc.)

I hope Appsheet would make this story far simpler, by introducing new features to display the image on the excel template in a native way.

3 Likes

Hi Tsuji,

I’m glad this feature is helpful for others, thanks for sharing your user experience .

Marge problem:

At this time Appsheet workflow can’t do any structural change on an Excel template, template must be designed at first. [No dynamic structure]

I ran into the same constraint, I assume your excel template is organized by section:

  1. A table like text structure [ name, phone, id, address, date etc…]

  2. Then the image section at the end

  • To solve it just merged the two desired columns from row 15 to 17. [merged more if you don’t have a fixed image number]
  • Conditional cell formatting works well just use a <>"" to format the cell when an image is present with the desired border and alignment.

If you have another section after your image and you don’t have a fixed image number.

  • I use multiple versions of the same template to make it fit the exported data.
    just use the IF THIS IS TRUE… condition in the workflow to choose the right one by counting image number.

I made most of the formatting and design template through google excel interface, for me its more convenient too use.

For the missing image feature, I just do the same as you. [Google spreadsheet solution is more reliable than VBA]

I’m sure AppSheet will add it in the future.

2 Likes

Hey Jeremy,

thank your feedback and sharing your experience, knowledge as well.

Yes, I also prefer Googlesheet rather than Excel, as it is more flexible in terms of available services and extensions.

In the meantime, can you elaborate a bit more how you construct the syntax for

==
I use multiple versions of the same template to make it fit the exported data.
just use the IF THIS IS TRUE… condition in the workflow to choose the right one by counting image number.

==

I wish to fully understand how your trick works to help my workflows.

In the meantime, I found an interesting thing.

I put the tables on the same row. Meaning I put the two separate select/list expression on the same row. On the google doc as template, it returned error, but on excel, it worked out and generate the output based on that.

Till today, we could generate kinda of single column table, but probably we would be able to make ‘multiple column’ type of table with excel as a template. I will keep testing further.

Temlate looks like this.

image

Then output file looks so.

image

This is quite interesting to see what sort of stuffs we are able to create!

1 Like

Hi,

Sorry for the late reply, i got really busy.

For the “If this is true” let me explain you, maybe you got over hyped by my comment [ English isn’t my first language]

I duplicate “n” time the desired Workflow to be “dynamic” [ n = number of variation you need ]

Quick example an Excel template with either 2, 3 or 4 images

image

What i do with the “if this true is” :

1st worklfow with the desired 2 image design

AND([STATUT] = OK, ISNOTBLANK([Image2]))

2nd worklfow with the desired 3 image design

AND([STATUT] = OK, ISNOTBLANK([Image3]))

3rd worklfow with the desired 4 image design

AND([STATUT] = OK, ISNOTBLANK([Image4]))

Not rely reliable if you have too much design variation, but in most case it will do the job.

It would be so nice in the future if the workflow feature evolve in a way you can set the Trigger expression and then expressions for selecting the right template file.

image

Yes, ofc you can put as many as you want on a single row. [ with some tricks you can even export your whole app table source !!! .csv has some limitations]

You can also put a double <<[column1]>><<[column2]>> or use <<CONCATENATE([column1]
,[column2])>> in a single template cell. [ you can also make an “alt+enter” type of cell paragraph formatting]

Sadly, we can’t make the worksheet named by an <<[ ]>>

AppSheet has made some progress url image are now exported directly. [ but for some an obscure reason the image don’t show with a =IMAGE() ]

I can’t agree more, it’s quite interesting to see how easy and fast this is to put any idea or concept in production with AppSheet. [No-Code tools in general]

Best Regards,

Hello,

is it possible to display a picture in an Excel attachment without using Zapier?

Thank you.

There are bunch of web services to convert files from A to B available, but I found this is useful and easy to handle. Furthermore, it looks completely free service.

My clients are saying to get the workflow output in the excel format, which is achievable with the latest introduction from Appsheet, Excel as template.

If we do have existing template either out of Excel of Google doc and get the final output file as PDF, still we will have option to covert those PDF file to others in the relatively easy way by using such a services.

I’ve tested various services, but most of them is not perfectly meet the requirement, as the converted excel files are collapsed rather than maintaining the original forms and layout.

Try this one.

https://altoconvertpdftoexcel.com

As far as the quick test is concerned, it convert PDF generated by Appsheet workflow onto the excel file while maintaining the layout as it is. One negative thing is they do not maintain hyperlink which was put on the original PDF.

If anyone knows the better service, welcome to hear your voice.

1 Like

You could try https://en.pdf24.org/
This is my favorit tool for PDF conversion.

God aften, Thank you Fabian,
Is it a kind of desktop took and application?

The tool I introduced is web based.

I really want to find actually is API solutions to convert PDF file to Excel. For instance we generate PDF file out of workflow under Appsheet, then it trigger the actions to generate excel file out of it and send to appsheet users. The app users of mine will be able to get both PDF and Excel by simple triggering Appsheet action/workflow. This is on one of my wish list.

I know the Desktop Tool, but they seem to have also a free API:

And a web based interface:

hi, all, a bit of help, please.
in the same Workflow
the 1st paste below works well in a Body Template doc,
but the second paste belowq is in the Excel (xlsx) Template does not,
i’m sure the commands are exactly the same
any ideas??

INVOICING REPORT

<<Start: Select(HEADERS[Docket #], TRUE)>>

Job #: <<[Job #]>> Client: <<[Client]>> Project Name: <<[Project Name]>> Total Hours: <>

Surveyor Start Date Code Docket Number Rate Hours Start Time Finish Time
<<Start: [DOCKET LINES]>><<[Surveyor]>> <<[Day Start Date]>> <<[Code]>> <<[_THISROW-1].[FRIENDLY DOCKET #]>> <<[Decimal Hours]>> <> <<[Finish Time Only]>><>

<>

=======================================================

<<Start: Select(HEADERS[Docket #], TRUE)>><<[Job #]>> - <> - <>
Date Code Docket Number Rate Hours This Claim
Surveyor: <>
<<Start: [DOCKET LINES]>><<[Day Start Date]>> <<[Code]>> <<[_THISROW-1].[FRIENDLY DOCKET #]>> <<[Decimal Hours]>> <>
Subtotal <>
GST amount
Total Due
<>

i don’t know why, it was all there when i pasted while writing but upon send there’s commands missing.
screenshots follow, same order: good doc email body template first, then bad xlsx email attachment template…


thanks in advance!

hi again. i saw a post saying to not omit the square brackets. that did not help me. further, my body email template doc still works with come sqare brackets missing. i DO get the below error in the LOG, but i can’t make sense of it.

Error:
“System.NullReferenceException: Object reference not set to an instance of an object.\r\n at Nirvana.Data.ParseTemplateExcel.Parse(Context context, String templateText, AppErrors appErrors) in d:\a\1\s\V2API\Workflow\Template\TemplateExcel\ParseTemplateExcel.cs:line 89\r\n at Nirvana.Data.WorkflowTemplate.ParseTemplate(Context context) in d:\a\1\s\V2API\Workflow\Template\WorkflowTemplate\WorkflowTemplate.cs:line 614\r\n at Nirvana.Data.WorkflowTemplate.Replace(Context context, Int32 columnSliceMapping, Change change, UpdateModeEnum updateMode, EmailExpressions emailExpressions, AppErrors appErrors) in d:\a\1\s\V2API\Workflow\Template\WorkflowTemplate\WorkflowTemplate.cs:line 653\r\n at Nirvana.Data.WorkflowActionEmail.GetAttachment(Context context, String tableName, Int32 columnSliceMapping, Change change, UpdateModeEnum updateMode, EmailExpressions emailExpressions, AppErrors appErrors) in d:\a\1\s\V2API\Workflow\Action\WorkflowActionEmail.cs:line 226\r\n at Nirvana.Data.WorkflowActionEmail.Create(Context context, String tableName, Int32 columnSliceMapping, Change change, UpdateModeEnum updateMode, ActionDefinition actionDefinition, WorkflowLogEvent workflowLogEvent) in d:\a\1\s\V2API\Workflow\Action\WorkflowActionEmail.cs:line 49\r\n at Nirvana.Data.WorkflowRuleEvaluator.CreateActionResults(Context context, WorkflowActionBase workflowAction, AppTemplate appTemplate, String tableName, Int32 columnSliceMapping, Change change, UpdateModeEnum updateMode, WorkflowLogEvent workflowLogEvent) in d:\a\1\s\V2API\Workflow\Evaluators\WorkflowRuleEvaluator.cs:line 138\r\n at Nirvana.Data.WorkflowRuleEvaluator.PerformActions(Context context, AppTemplate appTemplate, String tableName, Int32 columnSliceMapping, Change change, UpdateModeEnum updateMode, WorkflowLogEvent workflowLogEvent) in d:\a\1\s\V2API\Workflow\Evaluators\WorkflowRuleEvaluator.cs:line 367\r\n at Nirvana.Data.WorkflowRuleEvaluator.ProcessRule(Context context, String tableName, Int32 columnSliceMapping, Change change, UpdateModeEnum updateMode) in d:\a\1\s\V2API\Workflow\Evaluators\WorkflowRuleEvaluator.cs:line 504”

Please contact support@appsheet.com directly for help with this.

thanks, @Steve