How to batch update to prevent Read / Write quota limitations ?

[RageshAntony]

I am developing an app to translate Strings file (that used to localisation in apps)

First I will read the XML and creates some arrays like this with GOOGLETRANSLATE formula

[hello_word,"Hello World",=GOOGLETRANSLATE("Hello World","en","es"),=GOOGLETRANSLATE("Hello World","en","fr"),=GOOGLETRANSLATE("Hello World","en","ar")]

[good_morning,"Good morning ",=GOOGLETRANSLATE("Good morning ","en","es"),=GOOGLETRANSLATE("Good morning ","en","fr"),=GOOGLETRANSLATE("Good morning ","en","ar")]

// 1000 keys ....

Now I am writting these values with Row Key like this

        final langCodes= ["es","fr",ar"];

          await sheetFlutter?.values.insertRowByKey(
            string.Key,
            [
              string.Value,
              ...transLangsSheetFormula(langCodes, string.Value)
            ],
          );

          List<String> transLangsSheetFormula(List<String> langCodes, String value) {
            final formulas = <String>[];
            langCodes.forEach((element) {
              formulas.add(trans(element, value));
            });
            return formulas;
          }

          String trans(String langCode, String value) {
            return '=GOOGLETRANSLATE("$value","en","$langCode")';
          }

Output

Now my problem is , the Strings count are nearly 1000

So my Write and Read quota got expired . So I tried putting a Future.Delayed , but that makes finishing time very longer

So I raised a Quota request , but they replied to use batchUpdate first

Then I searched the library and found batchUpdate function , but its like this

  static Future<http.Response> batchUpdate(
    AutoRefreshingAuthClient client,
    String spreadsheetId,
    List<Map<String, dynamic>> requests,
  )

I don't know how to use this because the function is a static function not like this

 final gsheets = GSheets(Constants.gSheetCredits);
 final ss = await gsheets.spreadsheet(Constants.spreadsheetId);
 final sheetFlutter = await ss.worksheetByTitle('flutter_strings');

In the above , I have an direct instance of a sheet 'flutter_strings' with AUTH completed

But the batchUpdate method don't have any sheet names only spreadsheetId and also need AutoRefreshingAuthClient

1. how to configure this AutoRefreshingAuthClient
2. how to send values ? what is the format of the List ?
3. how to select particular sheet name ?

There is no docs about this in the medium article !

Please guide me how to batch update my arrays like the example above

Note I need batch update with following constraints

1. Sheet Name : must write in the exact sheet I provided
2. with row key : each row must appended with key I provided [same like sheetFlutter?.values.insertRowByKey(keynote..)

[RageshAntony]

I tried like this :

void batch() async {
    print ("running batvh");
  var data = [
    {
      "data": [
        {
          "range": "Sheet1!C1:E1",
          "values": [
            [
              "C1A",
              "D1D",
              "E1F"
            ]
          ]
        }
      ],
      "valueInputOption": "USER_ENTERED"
    }];
  //https://docs.google.com/spreadsheets/d/1ZZRwTuMWnJZ8HpanGGCyhsWQ_Kw_gUaFg3BQjPzZGXg/edit?usp=sharing
    await GSheets.batchUpdate(await gsheets.client, "1ZZRwTuMWnJZ8HpanGGCyhsWQ_Kw_gUaFg3BQjPzZGXg", data);
}

getting Error:

[ERROR:flutter/lib/ui/ui_dart_state.cc(209)] Unhandled Exception: GSheetsException: Invalid JSON payload received. Unknown name "data" at 'requests[0]': Cannot find field.
Invalid JSON payload received. Unknown name "valueInputOption" at 'requests[0]': Cannot find field.
#0      checkResponse (package:gsheets/src/utils.dart:53:5)
#1      GSheets.batchUpdate (package:gsheets/src/gsheets.dart:306:5)
<asynchronous suspension>
#2      GSheetsAndroidRepo.batch (package:strings_file_translator/app/data/repo/gsheets_android_repo.dart:430:5)
<asynchronous suspension>

But working in Google Sheets API console link

{
  "spreadsheetId": "1ZZRwTuMWnJZ8HpanGGCyhsWQ_Kw_gUaFg3BQjPzZGXg",
  "totalUpdatedRows": 1,
  "totalUpdatedColumns": 3,
  "totalUpdatedCells": 3,
  "totalUpdatedSheets": 1,
  "responses": [
    {
      "spreadsheetId": "1ZZRwTuMWnJZ8HpanGGCyhsWQ_Kw_gUaFg3BQjPzZGXg",
      "updatedRange": "Sheet1!C1:E1",
      "updatedRows": 1,
      "updatedColumns": 3,
      "updatedCells": 3
    }
  ]
}

[KennyTC]

@RageshAntony do you know how to batch update? I have the same problems as yours.

[jeff9315]

An example to get around the quota would be WONDERUL!!