Google Script appendRow() not working when entire sheet is filtered

[Gilberto Junior]

I found a problem using appendRow method of google scripts when the entire sheet has filters enabled, as described in https://stackoverflow.com/questions/78420326/google-script-appendrow-not-working-when-entire-sheet-is-filtered.

The steps to replicate the problem are:

1. Enable a sheet with the filter enabled in all columns, not only where there's data, like this image.

2. Filter the column code only with "DSPACE". Note that with all columns filtered, appeared a "Blank spaces" option in the filter; The data vanishes only when trying to insert a row with the filtered value applied.

Now, run the code below:

function myFunction2() {

let sheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1z8mjdfnfpJdc06wCa5PTJPQFGTwPIOfI28i4ulkVL84/edit#gid=0').getActiveSheet();

Logger.log(sheet.getLastRow());

sheet.appendRow([new Date(), 'DSPACE', 'TEST 4']);

sheet.appendRow([new Date(), 'DSPACE', 'TEST 5']);

sheet.appendRow([new Date(), 'DSPACE', 'TEST 6']);

Logger.log(sheet.getLastRow());

}

If the range is not filtered, filtered with "Blank spaces" or the filter is available only to the correct columns, the rows are appended correctly.

Otherwise, the data simply vanish, with no errors, and no row is appended.

Am I missing something?

[Tanaike]

In your situation, is this thread usful? https://stackoverflow.com/a/65366101

[Gilberto Junior]

Great help! It works as expected.

Have already seen similar questions, and I was trying to avoid getLastRow() + setValues() because of the atomicity of the insertion. The function will be running in a web app. 

It seems that this is the only solution available for now.

Thanks!

On Thu, May 9, 2024 at 10:11 PM Tanaike <kanshi...@gmail.com> wrote:

In your situation, is this thread usful? https://stackoverflow.com/a/65366101

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/ct6EoAwvelU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/6076b9ba-14e3-4600-90e7-4ac374cea76bn%40googlegroups.com.

[Tanaike]

Thank you for replying. As I mentioned in my answer to your question on Stackoverflow, when the date object is included in the values, even when that workaround is used, it seems that only the date objects are not put. So, I proposed another workaround. Please confirm my answer. https://stackoverflow.com/a/78460152