1

I'm trying send some data from firebase to google sheets. I used the method push() to insert the data into a variable and call it in "resource {values: duplicitiesWithJudicialCharges}".

I know that have more than one value, but in my google sheet it's apearing just one line.

From what I've observed, the last value erases the previous one and sticks to the first line. I would like all the values in the rows to appear in sequence.

    const resultduplicitiesWithJudicialCharges = firestore.collection("Processos judiciais").where("documentosDosautores", "==", cpf)
    const duplicitiesWithJudicialCharges = new Array()

    resultduplicitiesWithJudicialCharges.get().then((querySnapshot) => {
        querySnapshot.forEach((parentDoc) => {
            //functions.logger.log(parentDoc.id, " => ", parentDoc.data())
            parentDoc.ref.collection("fee-arbitrations - Base de Execução").where('arbitramentoDeHonoráriosBE', '==', arbitramentoHonorários).get().then((querySnapshot) => {
                querySnapshot.forEach(async (childDoc) => {
                    //duplicitiesWithJudicialCharges.push(`${'arbitramentoHonorários'}: ${arbitramentoHonorários}`, `${'nome'}: ${nome}`, `${'processoBE'}: ${childDoc.data().processoBE}`)
                    duplicitiesWithJudicialCharges.push([`${arbitramentoHonorários}`, `${nome}`, `${childDoc.data().processoBE}`])
                    //duplicitiesWithJudicialCharges.unshift([`${arbitramentoHonorários}`, `${nome}`, `${childDoc.data().processoBE}`])
                    functions.logger.log(duplicitiesWithJudicialCharges)
                    // let res = [duplicitiesWithJudicialCharges]
                    // functions.logger.log(res)
                    const updateOptions = {
                        auth: jwtClient,
                        spreadsheetId: 'XXXXXXXXXXXXXXXXXXXXXXXXXX',
                        //range: 'grpr!A12',
                        range: '3. Duplicidades judiciais!A2:H1000',
                        valueInputOption: 'USER_ENTERED',
                        resource: { values: duplicitiesWithJudicialCharges },
                    }
                    await google.sheets({ version: 'v4', auth: jwtClient }).spreadsheets.values.clear({
                        range: '3. Duplicidades judiciais!A2:H1000', // SEMPRE QUE FOR QUERER DELETAR, VERIFIQUE A AS LINHAS E COLUNAS QUE POSSUEM VALOR
                        spreadsheetId: 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX',

                        // Request body metadata
                        requestBody: {
                            // request body parameters
                            // {}
                        },
                    });
                    google.sheets({ version: 'v4', auth: jwtClient }).spreadsheets.values.update(updateOptions)
                })
            })
        })

    })

Below I bring an example of data captured by the function that I want to send to the spreadsheet.

enter image description here

This is the result that appears when I send the data to the spreadsheet.

enter image description here

I would like google spreadsheet to show this result:

enter image description here

Does anyone knows where is the problem?

Rodrigo Fiad Pasini
  • 57
  • 6
  • Are you still looking for a solution to your this question? – Tanaike May 29 '22 at 06:00
  • Hi @Tanaike! Yes, I still lookin for. – Rodrigo Fiad Pasini May 30 '22 at 11:46
  • Thank you for replying. I think that the reason for your issue is due to `spreadsheets.values.clear` and `spreadsheets.values.update` are used in the loop. But, your script uses 2 loops. So, in order to correctly understand your expected result, can you provide the sample values of `duplicitiesWithJudicialCharges` in each loop and your expected result as an image? By this, I would like to confirm your question. – Tanaike May 30 '22 at 12:03
  • @Tanaike I updated the question to make it easier to visualize the data. I inserted image of data examples. In reality, the survey brings more than two pieces of data. – Rodrigo Fiad Pasini May 30 '22 at 14:47
  • Thank you for replying. From your additional 2 images, you want to add only 2 rows to the sheet. Is my understanding correct? Unfortunately, from your additional images, I cannot see your expected output situation. – Tanaike May 31 '22 at 00:04
  • @Tanaike I updated this question to show what result I want. – Rodrigo Fiad Pasini May 31 '22 at 13:32
  • Thank you for replying. From your additional information, I proposed a modified script as an answer. Could you please confirm it? Unfortunately, I cannot test this modified script. So, if that was not useful, I apologize. – Tanaike May 31 '22 at 23:49
  • Thank you for replying. I would like to support you. But, I have to apologize for my poor English skill, again. Unfortunately, I cannot still understand your reply. I think that the reason of this is due to that I cannot test the script. So, in this case, I would like to try to test the script. When I could understand your reply, I would like to think of the solution. So, in the current stage, I would like to remove my answer. Because I don't want to confuse other users. This is due to my very poor skill. I deeply apologize for my very poor skill again. – Tanaike Jun 07 '22 at 12:36
  • @Tanaike Considering your previous suggestions, and after studying a lot, I made some changes and found a solution to my question. The answer I posted worked for my case. Thanks for your help. – Rodrigo Fiad Pasini Jun 07 '22 at 19:43

1 Answers1

0

After a lot of studying the ways to send the data and with the help of some people like @Tanaike, I managed to find a solution to my question.

Below is the code that worked for me.

const sheets = google.sheets({ version: "v4", auth: jwtClient })
await sheets.spreadsheets.values.clear({
            range: '2. Duplicidades administrativas!A2:H2000',
            spreadsheetId: '1j9-R6gRj46Lxs0Zlj9LDTv37Hv-hW339Nph6dRI2W9c',
            requestBody: {
            },
        })


        const resultduplicitiesWithAdministrativeCharges = firestore.collection('Protocolos finalizados') //1lGPC1UuxVRplJCVfAXAPWeigjgx1ETyx1OpdtCCJoXo (Pagamentos finalizados)
            .where("autosPF", "==", arbitramentoHonorários) // autosPF => Planilha Pagamentos finalizados  /  arbitramentoHonorários => Planilha de Criação de filtros
            .where("cpf_PF", "==", cpf) // cpf_PF => Planilha Pagamentos finalizados  /  cpf => Planilha de Criação de filtros
        const resDuplicitiesWithAdministrativeCharges = await resultduplicitiesWithAdministrativeCharges.get()
        if (resDuplicitiesWithAdministrativeCharges.size) {
            resDuplicitiesWithAdministrativeCharges.forEach(doc => {
                if (resDuplicitiesWithAdministrativeCharges.size >= 1) {
                    //const duplicitiesWithAdministrativeCharges = [arbitramentoHonorários, nome, doc.data().requerimento, doc.data().mesPagamento]
                    //functions.logger.log(duplicitiesWithAdministrativeCharges)
                    const updateOptions = {
                        auth: jwtClient,
                        spreadsheetId: "1j9-R6gRj46Lxs0Zlj9LDTv37Hv-hW339Nph6dRI2W9c",
                        range: "2. Duplicidades administrativas!A2",
                        valueInputOption: "USER_ENTERED",
                        resource: { values: [[arbitramentoHonorários, nome, doc.data().requerimento, doc.data().mesPagamento]] },
                    };
                    sheets.spreadsheets.values.append(updateOptions)
                }
            })
        }
Rodrigo Fiad Pasini
  • 57
  • 6