Javascript function to find the first empty row in spreadsheet for automatic reports

See more:

Javascript

MS-Excel

API

Async

, +

I was writing some code for automatically generating report and appending it into a spreadsheet. The program runs periodically, each run generates a set of data and inserts it into the first empty row in a specific spreadsheet file.

when writing code for inserting the row, I found that there is no API for searching where the first empty row is. Therefore I need to use the read API to search the row number for inserting new data.

The read API looks like this:


Since the spreadsheet is stored online, for performance issue, readCells is limited to return at most 30 rows of data each time.

Can you help me write a function to search the position of the first empty row in column A? Please note that the spreadsheet is for automatic reports, so the number of rows could be very large, and I want to generate the report quickly enough. this is not for Web Excel or Google Sheets etc.

What I have tried:

// suppose there are data in cells A1, A2, ..., upto A10
async function test() {
    console.log(await readCells('A1:A1')); // [['1']]
    console.log(await readCells('A1:A3')); // [['1'], ['2'], ['3']]
    console.log(await readCells('A1:A10')); // [['1'], ['2'], ..., ['10']]
    console.log(await readCells('A8:A12')); // [['8'], ['9'], ['10']]
    console.log(await readCells('A11:A11')); // null
}