如何拆分Google Sheets同一单元格内的值并在Firestore中创建独立文档

阿华AIGC实验室

2026-4-28

Solution: Split Cell Values and Create Individual Firestore Documents

Hey there! The core issue here is that you’re treating the entire arbitramentoDeHonoráriosBE cell content as a single value, but you need to split it into individual entries and create a separate sub-document for each one. Let’s walk through how to fix this:

Step 1: Split the Cell Value

First, you need to split the content of the arbitramentoDeHonoráriosBE cell into an array of individual values. You’ll need to adjust the separator based on how your values are delimited in Google Sheets (common options are commas, line breaks, or semicolons). For this example, I’ll assume values are comma-separated—just swap the separator if yours is different.

Step 2: Handle Asynchronous Operations Correctly

Your original code uses forEach with asynchronous set calls, which can lead to race conditions (the function might finish before all Firestore operations complete). Instead, we’ll collect all the set promises and wait for them to finish with Promise.all.

Modified Code

Here’s the updated version of your function with these changes:

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) => {
  try {
    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`
    })

    // Collect all Firestore operations to run in parallel
    const firestorePromises = [];

    data.values?.forEach(row => {
      const [processoBE, autoresBE, documentosDosautores, arbitramentoDeHonoráriosBE, valorDaCausa] = row
      
      // 1. Create the parent document for the process
      firestorePromises.push(
        firestore.collection("Processos judiciais").doc(processoBE).set({
          processoBE,
          autoresBE,
          documentosDosautores,
          valorDaCausa
        })
      )

      // 2. Split the honorários values and create a sub-document for each
      if (arbitramentoDeHonoráriosBE) {
        // Adjust the separator here (e.g., '\n' for line breaks, ';' for semicolons)
        const honorariosEntries = arbitramentoDeHonoráriosBE
          .split(',')
          .map(entry => entry.trim()) // Remove extra spaces around each value
          .filter(entry => entry); // Skip empty entries (in case of trailing separators)

        honorariosEntries.forEach(entry => {
          firestorePromises.push(
            firestore.collection("Processos judiciais")
              .doc(processoBE)
              .collection('Arbitramento de Honorários - Base de Execução')
              .doc(entry) // Use the individual entry as the document ID
              .set({
                arbitramentoDeHonoráriosBE: entry,
                processoBE
              })
          )
        })
      }
    })

    // Wait for all Firestore operations to complete
    await Promise.all(firestorePromises)
    
    response.status(200).send('Processed successfully!')
  } catch (error) {
    console.error('Error processing data:', error)
    response.status(500).send('Error processing data')
  }
})

Key Changes Explained:

Error Handling: Wrapped the entire logic in a try/catch block to properly handle and report errors.
Value Splitting: Used split(',') to break the cell content into an array, then cleaned up each entry with trim() and filtered out empty values.
Asynchronous Control: Collected all Firestore set operations into an array and used Promise.all to wait for them all to finish, ensuring no operations are dropped.
Flexibility: The separator is easy to adjust—just replace ',' with your actual delimiter (like '\n' if values are on separate lines in the cell).