Add form data to dynamically-named Google Sheets tabs, "Invalid collection in parameter 'rows'" error

[Design1]

What is your goal?

Automatically add event RSVP data to different Google Sheets tabs based on which speaker the user registered for. Each speaker has their own tab (e.g., “John Smith-12/10”), and I need RSVPs to go directly to the correct speaker’s tab.

What is the problem & what have you tried?

I initially tried using the “Add a Row” module, but quickly hit a roadblock - the Sheet Name field only provides a dropdown of existing sheets with no option to map dynamic values.
Since I need to construct the sheet name from form variables ({{Speaker name}}-{{Event date}}), this wasn’t going to work.

So I switched to “Bulk Add Rows (Advanced)” which has a mappable Sheet Name field. I configured it with the dynamic sheet name and tried mapping the row data directly: [[{{1.Email}}, {{1.Speaker name}}, {{1.Event date}}, {{1.Question}}]]

This gave me an “Invalid collection in parameter ‘rows’” error.

To fix the validation error, I added an Array aggregator before Bulk Add Rows.
The aggregator has [[{{1.Email}}, {{1.Speaker name}}, {{1.Event date}}, {{1.Question}}]] in the Values field, and Bulk Add Rows uses {{8.Array}} in the Rows field.

This partially works - the data now adds to the sheet without errors. But two problems remain:

The data appears as a JSON string in cell A2 instead of spreading across columns A, B, C, D
Once the Array aggregator is in place, I can only access the aggregated array ({{8.Array}}) and lost access to individual variables like {{1.Speaker name}}, making it difficult to construct the dynamic sheet name

I’ve tried switching between USER_ENTERED and RAW for the Value input option, and experimented with single vs double brackets in various places, but haven’t found a configuration that works.

Thank you for your help… I just spent hours in this scenario…

Error messages or input/output bundles

Error 1 - When mapping rows directly in Bulk Add Rows:

BundleValidationError
Validation failed for 1 parameter(s).
– Invalid collection in parameter ‘rows’.
Origin: Make

Error 2 - When trying to use aggregated data for dynamic Sheet Name:

RuntimeError
400: INVALID_ARGUMENT - Unable to parse range: ‘1. Speaker name (B) - 1. Event date (C)’!A1
Origin: Google Sheets

Current output (not an error, but wrong format):
Data successfully adds to cell A2, but appears as JSON string instead of spreading across columns:

[
  [
    email@example.com,
    John Smith,
    12/10,
    Sample question
  ]
]

Expected:

• email@example.com in A2
• John Smith in B2
• 12/10 in C2
• Sample question in D2

Screenshots (scenario flow, module settings, errors)

[Design1]

I am adding additional screenshots below, I couldn’t had more than 1 in my current post.

[samliew]

Welcome to the Make community!

You are inserting a plain text STRING into the Values field, when an ARRAY is expected.

1. Switch off the “Map” toggle.

2. Put each variable into the respective cells.

Hope this helps! If you are still having trouble, please provide more details.

— @samliew

[Design1]

Hi @samliew !

First, thank you! It helped me fix the issue with the row values, and I can now correctly add new rows into Google Sheets.

I’m now stuck on what seems to be the very last step…

• I have a Google Sheet with one main tab called RSVP.

• I manually create additional tabs (one per speaker), e.g.:

  • John Snow-12/10

  • Marie Poppins-11/22

• When a user submits the form, the RSVP data is added to the RSVP tab (works).

• Then I want to ALSO add the user to the tab corresponding to the event they selected. but only if that tab already exists (because I create them manually).

Problem:

Even though I use a router with a condition checking if the tab name exists (using List Sheets), Make still runs the “Add Row” step on a non-existing tab and throws:

RuntimeError  

404: NOT_FOUND – Requested entity was not found  

Google Sheets

It seems the router is not preventing the execution of the “Add Row” module…? Or i miss something?

[Stoyan_Vatov]

Hey there,

I believe you are talking about different sheets, not different tabs?

If you are already using list sheets, what happens if you directly map it from there? Instead of mapping it from the tools module.

Also your spreadsheet ID is wrong. You are mapping the entire output bundle from the Watch module, not just the ID. I think this one is what is throwing the error.

[Design1]

Hi!

You were right, the issue was coming from the Spreadsheet ID. I was mistakenly mapping the entire bundle instead of the actual ID.

It’s all good now!

I’m still a very new Make user, so I’m not fully sharp yet Thanks a lot for your help and patience!

Have a great day!