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

67 views
Skip to first unread message

Gilberto Junior

unread,
May 9, 2024, 11:24:52 AMMay 9
to Google Apps Script Community

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() {

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

unread,
May 9, 2024, 9:10:54 PMMay 9
to Google Apps Script Community
In your situation, is this thread usful? https://stackoverflow.com/a/65366101

Gilberto Junior

unread,
May 10, 2024, 6:17:27 PMMay 10
to google-apps-sc...@googlegroups.com
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

unread,
May 10, 2024, 8:26:01 PMMay 10
to Google Apps Script Community
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

Reply all
Reply to author
Forward
0 new messages