Create row block limited to 30 rows?

[tatiang]

Is there a limit to the number of rows I can create in a Google Sheet using a create row block?

When I run this set of blocks, it stops every time after adding 30 rows:

[tatiang]

I’d still like to figure this out. Airtable doesn’t seem to have this same limit.

But for now, I’m switching to Firebase because it’s so much faster to store data. The create row block takes about 1 second to add data to Google Sheets and about 0.5 seconds to add data to Airtable. But I can store that same amount of data in Firebase in about 0.01 seconds.

[muneer]

I believe it is more of a memory heap issue. If you use a wait block in the loop to give the asynchronous create row a slice of time to complete it should do all the 50 rows.

[tatiang]

I used a Wait 0 seconds block and it still stopped at 30. I believe I also tried Wait 0.3 seconds but I’ll double-check that today. I’m backing up data from my app and there might be hundreds of rows so any delay is going to make the whole process quite slow, which I’m trying to avoid.

[muneer]

If you have lots of rows then you can read all the rows and make them into a list inside a list and then use Google Apps Scripts to push the results. I updated a Google Sheet with 15000+ and it took just a little than half a minute.

[tatiang]

Oh interesting! I’m kind of doing that with Firebase and it’s quite quick for my needs. But I might consider that if my data size grows.

With Google Apps scripting, are you calling the script with the Webviewer or are you triggering it somehow else within the Sheet itself?

I saw you mentioned Firestore in another post… is that something I should consider? What’s the advantage vs. Firebase’s Realtime Database?

[jared]

@tatiang

What’s the advantage vs. Firebase’s Realtime Database?

The types of queries you can construct and way more powerful and flexible. You store data slightly different and can use it more like a relational database, though it’s not.

My latest app uses firestore exclusively for the db. Firebase for user auth.

[muneer]

If all what you are after is a backup copy of your GSheet then you can do the trick I use with Firebase.

I create this simple Google Apps Script which returns all the rows in the sheet as a JSON string.

function doGet() {
  const dataRange = SpreadsheetApp.openById("Enter your own sheet ID")
                  .getSheetByName("Sheet1")
                  .getDataRange();
  return ContentService.createTextOutput(JSON.stringify(dataRange.getValues()))
    .setMimeType(ContentService.MimeType.JSON);
}

This is a sample output

Using these blocks

Now All what is required is to convert the API response to an object and store this object into a Firebase key.

You will have an exact replica of your GSheet.

[tatiang]

That’s a very useful setup. Thank you for sharing that. But actually, what I want to do is backup a local database and restore to the local database using a Google Sheet or Firebase. So ideally, I’d backup the JSON to Firebase and then restore by getting the full JSON from Firebase.

I actually have both working somewhat well with Firebase just looping through the data and using lists to store everything. So I think I might be okay for now. But if you know of a faster way using scripting or APIs, I’d be interested.