Create xlsx file, save in tmp and attach it in firebase mail

Question

I've created a Google Cloud Function which, when it is trigged, it creates an xlsx file with exceljs and attaches it in an email sent with firebase-send-mail. This is my code:

(data is dummy for test)

exports.onEventReservCreate = functions
    .region("europe-west2")
    .firestore.document(
        "foodCourts/{foodCourtId}/events/{eventId}/eventReservations/{evtResId}"
    )
    .onCreate(async (snap, context) => {

        try {
            const excel = require("exceljs")

            //Creating New Workbook
            var workbook = new excel.Workbook()

            //Creating Sheet for that particular WorkBook
            var sheet = workbook.addWorksheet("Sheet1")

            // set path for file
            const tempFilePath = path.join(os.tmpdir(), "excel.xlsx")


            sheet.columns = [
                { key: "name", header: "name" },
                { key: "age", header: "age" },
            ]


            var data = [
                { name: "Eddy", age: 24 },
                { name: "Paul", age: 24 },
            ]

            //adding each in sheet
            data.forEach(el => sheet.addRow(el))

            // get the user email from firestore db
            const userRef = db.collection(`users`).doc(uid)
            const user = (await userRef.get()).data()

            workbook.xlsx
                .writeFile(tempFilePath)
                .then(res => {

                    // sending email to user
                    const emailData = {
                        to: [user.email],
                        template: {
                            name: "reportEvents",
                            data: {
                                attachmentPath: tempFilePath,
                                attachmentName: "nome allegato",
                                date: dateFnsTz.format(
                                    new Date(),
                                    "dd/MM/yyyy - HH:mm"
                                ),
                            },
                        },
                    }

                    return db.collection("email").doc().set(emailData)
                })
                .catch(err => console.log("ERROR --> ", err))
        } catch (err) {
            console.log(
                `Error while sending - Error: ${err}`
            )
        }
    })

In functions log i have this error:

Error when delivering message=email/[id]: Error: ENOENT: no such file or directory, open '/tmp/excel.xlsx'

Why /tmp folder doesn't exist?

Thanx

Can you show the set of imports/require at the top of your `index.js` file? — Renaud Tarnec, Jun 17 '21 at 15:13
Sure `require("firebase-functions/lib/logger/compat") const functions = require("firebase-functions") const admin = require("firebase-admin") const dateFns = require("date-fns") const dateFnsTz = require("date-fns-tz") const _ = require("lodash") const express = require("express") const cors = require("cors") const fs = require("fs") const path = require("path") const os = require("os")` — ProfLayton, Jun 17 '21 at 15:22
Perhaps log `tempFilePath` to ensure it's `/tmp/excel.xlsx`... it's possible (improbable?) that Cloud Functions runtime is incorrectly responding to `os.tmpdir()` but, since we know this will be Linux, you can probably avoid the lookup and go direct. — DazWilkin, Jun 17 '21 at 15:39
Add other logging through the promise's enumeration to see how far it gets. Why is the `doc()` empty? Do you not need to provide a reference? `db.collection("email").doc().set(emailData)` — DazWilkin, Jun 17 '21 at 15:39
It's probably me but you say this sends an email and the error says "delivering message" but I see no code that suggests anything's actually being sent; persisted to Firestore possibly but not emailed — DazWilkin, Jun 17 '21 at 15:40
@DazWilkin The OP most probably uses the [email extension](https://firebase.google.com/products/extensions/firestore-send-email?authuser=0). Creating a doc in the `email` collection will send the email. Also, `.doc().set()` is equivalent to `add() `. — Renaud Tarnec, Jun 17 '21 at 15:41
@renaud-tarnec got it, thanks! So what's generating the error message? The email extension? — DazWilkin, Jun 17 '21 at 15:42
firebase-send-mail listen the collection "email" and when a document is added it sends email to provided address in document, the problem is in file i've created not in email collection. — ProfLayton, Jun 17 '21 at 15:45
@DazWilkin Actually you most probably caught the problem!! :-) By reading your comment I now realize that the extension does not know anything about the file, since the mail is sent by another Cloud Function (extensions are executed via CF). I'll delete my anwser :-) Please write an answer along those lines (maybe after double checking with ProfLayton that he uses the extension). He should send the mail directly in the Cloud Function, as shown [here](https://github.com/firebase/functions-samples/tree/main/email-confirmation). — Renaud Tarnec, Jun 17 '21 at 15:46
@RenaudTarnec i've read about nodemailer, maybe i should give it a try, i'll update post if i found something useful — ProfLayton, Jun 17 '21 at 15:53

score -1 · Answer 1 · answered Jun 18 '21 at 03:24

The temp folder has limited and restrictive access, I am not sure how your email script is actually reading the file as it may just be a root folder issue or an instance issue. Instead, I would upload the file to storage with a unique download link and send that within your email.

score -1 · Answer 2 · answered Jun 28 '21 at 15:19

SOLVED!

This is my code

it will require some packages like node-fs, os, path.

This code will create xslx file, storage it in your bucket, retrieve its url and add in "email" collection a new document that have "url" property that is the url of the xslx file in bucket, so user that will receive the mail can download it.

const admin = require("firebase-admin")
const fs = require("fs")
const path = require("path")
const os = require("os")
const excel = require("exceljs")

const batch = db.batch()

const bucket = admin.storage().bucket("your_bucket's_name")

//Creating New Workbook
var workbook = new excel.Workbook()

//Creating Sheet for that particular WorkBook
var sheet = workbook.addWorksheet("Sheet1")

//Header must be in below format
sheet.columns = [
   { key: "name", header: "name", width: 30 },
   ...other columns
]


//Data must be look like below, key of data must be match to header.
sheet.addRow({name: "John"}))

const tempFilePath = path.join(os.tmpdir(), "excel.xlsx")

await workbook.xlsx.writeFile(tempFilePath)

const destinationName = `[your_filename].xlsx`

const result = await bucket.upload(tempFilePath, {
    destination: destinationName,
})

result[0].getSignedUrl({
    action: "read",
    expires: "03-17-2025", // choose a date
}).then((url, err) => {
    if (err) {
       console.log(err)
    }
    const emailData = {
       to: ["user_email"],
       template: {
          name: "your_template's_name",
          data: {
             url,
          },
    }

    const emailRef = db.collection("email").doc()

    batch.set(emailRef, emailData)

    return batch.commit()
}).catch(err => console.log(err))

Create xlsx file, save in tmp and attach it in firebase mail

2 Answers2