updateForeignKey method

Future<void> updateForeignKey(SheetsApi api, List<ForeignKey>? foreignKeyConfigs)

Atualiza as foreign keys na planilha

Implementation

Future<void> updateForeignKey(
  sheets.SheetsApi api,
  List<ForeignKey>? foreignKeyConfigs,
) async {
  if (foreignKeyConfigs == null || foreignKeyConfigs.isEmpty) return;

  final List<sheets.ValueRange> updateBatch = [];

  for (final config in foreignKeyConfigs) {
    // Nomes claros: origem (source) vs destino/referência (lookup)
    final sourceSheetHeaders = _structure[config.sourceTable];
    final lookupSheetHeaders = _structure[config.lookupTable];

    if (sourceSheetHeaders == null || lookupSheetHeaders == null) {
      throw Exception("Sheet structure not found for: ${config.sourceTable}");
    }

    // Identificando índices (Column Index)
    final colIdxTrigger = sourceSheetHeaders.indexOf(config.sourceKeyColumn);
    final colIdxLookupKey = lookupSheetHeaders.indexOf(
      config.lookupKeyColumn,
    );
    final colIdxLookupValue = lookupSheetHeaders.indexOf(
      config.lookupResultColumn,
    );
    final colIdxTarget = sourceSheetHeaders.indexOf(
      config.sourceTargetColumn,
    );

    if ([
      colIdxTrigger,
      colIdxLookupKey,
      colIdxLookupValue,
      colIdxTarget,
    ].contains(-1)) {
      throw Exception(
        "One or more columns not found in sheet: ${config.sourceTable}",
      );
    }

    final colLetterTrigger = listAlfabetic(colIdxTrigger);
    final colLetterLookupKey = listAlfabetic(colIdxLookupKey);
    final colLetterLookupValue = listAlfabetic(colIdxLookupValue);
    final colLetterTarget = listAlfabetic(colIdxTarget);

    final xLookupFormula =
        "=ARRAYFORMULA(SE(${colLetterTrigger}2:$colLetterTrigger =\"\"; \"\"; PROCX(${colLetterTrigger}2:$colLetterTrigger; ${config.lookupTable}!$colLetterLookupKey:$colLetterLookupKey; ${config.lookupTable}!$colLetterLookupValue:$colLetterLookupValue; \"not found\")))";

    final targetCellRange = '${config.sourceTable}!${colLetterTarget}2';

    updateBatch.add(
      sheets.ValueRange(
        range: targetCellRange,
        values: [
          [xLookupFormula],
        ],
      ),
    );
  }

  if (updateBatch.isNotEmpty) {
    final batchRequest = sheets.BatchUpdateValuesRequest(
      data: updateBatch,
      valueInputOption: "USER_ENTERED",
    );

    await api.spreadsheets.values.batchUpdate(batchRequest, spreadsheetId!);
  }
}