In Excel/PDF output, an Excel file format template is used as a base, matches Kintone record data, and creates an Excel or PDF file.
Please prepare the following items in advance:
You can create template files freely in Excel format.
Please also see the starter kit ,which includes sample apps and templates you can try immediately.
The sheet name of the template file can be any name you choose.
You can use the gusuku commands to utilize the field value as the sheet name. The available commands are as follows:
| $(Field code) | Output the specified field code. |
You can connect multiple commands and describe them with fixed strings.
By adding a “#” at the beginning of the sheet name, you can output multiple sheets corresponding to the number of records from the list view.
| Any sheet name | Add sheets for the number of selected records. |
When outputting sheets with a “#” in the sheet name for the number of records, please note the following points:
Most gusuku commands are used with the field codes. Field codes can be written as they are in both Japanese and English.
Note
You can connect multiple commands in one cell and describe them with fixed strings.
| gusuku Command | Description |
|---|---|
| $(field code) | Output the specified field code. |
| $STR(field code) | The specified field code will be treated as a string and output. |
| $NUM(field code) | The specified field code will be treated as a number and output. |
| $DATE(field code) | The specified field code will be treated as a date and output. By default, the JST time zone is used. When specifying a time zone, you can specify $DATE, UTC(field code) and the time zone name, or specify with the field code $DATE, GMT+1:00 and the offset. |
Metadata fields are available on any sheet.
| Metadata Field | Description |
|---|---|
| $($gusuku.meta.loginuser.code) | Output the login name of the Kintone user who generated the report (not supported in the Job Runner version). |
| $($gusuku.meta.loginuser.name) | Output the display name of the Kintone user who generated the report (not supported in the Job Runner version). |
| $($gusuku.meta.loginuser.email) | Output the email address of the Kintone user who generated the report (not supported in the Job Runner version). |
| $DATE($gusuku.meta.timestamp) | Output the date and time when the report was generated. Please change to your preferred date format in the cell formatting. |
$IF only works at the beginning of a cell. Therefore, it cannot be specified in the form of “$(a)$IF(…)” by combining it with other gusuku commands.
| gusuku Command | Description |
|---|---|
| $IF, true label, false label (field code) | If the value specified by the field code is the string "true", it will be replaced with the string "True Label"; otherwise, it will be replaced with the string "False Label". It is useful when you want to output true/false in a different notation. |
| $IF(field code A) Billing Available $DATE(Date) No Billing | If the string specified in field code A is "Billing Available", the date from the date field will be output; otherwise, "No Billing" will be output. |
| $IF(field code A)$("Billing Available")$("There is a bill")$("There is no bill") | If the string specified in field code A is "Billing Available", it will output "There is a billing"; otherwise, it will output "There is no billing." t is similar to the example above, but if you want to fix two or more of the "condition value", "value when matched", and "value when not matched" as a continuous string, you need to use the format $("xx xx"). |
| gusuku Command | Description |
|---|---|
| $FOREACH and $END | Loop from the line where $FOREACH is written to the line where $END is written, for the number of selected records. In the loop, you can write the gusuku command specifying the field code of each record. |
| gusuku Command | Description |
|---|---|
| $FOREACH(Field Code) and $END |
Loop from the line where $FOREACH is written to the line where $END is written. Field codes can specify the field codes for "Table", "Related Records", "Attachment", and "Text area". In the loop, you can specify the gusuku command that references the field codes of the app in the table or related records, as well as the $ or $IMG mentioned later. |
| $IMG | If you specify the field code for the attachment with $FOREACH, it can be written inside the loop. If an image in PNG, JPEG, GIF, or BMP format is attached, it will be output in that column (it will not be enlarged beyond the original size of the image). |
| $EXTIMG | If you specify the field code for the attachment with $FOREACH, it can be written inside the loop. Similar to the $IMG command, this one outputs an image but enlarges it to fit the column, exceeding the original size of the image. Depending on the size of the attached image, the output may result in a very rough image, so please be careful. |
| $(contentType) $(fileKey) $(name) $(size) |
If you specify the field code for the attachment with $FOREACH, it can be written inside the loop. It can output each piece of information from the attached file. |
| $ | When specifying a field code for a Text area with $FOREACH, it is possible to write it inside the loop. A single line string of specified field codes will be output, separated by line breaks. |
It can extract and output only a part using FOREACH. This function is called a slice.
Ex: Output items from the first through the tenth. Please note that the start is from 0.
$FOREACH(0..9)
「..」The numbers before and after “..” can be omitted; it means “all” when omitted.
「5..」Specifying this means all from line 6 onwards. Specifying “..5” means the first six lines.
You can also slice tables and related records. In this case, specify the field codes of the table and related records first, and then specify the slice range.
$FOREACH(table name)(0..9)
$FOREACH(related records name)(0..9)
For strings that include line breaks, each line can be specified as one element.
Fields such as “Attachment” and “Text area” can also specify numbers directly instead of writing a loop with FOREACH.
By specifying this, it can output the first line of a Text area’s field, separated by line breaks.
When expanding table rows or related records with FOREACH, what is referenced by $(field code) is interpreted as the columns of the table or the fields of the related records. During FOREACH expansion, specifying the parent record’s field with $(field code) will not be output.
The parent record can be referenced by specifying FOREACH, $(..) If you want to refer to the fields of the original record while expanding table rows or related records, you can write it as follows.
$(..)(Parent record field code)
The following elements change their position and range as rows increase or decrease by $FOREACH to $END.
To break lines within a cell, turn on ‘Wrap text’ in the cell settings. When turned off, even if the value of the Text area field contains line breaks, it will not break into a new line within the cell.
Text decorations in the Rich text field are not reflected in the cell.
To output the values of Date fields or Date and time fields in a specific format, please set the desired format in cell formatting and output it with $DATE.
$DATE(departure date)
In the above example, if you omit DATE and write $(departure date), the field value of Kintone will be output as a string, and the cell’s date formatting will not be applied.
Write the code below to output the selected department name using the Department selection field. If you omit “(name),” the code will be output. The same applies to the User selection and the Group selection fields.
$(Department selection's field code)(0)(name)
If there are multiple, please enter the expected quantity into the number.
$(Department selection's field code)(0)(name) $(Department selection's field code))(1)(name)
To output the attached file as an image, please use $IMG. The file name will be output instead of the image when outputting without using $IMG.
Please note that a single attachment field can have multiple files attached. If there is only one attachment, please add “(0)” to indicate “the first attachment.”
$IMG(Attachment)(0)
To expand all files attached to the field in a row direction, use $FOREACH.
$FOREACH(Attachment)
$IMG
$END
When outputting to PDF, there are several limitations, including fonts.
Please check the “Restrictions when outputting PDF” for details.
If there is an array formula in the template, it will be replaced with a regular formula.
There are limitations to the Excel functions that can be used within the template. Please see here for available functions.
The file size of the template is limited to 5MB.
The maximum number of rows to include in the template is 10,000 rows in total across all sheets.
The related records can output up to 500 records. More than 500 records cannot be output.
When adding a “#” to the sheet name and outputting a sheet for each record, the number of cells in the original sheet must be within 10,000 cells.
The maximum number of rows that can be included in a sheet, as a limitation of Microsoft Excel , is 1,048,576 rows (about 1 million rows). When expanding a large number of records exceeding one million lines with $FOREACH, an error occurs due to this limitation.
Comments attached to the cells within $FOREACH to $END will be deleted after expansion.