More performant insertRows()

[uberl]

Hi!

Inserting rows is really slow (3 Minutes for 640 entries :-/ ). Do you see any way to increase performance of insertRows?
I really like the simplicity of sheetquery thanks for the project ❤️
This is my first post on github, please be gentle. 😇

I am also open to implementing the solution myself, if there is one.

This is my apps script code:

function insertRows(rows)
{
  Logger.log(`Inserting ${rows.length} rows.`)

  console.time('insertRows');
  
  SheetQuery
    .sheetQuery()
    .from("Einsätze")
    .insertRows(rows)


  console.timeEnd('insertRows');

}

These are the logs:

20.03.2024, 13:29:16 Info Inserting 641 rows.
20.03.2024, 13:32:12 Fehlerbehebung insertRows: 175975ms

I am not sure if anything can be improved in sheetquery. The relevant source code is simply a call to appendRow

        sheet.appendRow(rowValues);

Here is the full method from index.ts lines 203+

/**
   * Insert new rows into the spreadsheet
   * Arrays of objects like { Heading: Value }
   *
   * @param {DictObject[]} newRows - Array of row objects to insert
   * @return {SheetQueryBuilder}
   */
  insertRows(newRows: DictObject[]): SheetQueryBuilder {
    const sheet = this.getSheet();
    const headings = this.getHeadings();

    newRows.forEach((row) => {
      if (!row) {
        return;
      }

      const rowValues = headings.map((heading) => {
        const val = row[heading];
        return val === undefined || val === null || val === false ? '' : val;
      });

      // appendRow() will throw if array is empty, so we check to prevent that
      if (rowValues && rowValues.length !== 0) {
        sheet.appendRow(rowValues);
      }
    });

    return this;
  }

    return this;
  }

[vlucas]

Yes, it is pretty slow this way (calling appendRow in a loop). There are some faster ways to achieve this, but they don't have the same atomic guarantees, so I have been hesitant to use them.

There are some methods here that you can experiment with if you like:
https://stackoverflow.com/questions/44695360/appending-multiple-rows-to-spreadsheet-google-apps-script

If any of those work for you, I'd love to have a contribution back with the working code! :)