Timeout writing long data set

[mgugnali]

I get Time Out error when I try to write more than 50000 rows to my google sheet.

Log:
2021/09/20 12:34:39 - Pentaho Google Sheets Output Replace ALL.0 - Clearing range XXXX in Spreadsheet :XXXX
2021/09/20 12:34:39 - Pentaho Google Sheets Output Replace ALL.0 - Clearing Sheet: XXXXin Spreadsheet :XXXX
2021/09/20 12:34:42 - Pentaho Google Sheets Output Replace ALL.0 - Writing to Sheet
org.pentaho.di.core.exception.KettleException:
Read timed out

at org.pentaho.di.trans.steps.pentahogooglesheets.PentahoGoogleSheetsPluginOutput.processRow(PentahoGoogleSheetsPluginOutput.java:375)
at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
at java.lang.Thread.run(Thread.java:748)

2021/09/20 12:35:04 - Pentaho Google Sheets Output Replace ALL.0 - ERROR (version 9.2.0.0-290, build 9.2.0.0-290 from 2021-06-02 06.36.08 by buildguy) : Unexpected error
2021/09/20 12:35:04 - Pentaho Google Sheets Output Replace ALL.0 - ERROR (version 9.2.0.0-290, build 9.2.0.0-290 from 2021-06-02 06.36.08 by buildguy) : org.pentaho.di.core.exception.KettleException:
2021/09/20 12:35:04 - Pentaho Google Sheets Output Replace ALL.0 - Read timed out
2021/09/20 12:35:04 - Pentaho Google Sheets Output Replace ALL.0 -
2021/09/20 12:35:04 - Pentaho Google Sheets Output Replace ALL.0 - at org.pentaho.di.trans.steps.pentahogooglesheets.PentahoGoogleSheetsPluginOutput.processRow(PentahoGoogleSheetsPluginOutput.java:375)
2021/09/20 12:35:04 - Pentaho Google Sheets Output Replace ALL.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2021/09/20 12:35:04 - Pentaho Google Sheets Output Replace ALL.0 - at java.lang.Thread.run(Thread.java:748)
2021/09/20 12:35:04 - Pentaho Google Sheets Output Replace ALL.0 - Finished processing (I=0, O=0, R=52148, W=52148, U=0, E=1)
2021/09/20 12:35:04 - write-to-google-spreadsheets-replace - ERROR (version 9.2.0.0-290, build 9.2.0.0-290 from 2021-06-02 06.36.08 by buildguy) : Errors detected!
2021/09/20 12:35:04 - Spoon - The transformation has finished!!
2021/09/20 12:35:04 - write-to-google-spreadsheets-replace - ERROR (version 9.2.0.0-290, build 9.2.0.0-290 from 2021-06-02 06.36.08 by buildguy) : Errors detected!
2021/09/20 12:35:04 - write-to-google-spreadsheets-replace - ERROR (version 9.2.0.0-290, build 9.2.0.0-290 from 2021-06-02 06.36.08 by buildguy) : Errors detected!
2021/09/20 12:35:04 - write-to-google-spreadsheets-replace - Transformation detected one or more steps with errors.
2021/09/20 12:35:04 - write-to-google-spreadsheets-replace - Transformation is killing the other steps!

[mgugnali]

I tryed to use a workaround:

1. I writed first 10000 rows without "append to sheet" option
2. I looped 4 times writing 10000 rows every loop with "append to sheet" option

I didn't get any error, but if I run the entire process again I get this error:
#####################################################
2021/09/20 14:25:15 - Pentaho Google Sheets Output Append.0 - {
2021/09/20 14:25:15 - Pentaho Google Sheets Output Append.0 - "code" : 400,
2021/09/20 14:25:15 - Pentaho Google Sheets Output Append.0 - "errors" : [ {
2021/09/20 14:25:15 - Pentaho Google Sheets Output Append.0 - "domain" : "global",
2021/09/20 14:25:15 - Pentaho Google Sheets Output Append.0 - "message" : "This action would increase the number of cells in the workbook above the limit of 5000000 cells.",
2021/09/20 14:25:15 - Pentaho Google Sheets Output Append.0 - "reason" : "badRequest"
2021/09/20 14:25:15 - Pentaho Google Sheets Output Append.0 - } ],
2021/09/20 14:25:15 - Pentaho Google Sheets Output Append.0 - "message" : "This action would increase the number of cells in the workbook above the limit of 5000000 cells.",
2021/09/20 14:25:15 - Pentaho Google Sheets Output Append.0 - "status" : "INVALID_ARGUMENT"
2021/09/20 14:25:15 - Pentaho Google Sheets Output Append.0 - }
#####################################################

This is because in the first step (without "append to sheet" option), the process clear all cells, but don't remove all rows of sheet before writing.
Loops after first, insert rows after firts 10000 rows and shift empty rows down, so my sheet has too many cells after a while.

I'd like to have a function to remove all rows of the sheet, I think it can be usefull.

Thanx

[jfmonteil]

Hello, for the tiemout, I added it in the latest release

[jfmonteil]

Let me look at the remove all rows
The thing is that at some point you should switch to a DB