insertAll method

Future<List<int>> insertAll(List<Map<String, dynamic>> dataList)

Insere múltiplos registros com auto-incremento de ID

Implementation

Future<List<int>> insertAll(List<Map<String, dynamic>> dataList) async {
  if (dataList.isEmpty) return [];

  final rows = await getRawRows(forceRefresh: true);
  if (rows.isEmpty) throw Exception("Cabeçalhos não encontrados.");

  final headers = List<String>.from(rows[0]);
  int idColIndex = headers.indexOf("id");
  if (idColIndex == -1) throw Exception("Coluna 'id' não encontrada.");

  int maxId = 0;
  int lastPopulatedRow = 1; // 1 is header
  List<int> emptyRows = [];

  for (var i = 1; i < rows.length; i++) {
    bool isEmptyId = true;
    if (rows[i].length > idColIndex) {
      var val = rows[i][idColIndex];
      var idStr = val == null ? "" : val.toString().trim();
      if (idStr.isNotEmpty && idStr != "null") {
        isEmptyId = false;
        lastPopulatedRow = i + 1;

        var currentId =
            double.tryParse(idStr)?.toInt() ?? int.tryParse(idStr);
        if (currentId != null && currentId > maxId) {
          maxId = currentId;
        }
      }
    }
    if (isEmptyId) {
      emptyRows.add(i + 1);
    }
  }

  List<List<Object?>> newRows = [];
  List<int> newIds = [];
  int nextId = maxId + 1;

  for (var data in dataList) {
    final newRow = headers.map((h) {
      if (h == "id") {
        return nextId;
      }
      return data[h];
    }).toList();

    newRows.add(List.from(newRow));
    newIds.add(nextId);
    nextId++;
  }

  List<sheets.ValueRange> updateData = [];
  int nextAppendRow = rows.length + 1;

  for (int r = 0; r < newRows.length; r++) {
    int currentRow;
    if (emptyRows.isNotEmpty) {
      currentRow = emptyRows.removeAt(0);
    } else {
      currentRow = nextAppendRow;
      nextAppendRow++;
    }

    for (int c = 0; c < headers.length; c++) {
      var val = newRows[r][c];
      if (val != null && val.toString().isNotEmpty) {
        String colLetter = listAlfabetic(c);
        updateData.add(
          sheets.ValueRange(
            range: '$sheetName!$colLetter$currentRow',
            values: [
              [val],
            ],
          ),
        );
      }
    }
  }

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

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

  _cachedRawRows = null; // Invalida o cache
  return newIds;
}