How I can separate the values that are inside the same cell (google sheets) and create a different d-CodePudding

I'm creating a database in Firestore and the code I did is this:

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()
    
exports.processosjudiciais = functions.https.onRequest(async (request, response) => {
    
    const jwtClient = new google.auth.JWT({
        email: serviceAccount.client_email,
        key: serviceAccount.private_key,
        scopes: ['https://www.googleapis.com/auth/spreadsheets']
    })

    await jwtClient.authorize()

    const { data } = await sheets.spreadsheets.values.get({
        auth: jwtClient,
        spreadsheetId: 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX',
        range: `Processos judiciais!A11664:E11667`
    })

    data.values?.forEach(row => {
        const [processoBE, autoresBE, documentosDosautores, arbitramentoDeHonoráriosBE, valorDaCausa] = row
        firestore.collection("Processos judiciais").doc(processoBE).set({
            processoBE, autoresBE, documentosDosautores, valorDaCausa
        })
        firestore.collection("Processos judiciais").doc(processoBE).collection('Arbitramento de Honorários - Base de Execução').doc(arbitramentoDeHonoráriosBE).set({
            arbitramentoDeHonoráriosBE, processoBE
        })
    })
})

The structure of my code in firestore looks like this:

Instead of having a document that displays multiple values of "ArbitramentoDeHonoráriosBE", I wanted each value of "arbitramentoDeHonoráriosBE" to be a different document, containing both ArbitrationDeHonoráriosBE and "processBE".

I separated an image with colors to facilitate the visualization of the idea.

Does anyone know how I can separate the values that are inside the same cell (google sheets) and create a different document in the firestore?

CodePudding user response：

It seems arbitramentoDeHonoráriosBE is a string and the value are separated by a ;. If you want to create a document for each value then you can try this:

const updatePromises = [];

data.values?.forEach(row => {
  const [arbitramentoDeHonoráriosBE, processoBE] = row

  // Get an array of those values separated by ';'
  arbitramentoDeHonoráriosBE.split(";").forEach(v => {
    // Push the set() promises to updatePromises array
    updatePromises.push(
      firestore.collection("Processos judiciais").doc(processoBE).collection('Arbitramento de Honorários - Base de Execução').doc(arbitramentoDeHonoráriosBE).set({
        arbitramentoDeHonoráriosBE: v,
        processoBE
        // add required field in document
      })
    )
  })
})

// run all promises 
return await Promise.all(updatePromises)

Alternatively you can also use batched writes to write documents.

CodePudding user response：

Using @Dharmaraj's suggestion, I was able to find the answer with this code.

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()

exports.processosjudiciais = functions.https.onRequest(async (request, response): Promise<any> => {

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

    await jwtClient.authorize()

    const { data } = await sheets.spreadsheets.values.get({
        auth: jwtClient,
        spreadsheetId: 'XXXXXXXXXXXXXXXXXXXXXXXXXXXX',
        range: `Processos judiciais!A11664:E11667`
    })
    
    const updatePromises = new Array()
    data.values?.forEach(row => {
        const [processoBE, autoresBE, documentosDosautores, arbitramentoDeHonoráriosBE, valorDaCausa] = row
        firestore.collection("Processos judiciais").doc(processoBE).set({
            processoBE, autoresBE, documentosDosautores, valorDaCausa
        })
        arbitramentoDeHonoráriosBE.split("; ").forEach((v: string) => {
            updatePromises.push(
                firestore.collection("Processos judiciais").doc(processoBE).collection('fee-arbitrations - Base de Execução').doc(v).set({
                    arbitramentoDeHonoráriosBE: v,
                    processoBE,
                })
            )
        })
    })
    return await Promise.all(updatePromises)
})