Hi. I work for a non-profit and we record donations on a spreadsheet which includes the last name, first name, donation amount and donation date along with a few other fields. In some situations, the same person donates multiple times. We want to send only one email but list each donation amount and date.
So, for example, say John Doe donates $500 the first of every month, which means there will be 12 rows at the end of the year that are the same except the donation amount (well, not in this case) and donation date.
As it stands now, I have a simple scenario that sends out 12 different emails, but how do I send only one email where the body lists each donation amount and date separately like the following:
Dear John Doe. Thank you for your donations listed below:
January 1, 2024 $500
February 1, 2024 $500
.
.
December 1, 2024 $500
My current scenario is just a Google sheet module and a Gmail module. I’ve tried inserting an array aggregator, but have not been able to do what I want to achieve.
If I was doing it programmatically, I would do something like this:
write opening paragraphs
read a spreadsheet row
previous email = email
while previous email == email
write donation amount, donation date
end-while
Thanks for your help
David
1 Like
Hi @David_Garrison
You can search records in sheet and use text aggregator with table html. Then send them to email.
Regards,
Msquare Automation - Platinum Partner of Make
@Msquare_Automation
2 Likes
Hi and thanks for the suggestion. However, I’m new to Make and it’s not clear to me what you are suggesting.
I inserted a text aggregator between the Google sheet search rows module and the Gmail module, but I lost access to the output from the Google sheet module and could only access the fields I defined in the text aggregator (see attached).
If it’s not too much to ask, can you please explain a bit more about the solution?
Thanks for the help
David
Hi @David_Garrison, You’re on the right track, @Msquare_Automation’s solution should be perfect here. Although I don’t get what you mean by ‘previous email’ and I don’t understand if you only need to do this operation for one person, or multiple, or how do you know which person we are searching for (do you change the input data in google sheet every time you want to use this scenario?).
x In search rows, search with person’s unique identifier (maybe mail).
x In text aggregator, use mapped values from ‘Search rows’ module :
dateFormat({{1.‘Date’}};MMMM d, YYYY) ${{1.‘donatedAmount’}}{{newline}}
Replace {{1.‘Date’}} and donated amount with proper mapped values from google sheet and instead of newline, just hit enter so that next result will be on a new line when aggregating (it’s like concatenate in excel).
That’s it. In your gmail module this is your body text :
((opening text, hello etc.))
{{2.‘aggregatedText’}}
((closing text, thanks bye etc.))
Let me know if that works.
Cheers,
kudracha
Hi Kudracha and thanks for the help. I was able to use what you and others have suggested to successfully generate emails with the body containing a row for each donation made by a donor.
Now, I have a new requirement and not sure how to accomplish it. I need to total up the donation amounts for a give donor and then write a line with the total after listing out each donation. Something like this:
Dear John Doe. Thanks for your 2024 donations, blah, blah, blah:
$100.00 was donated on October 1, 2024
$150.00 was donated on November 1, 2024
$175.00 was donated on December 1, 2024
Total donations for 2024: $425.00
I tried to use a numeric aggregator, but then I lost access to the output from the iterator.
Any thoughts as to how I can sum up all donations for a given donor and then write out the total after all of the individual donations?
Thanks again for your help
David
2 Likes
Hi @David_Garrison
Can you please start a new topic for your new question? When you initiate a new conversation, you’re more likely to catch the community’s attention and receive the help you need.
Thanks for helping to keep the community organized and making it easier for everyone to share and find the information they need. 
@David_Garrison, It’s normal to lose access to output after the aggregator. That’s how it works in Make. Your scenario should look something like this to integrate a sum :
update from old scenario :
x add numeric aggregator in parallel to text aggregator
x add set variable module setting variable ‘sum’ just after numeric aggregator, with filter inbetween to detect last result (consume less operations):
‘sheet search rows : bundle position’ = ‘sheet search rows : bundle total amount’
x add get variable module just after text aggregator, getting variable ‘sum’
x use sum from get variable module in gmail (Total donations = ‘sum’)
Let me know if u need more details
Thanks kudracha! I didn’t know that you could route data to do something e.g. sum values, save the value and then recall it later, but it does the trick.
I started with your scenario layout but was getting multiple emails for the same email address, so I ended up combining what I had already done with the addition of the router to sum up the donation values.
However, I now have a different problem in that some of the donation amounts have a comma in them which I causing the sum aggregator to choke. I can change the sheet cell format to something that doesn’t put a comma every 3 numbers, but I would like to keep the sheet as-is, and reformat the number in Make and remove the comma.
I tried formatnumber and parsenumber functions, but kept getting errors and the help did not help at all which I have found to not being very helpful at all!
Also, I’m not quite following the purpose of the filter between the numeric aggregator and the save variable module. Additionally, the only output I have available to the numeric aggregator is in the second screenshot.
Thanks again for your help - much appreciated!
David
