Batch insert google apps script

Question

Batch insertion in google apps script. I am using push to write data from a table to a sheet. Is there a way to do batch insert 3000 records and loop through via a trigger to execute after a couple of minutes until all the records are inserted in google apps script.

  function myBatchInsert() {
    var connection = Jdbc.getConnection("jdbc:mysql://host:port", "user", "passwrd");
    var query = connection.createStatement();
    var result = query.executeQuery('SELECT * FROM Table WHERE');
    var googlespreadsheet;
    var sheet;
    var googlespreadsheetSheetName = "Table";

    googlespreadsheet = SpreadsheetApp.openById(SpreadsheetApp.getActiveSpreadsheet().getId());

    var datasheet = googlespreadsheet.getSheetByName("Table");
    datasheet.setName("Table");

    SpreadsheetApp.setActiveSpreadsheet(googlespreadsheet);
    sheet = SpreadsheetApp.setActiveSheet(googlespreadsheet.getSheetByName(googlespreadsheetSheetName));

    var columncount = result.getMetaData().getColumnCount();
    var columnName;

    for (var column = 1; column <= columncount; column++) {
      sheet.getRange(1, column).setValue(result.getMetaData().getColumnName(column));
    }

    var document = SpreadsheetApp.getActiveSpreadsheet();
    var cell = document.getRange('A2');
    var row = 0;
    var data = [];

    for (var i = 0; i < 1; i++) {
      while (result.next()) {
        var rowData = [];
        for (var column = 0; column < result.getMetaData().getColumnCount(); column++) {
          rowData.push(result.getString(column + 1));
        }
        data.push(rowData);
      }

      sheet.getRange(sheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);
    }

    result.close();
    query.close();
    connection.close();
  }

Answer 1

You can try reading about Batching (but it is more on adding to database than writing to sheets).

Use batch operations

Scripts commonly need to read in data from a spreadsheet, perform calculations, and then write out the results of the data to a spreadsheet. Google Apps Script already has some built-in optimization, such as using look-ahead caching to retrieve what a script is likely to get and write caching to save what is likely to be set.

You can write scripts to take maximum advantage of the built-in caching, by minimizing the number of reads and writes. Alternating read and write commands is slow. To speed up a script, read all data into an array with one command, perform any operations on the data in the array, and write the data out with one command.

---

There is no batch function regarding adding data to sheets but you can use a function efficiently to optimize your code.

// DO NOT USE THIS CODE. It is an example of SLOW, INEFFICIENT code. // FOR DEMONSTRATION ONLY var cell = sheet.getRange('a1'); for (var y = 0; y < 100; y++) { xcoord = xmin; for (var x = 0; x < 100; x++) { var c = getColor_(xcoord, ycoord); cell.offset(y, x).setBackgroundColor(c); xcoord += xincrement; } ycoord -= yincrement; SpreadsheetApp.flush(); }

The script is inefficient: it loops through 100 rows and 100 columns, writing consecutively to 10,000 cells. The Google Apps Script write-back cache helps, because it forces a write-back using flush at the end of every line. Because of the caching, there are only 100 calls to the Spreadsheet.

But the code can be made much more efficient by batching the calls. Here's a rewrite in which the cell range is read into an array called colors, the color assignment operation is performed on the data in the array, and the values in the array are written out to the spreadsheet:

// OKAY TO USE THIS EXAMPLE or code based on it.
var cell = sheet.getRange('a1');
var colors = new Array(100);
for (var y = 0; y < 100; y++) {
xcoord = xmin;
colors[y] = new Array(100);
for (var x = 0; x < 100; x++) {
colors[y][x] = getColor_(xcoord, ycoord);
xcoord += xincrement;
}
ycoord -= yincrement;
}
sheet.getRange(1, 1, 100, 100).setBackgroundColors(colors);

The inefficient code takes about 70 seconds to run. The efficient code runs in just 1 second!