I'm comparing data from a spreadsheet with a firebase collection. When the data are equal, it brings an answer. How do I throw this answer into another google spreadsheet?

I'm trying this way, but I can't enter the values in the worksheet.

import * as functions from 'firebase-functions'
import { google } from 'googleapis'
import { initializeApp } from 'firebase-admin/app'
const serviceAccount = require('../sheets_updater_service_account.json')
const sheets = google.sheets('v4')
import { getFirestore } from "firebase-admin/firestore"
initializeApp()
const firestore = getFirestore()


module.exports.readAndUpdateAdministrativeSheet = functions.https.onRequest(async (request, response) => {

    // =========================== AUTENTICAÇÃO FIREBASE ===================================
    const jwtClient = new google.auth.JWT({
        email: serviceAccount.client_email,
        key: serviceAccount.private_key,
        scopes: ['https://www.googleapis.com/auth/spreadsheets']
    })

    await jwtClient.authorize()


    // ================= CONEXÃO COM A PLANILHA CRIAÇÃO DE FILTROS =========================
    const { data } = await sheets.spreadsheets.values.get({
        auth: jwtClient,
        spreadsheetId: 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX',
        range: `Listagem de pagamento!A2:X6`,
    })

    // ========= CRIAÇÃO DE BANCO DE DADOS DA COLEÇÃO LISTAGEM DE PAGAMENTO ================
    const generateDuplicities = data.values!.map(async row => {
        const [idade, nome, cpf, cpf_x, numeroRequerimento, arbitramentoHonorários,
            valorArbitrado, valorDeferido, valorComplementar, status, resultado, codigoBanco,
            banco, agencia, conta, dataDoRequerimento, dataRequerimento, dataStatus,
            comarca, vara, ato, assistidos, email, telefone] = row
        firestore.collection("Listagem de pagamento").doc(numeroRequerimento).set({
            idade, nome, cpf, cpf_x, numeroRequerimento, arbitramentoHonorários,
            valorArbitrado, valorDeferido, valorComplementar, status, resultado, codigoBanco,
            banco, agencia, conta, dataDoRequerimento, dataRequerimento, dataStatus, comarca, vara, ato,
            assistidos, email, telefone
        })
        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((childDoc) => {
                        duplicitiesWithJudicialCharges.push(`${'arbitramentoHonorários'}: ${arbitramentoHonorários}`, `${'nome'}: ${nome}`, `${'processoBE'}: ${childDoc.data().processoBE}`)
                        functions.logger.log(duplicitiesWithJudicialCharges)
                        console.log(duplicitiesWithJudicialCharges)
                        const updateOptions = {
                            spreadsheetId: 'YYYYYYYYYYYYYYYYY',
                            range: 'grpr!A12', // Isso responde uma pergunta que fiz no STACKOVERFLOW
                            valueInputOption: 'USER_ENTERED',
                            resource: { values: duplicitiesWithJudicialCharges },
                        }
                        google.sheets({ version: 'v4'}).spreadsheets.values.update(updateOptions)
                    })
                })
            })

        })
    })
    await Promise.all(generateDuplicities)
}) 

Whats I'm doing wrong?

Does anyone know what I'm doing wrong?

CodePudding user response：

In your script, the client requesting Sheets API is not included. I think that when that is included and your value of duplicitiesWithJudicialCharges is a valid value, and your service account has the permission for writing the Spreadsheet, your script works. So, how about the following modification?

From:

google.sheets({ version: 'v4'}).spreadsheets.values.update(updateOptions)

To:

google.sheets({ version: 'v4', auth: jwtClient}).spreadsheets.values.update(updateOptions);

Or, if you want to use const sheets = google.sheets('v4'), how about the following modification?

From:

const updateOptions = {
    spreadsheetId: 'YYYYYYYYYYYYYYYYY',
    range: 'grpr!A12', // Isso responde uma pergunta que fiz no STACKOVERFLOW
    valueInputOption: 'USER_ENTERED',
    resource: { values: duplicitiesWithJudicialCharges },
}

To:

const updateOptions = {
    auth: jwtClient,
    spreadsheetId: 'YYYYYYYYYYYYYYYYY',
    range: 'grpr!A12', // Isso responde uma pergunta que fiz no STACKOVERFLOW
    valueInputOption: 'USER_ENTERED',
    resource: { values: duplicitiesWithJudicialCharges },
}

Note:

• In this modification, it supposes that your jwtClient have the permission for writing to the Spreadsheet of spreadsheetId: 'YYYYYYYYYYYYYYYYY',. Please be careful about this.