Keep getting error when using group merge

[dave999dave]

The instructions are very detailed with good screenshots. I've followed them as precisely as I can but I keep getting the below error.

Unexpected Error:
Exception: Invalid argument: url
at mailMerge (group-merge:942:29)
at sendEmails (group-merge:891:5)
at GS_INTERNAL_top_function_call.gs:1:8

The only thing I've not customized according to the instructions is the markers for mail and group merge.

Merge Field Marker: [{]{2}([^{^}]+)[}]{2}
Group Field Marker: [[]{2}([^[^\]]+)[]]{2}

I've used the traditional gmail/sheets mail merge before and that worked fine. But now I need to send a bunch of emails where one person would otherwise receive 50+ emails. So I'm trying to use this extension to get it down to one per recipient. As a test I removed the group field markers but the result is the same for this extension.

Any help would be much much appreciated. I've pasted the debut export, the email draft, and the column headers for the google sheet.

Here's the debug export:

completedRecipients: []
config:
{"SPREADSHEET_URL":"google_sheet_source_link","DATA_SHEET_NAME":"Small
Test - Gmail/Sheets mail
merge","TO":"{{Email}}","CC":"","BCC":"","TEMPLATE_SUBJECT":"Draft subject
line","ENABLE_GROUP_MERGE":true,"ENABLE_REPLY_TO":true,"REPLY_TO":"[email protected]","REPLACE_VALUE":"NA","MERGE_FIELD_MARKER":{},"MERGE_FIELD_MARKER_TEXT":"[{]{2}([^{^}]+)[}]{2}","GROUP_FIELD_MARKER":{},"GROUP_FIELD_MARKER_TEXT":"[[]{2}([^[^\\]]+)[\]]{2}","ROW_INDEX_MARKER":"{{i}}","ENABLE_DEBUG_MODE":true,"hostApp":"GMAIL","userLocale":"en"}
draftMode: false
exeRoundsMailMerge: 0
prevCompletedRecipients: []
processTime: []
start: 1688780199598

Here's the email draft:

Dear {{Name}}

Blah blah testing, testing.

I've pasted the fabulous fields below

[[
Number: {{i}}
Size: {{Size}}
Owner: {{Owner}}
Color: {{Color}}
Hyperlink: {{Hyperlink}}
]]

Here's the list of fields from the Google sheets

Name
Email
Size
Owner
Color
Hyperlink

[ttsukagoshi]

Hi @dave999dave, thank you for the detailed report!
The error message you are seeing:

Unexpected Error:
Exception: Invalid argument: url
at mailMerge (group-merge:942:29)
...

points to the part where the add-on looks for the spreadsheet that the user specified in the add-on sidebar:

This error message by Google says that it can't find the spreadsheet by the URL that the user has specified. Could you check the URL to see if it conforms to the following format?

https://docs.google.com/spreadsheets/d/<sheet-id>/edit

The point here is to confirm that the URL ends with /edit, or optionally with a query parameter specifying a worksheet: /edit#gid=012345

Details

The tricky part is that there are several patterns when designating a spreadsheet URL:

1. The basic format: https://docs.google.com/spreadsheets/d/<sheet-id>/edit#gid=0
2. The basic format without specifying the worksheet ID gid: https://docs.google.com/spreadsheets/d/<sheet-id>/edit
3. Pattern 2 without edit: https://docs.google.com/spreadsheets/d/<sheet-id>/
4. Pattern 3 without the trailing /: https://docs.google.com/spreadsheets/d/<sheet-id>

Patterns 1 - 4 all work fine if you are simply entering the URL in your browser, but it is not so in Google Apps Script (GAS), which is the underlying architecture behind this add-on. In GAS, Patterns 1 - 3 are acceptable, while Pattern 4 returns the error that you shared:

Exception: Invalid argument: URL

So I am hoping that the solution lies in correcting the URL format, but there might be some other variable factors that return the same error; something that I am not aware of. Please don't hesitate to say I'm barking up the wrong tree!

The Easiest Solution

If the cause of this error were in the URL format mentioned above, following the How-to-Use steps in the add-on's webpage would be the easiest way to avoid it; you can let the add-on enter the spreadsheet URL automatically at step 4.

But again, since you say you've followed the steps on the web page, this might not be the issue. Your additional feedback will be much appreciated!

[ttsukagoshi]

I've created an issue to add a validation process to the spreadsheet URL: #324

[dave999dave]

With your help I figured it out.

• The first three versions you mentioned above gave me this error "The worksheet of the list of recipients in the spreadsheet cannot found. Check if you have entered the correct sheet name," not the one I saw before. So that's progress.
• After seeing that error a few times I eventually realized that the "Sheet Name" referred to the sheet inside the google sheet, or "tab." It did NOT mean the name of the Google Sheet itself.

Once I used version 3 and changed the Sheet Name to the name of the tab inside the Google sheet everything worked as expected.

Your documentation is already better than 99% of what I see on the net. It's step by step, strives to be idiot proof, uses screenshots, has examples, etc etc. But I think if you made a few changes it would be even better. (If you wanted to give me edit rights I could handle this. I've written documentation for corporations for 20 years. Serious offer, but I of course understand if you say no. I would say no.)

• Expand section 4 and have a series of screenshots that walks users through all the fields (screenshot attached to highlight which part I'm referring to). Each field would have a corresponding description with one or two examples. As part of this the three url versions that work would be identified. The fact that the "Sheet Name" refers NOT to the Google Sheet but to the tab inside the sheet would be emphasized.
• Put a note at the start of the documentation that users are NOT required to use to use a sheet that has been prepped with Google's own in-house mail merge script. I've tested and confirmed that any sheet with the structure described in the documentation will work. This is a stand-alone product that does not need other extensions or scripts.

Many thanks for your quick response. Using this thing will save me many hours of effort.

Dave

(Serious offer about enhancing the documentation. I've made my living in part by making this stuff for years. You could make a copy, give me edit rights to that, and if you like the result you could migrate that to the publicly available version. I'm very impressed by what you've already made and very grateful at how much time this will save me.)