javascriptfirebasegoogle-cloud-functionsgoogle-cloud-storageexceljs
Erro uploading Excel to Google Cloud Storage
I'm using the 'exceljs' lib. It's work great on my local node server. Now I'm trying to use Firebase Functions to upload the excel file to Google Cloud storage.
This is the entire code I'm using:
'use strict';
const functions = require('firebase-functions');
const admin = require('firebase-admin');
const ExcelJS = require('exceljs');
admin.initializeApp();
var workbook = new ExcelJS.Workbook();
var worksheet = workbook.addWorksheet('Relatório Consolidado');
function startExcel(){
worksheet.columns = [
{ header: 'Empresa', key: 'empresa', width: 25 },
{ header: 'Data criação', key: 'data_criacao', width: 25 },
{ header: 'Responsável agendamento', key: 'agendador', width: 25 },
{ header: 'Colaborador', key: 'colaborador', width: 25 },
{ header: 'Endereço', key: 'endereco', width: 25 },
{ header: 'CPF', key: 'cpf', width: 25 },
{ header: 'CTPS', key: 'ctps', width: 25 },
{ header: 'Função', key: 'funcao', width: 25 },
{ header: 'Data agendado', key: 'nome_subtipo_produto', width: 25 },
{ header: 'Data atendimento médico', key: 'nome_subtipo_produto', width: 25 },
{ header: 'Data inicio atendimento', key: 'nome_subtipo_produto', width: 25 },
{ header: 'Data inicio exames', key: 'nome_subtipo_produto', width: 25 },
{ header: 'Tipo de exame', key: 'valor_produto', width: 25 },
{ header: 'Exames realizados', key: 'valor_produto', width: 25 },
{ header: 'Status atendimento', key: 'tipoPagamento', width: 25 },
{ header: 'Status exames', key: 'centroCustoStr', width: 25 }
];
}
function salvaExcel(){
return new Promise(function(resolve, reject){
let filename = `/tmp/Relatorio.xlsx`
let bucketName = 'gs://xxx.appspot.com/Relatorios'
const bucket = admin.storage().bucket(bucketName);
workbook.xlsx.writeFile(filename)
.then(() => {
console.log('Excel criado com sucesso! Enviando upload do arquivo: ' + filename)
const metadata = {
contentType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
};
bucket.upload(filename, metadata)
.then(() => {
const theFile = bucket.file(filename);
theFile.getSignedURL(signedUrlOptions)
.then((signedUrl) => {
resolve(signedUrl)
});
})
.catch((error) => {
reject('Erro ao realizar upload: ' + error)
})
})
.catch((error) => {
reject('Erro ao realizar upload: ' + error)
})
})
}
startExcel()
/**********************************
* Relatórios
********************************/
function relatorios(change, context){
return new Promise((resolve, reject) => {
const snapshot = change.after
const data = snapshot.val()
verificaRelatorioAgendamentos(change)
.then(() => {
resolve()
})
.catch((error => {
reject(error)
}))
})
}
function verificaRelatorioAgendamentos(change, context){
return new Promise((resolve, reject) => {
const snapshot = change.after
const data = snapshot.val()
const dataInicial = data.dataInicial
const year = moment(dataInicial).format('YYYY')
const month = moment(dataInicial).format('MM')
const state = 'DF'
let path = "/agendamentos/" + state + "/" + year + "/" + month
const relatorios = admin.database().ref(path).once('value');
return Promise.all([relatorios])
.then(results => {
let valores = results[0]
criaRelatorioAgendamentos(valores)
.then(() => {
resolve()
})
.catch((error => {
reject(error)
}))
})
})
}
function criaRelatorioAgendamentos(results){
return new Promise((resolve, reject) => {
let promises = []
results.forEach(element => {
let promise = new Promise(function(resolveExcel){
let data = element.val()
worksheet.addRow({
id: 1,
empresa: data.agendador.company,
data_criacao: data.dataCriacao,
agendador: data.agendador.nome,
colaborador: data.colaborador.nome,
cpf: data.colaborador.cpf,
ctps: data.colaborador.ctps,
funcao: data.colaborador.funcao,
data_agendado: data.data,
data_atendimento_medico: data.dataAtendimento,
data_inicio_atendimento: data.dataInicio,
data_inicio_exames: data.dataInicioExames,
tipo_exame: data.tipoExame,
exames: data.exames[0].nome,
status_atendimento: data.status,
status_exames: data.statusExames
})
resolveExcel()
})
promises.push(promise)
})
Promise.all(promises)
.then(() => {
salvaExcel()
.then((url) => {
console.log('Salvar URL' + url)
resolve(url)
})
.catch((error => {
reject(error)
}))
})
})
}
exports.relatorios = functions.database.ref('/relatorios/{state}/{year}/{month}/{relatoriosId}')
.onWrite((change, context) => {
return relatorios(change, context)
});
On Functions console, the log show me that the excel file was created successfully. But when uploading, a very strange error pops:
What I'm doing wrong? I apreciate any help.
Thanks!
Solution
The error message you are getting comes from trying to get the signed URL of a non-existant file.
When you call bucket.upload(filename, metadata), you are uploading the file /tmp/Relatorio.xlsx, which creates a file in your bucket called Relatorio.xlsx. On the next line you call bucket.file(filename); which incorrectly associates itself with /tmp/Relatorio.xlsx instead of Relatorio.xlsx.
To fix this, you should use the File object that is resolved from bucket.upload() instead of creating it yourself:
bucket.upload(filename, metadata)
.then((file) => file.getSignedURL())
.then((url) => {
console.log('Salvar URL' + url)
})
Other notes and fixes
Your code also contains a lot of unnecessary new Promise((resolve, reject) => { ... }) calls. This is called the Promise constructor anti-pattern and most of them can be removed by properly chaining the Promises. This blog post is a good crash course on Promises and how to use them properly.
Regarding your function's source code, as the index.js file for functions will contain multiple function definitions, you should not define variables at the top of your index.js file unless they are shared by all of your functions and they are stateless in case a function is called multiple times. This is particularly important when dealing with I/O or memory-intensive resources such as files.
With your current code, if the relatorios function was called twice in a short period, the saved file would contain both the old data from the first call and the new data from the current call leading to both an invalid file and a potential memory leak.
Removing the excessive promise calls and making it so that your exceljs code can be rerun without corrupting any data results in the following index.js file:
'use strict';
const functions = require('firebase-functions');
const admin = require('firebase-admin');
// 'exceljs' is required on-demand in MyExcelSheetHelper
admin.initializeApp();
/* HELPER CLASS */
/**
* A helper class used to create reuseable functions that won't
* conflict with each other
*/
class MyExcelSheetHelper {
constructor() {
const ExcelJS = require('exceljs');
this.workbook = new ExcelJS.Workbook();
this.worksheet = this.workbook.addWorksheet('Relatório Consolidado');
this.worksheet.columns = [
{ header: 'Empresa', key: 'empresa', width: 25 },
{ header: 'Data criação', key: 'data_criacao', width: 25 },
{ header: 'Responsável agendamento', key: 'agendador', width: 25 },
{ header: 'Colaborador', key: 'colaborador', width: 25 },
{ header: 'Endereço', key: 'endereco', width: 25 },
{ header: 'CPF', key: 'cpf', width: 25 },
{ header: 'CTPS', key: 'ctps', width: 25 },
{ header: 'Função', key: 'funcao', width: 25 },
{ header: 'Data agendado', key: 'nome_subtipo_produto', width: 25 },
{ header: 'Data atendimento médico', key: 'nome_subtipo_produto', width: 25 },
{ header: 'Data inicio atendimento', key: 'nome_subtipo_produto', width: 25 },
{ header: 'Data inicio exames', key: 'nome_subtipo_produto', width: 25 },
{ header: 'Tipo de exame', key: 'valor_produto', width: 25 },
{ header: 'Exames realizados', key: 'valor_produto', width: 25 },
{ header: 'Status atendimento', key: 'tipoPagamento', width: 25 },
{ header: 'Status exames', key: 'centroCustoStr', width: 25 }
];
}
/**
* Streams this workbook to Cloud Storage
* @param storageFilepath - the relative path where the file is uploaded to Cloud Storage
* @returns the signed URL for the file
*/
salva(storageFilepath) {
if (!storageFilepath) {
return Promise.reject(new Error('storageFilepath is required'));
}
const bucket = admin.storage().bucket();
const storageFile = bucket.file(storageFilepath);
const uploadFilePromise = new Promise((resolve, reject) => {
try {
const stream = storageFile.createWriteStream({
contentType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
});
stream.on('finish', () => {
resolve();
});
stream.on('error', error => {
reject(error);
});
this.workbook.xlsx.write(stream)
.then(() => {
stream.end();
});
} catch (e) { // catches errors from createWriteStream
reject(e);
}
})
return uploadFilePromise
.then(() => {
var CONFIG = {
action: 'read',
expires: '03-01-2500',
};
bucket.file(storageFilepath).getSignedUrl(CONFIG)
.then((signedUrl) => {
return signedUrl
})
})
}
}
/* FUNCTIONS CODE */
function criaRelatorioAgendamentos(path, querySnapshot) {
const excelFileHelper = new MyExcelSheetHelper();
const worksheet = excelFile.worksheet;
// this forEach loop is synchronous, so no Promises are needed here
querySnapshot.forEach(entrySnapshot => {
const data = entrySnapshot.val();
worksheet.addRow({
id: 1,
empresa: data.agendador.company,
data_criacao: data.dataCriacao,
agendador: data.agendador.nome,
colaborador: data.colaborador.nome,
cpf: data.colaborador.cpf,
ctps: data.colaborador.ctps,
funcao: data.colaborador.funcao,
data_agendado: data.data,
data_atendimento_medico: data.dataAtendimento,
data_inicio_atendimento: data.dataInicio,
data_inicio_exames: data.dataInicioExames,
tipo_exame: data.tipoExame,
exames: data.exames[0].nome,
status_atendimento: data.status,
status_exames: data.statusExames
});
});
return excelFileHelper.salva(path + '/Relatorio.xlsx');
}
exports.relatorios = functions.database.ref('/relatorios/{state}/{year}/{month}/{relatoriosId}')
.onWrite((change, context) => {
// Verificar relatorio agendamentos
const snapshot = change.after;
const data = snapshot.val();
const dataInicial = data.dataInicial;
const year = moment(dataInicial).format('YYYY');
const month = moment(dataInicial).format('MM');
const state = 'DF';
const path = "/agendamentos/" + state + "/" + year + "/" + month;
return admin.database().ref(path).once('value')
.then(valores => {
return criaRelatorioAgendamentos(path, valores);
});
});
- Keep numbers which appear in both columns, in J lang
- Differentiation in J
- How to reshape an array with an arbitrary size in one dimension?
- Why is Insert (fold) right associative
- Write 4 : 'x&{.&.;: y' tacitly
- Alignment issue when printing formatted prime numbers in J language
- How can I define a verb in J that applies a different verb alternately to each atom in a list?
- How to get user input in the J programming language
- How to unbox a list of boxed lists of differing lengths in J?
- How can I fix 'noun result was required' error in J?
- In j, how can I define a verb locally in one scope and pass it to a defined adverb?
- Convert boxed array to normal array?
- Read column of CSV file as array
- Replace atom in array of strings
- What does the dyad `=` do to boxed strings?
- Index of minimum element using J
- How can I take the outer product of string vectors in J?
- Building an array of verbs in J
- Reading in multidigit command line parameter
- Amend with bond to new data shows unexpected behaviour
- How to turn a table or matrix into a (flat) list in J
- How to run dissect in J?
- How to define selection using index function in J
- How to exit the J console?
- Find 4-neighbors using J
- Writing custom verbs in J
- How do I negate a selector in J lang?
- How to use arbitrary selector in interchange in J lang?
- different result once square root is added inside tacit
- Sum of arrays with repeated indices