Multiple rows on a single email w.r.to each user

[sravan kumar]

Hi Team,

I have spreadsheet where work done by each user is existing. Data consist of multiple rows w.r.to each user as per the different work done.

I trying to send the summary of all the work done by the user on the  next day by running the script manually using '*'.

Problem is there are multiple mails has been triggered for the specific user. As the data exist in the different rows.

I'm looking out for a solution where only single email goes to the user where all the rows are appended.

i tried to make the changes in the code but could not succeed.

Here is the sample data

user	Job	work_type	work_done
xyz	117358	x	259971
xyz	117358	y	259967
xyz	117473	z	260328
xyz	119890	a	267641
klm	122675	f	271840
abc	114071	f	251469
abc	114071	x	251494
abc	114095	z	251593
abc	114098	k	251587

Thanks in advance.

[Henrique Abreu]

I think the easiest route is to change the layout of your data, not the script.

Create another tab, let's call it it "Summary", while this one I'll name "Raw".
On summary you want one line per user (to have one email per user). So, on Summary!A2 you write:

=Unique(Raw!A2:A)

Now, on column B we'll need all rows aggregated. I'll stick to plain text for simplicity, if you want HTML that's an improvement you can do later.

There's various ways you could aggregate your data, I like to use ArrayFormulas and IFs, so on Summary!B2 you'd have:

=ArrayFormula(concatenate(if(Raw!A$2:A=A2, Raw!B$2:D & if(column(Raw!B$2:D)=column(D$1),char(10),", "), "")))

Then copy this down on Summary column B for all rows that have user on column A.

It is possible to turn this formula to automatically expand down to all users, but it'd become too complex and it's beyond the point here.

Now put some headers on Summary A1 and B1, e.g. "user" and "report", and have FormEmailer read from this sheet instead of Raw.

Remember to uncheck the "html" box or change this formula to return html instead.

Regards,

Henrique G. Abreu

--
You received this message because you are subscribed to the Google Groups "FormEmailer" group.
To unsubscribe from this group and stop receiving emails from it, send an email to formemailer+unsubscribe@googlegroups.com.
To post to this group, send email to forme...@googlegroups.com.
Visit this group at https://groups.google.com/group/formemailer.
For more options, visit https://groups.google.com/d/optout.

[sravan kumar]

Hi Henrique,

Thanks for quick reply

I have already created the summary sheet, and have been sending the single row to the respective user.

But the current requirement is to send the mail in detail that's where i got stuck. Request you to help in this regard, to fulfill the mentioned requirement.

Thanks in advance

On Sunday, 25 March 2018 19:09:30 UTC+5:30, Henrique Abreu wrote:

I think the easiest route is to change the layout of your data, not the script.

Create another tab, let's call it it "Summary", while this one I'll name "Raw".
On summary you want one line per user (to have one email per user). So, on Summary!A2 you write:

=Unique(Raw!A2:A)

Now, on column B we'll need all rows aggregated. I'll stick to plain text for simplicity, if you want HTML that's an improvement you can do later.

There's various ways you could aggregate your data, I like to use ArrayFormulas and IFs, so on Summary!B2 you'd have:

=ArrayFormula(concatenate(if(Raw!A$2:A=A2, Raw!B$2:D & if(column(Raw!B$2:D)=column(D$1),char(10),", "), "")))

Then copy this down on Summary column B for all rows that have user on column A.

It is possible to turn this formula to automatically expand down to all users, but it'd become too complex and it's beyond the point here.

Now put some headers on Summary A1 and B1, e.g. "user" and "report", and have FormEmailer read from this sheet instead of Raw.

Remember to uncheck the "html" box or change this formula to return html instead.

Regards,

Henrique G. Abreu

To unsubscribe from this group and stop receiving emails from it, send an email to formemailer...@googlegroups.com.

[sravan kumar]

Team, any help or suggestions here??

[Henrique Abreu]

What is your issue?

Henrique G. Abreu

On Mon, Mar 26, 2018 at 11:43 AM, sravan kumar via FormEmailer <formemailer+APn2wQelmLyLErbfdmUy...@googlegroups.com> wrote:

Team, any help or suggestions here??

On Sunday, 25 March 2018 19:22:25 UTC+5:30, sravan kumar wrote:

Hi Henrique,

Thanks for quick reply

I have already created the summary sheet, and have been sending the single row to the respective user.

But the current requirement is to send the mail in detail that's where i got stuck. Request you to help in this regard, to fulfill the mentioned requirement.

Thanks in advance

On Sunday, 25 March 2018 19:09:30 UTC+5:30, Henrique Abreu wrote:

I think the easiest route is to change the layout of your data, not the script.

Create another tab, let's call it it "Summary", while this one I'll name "Raw".
On summary you want one line per user (to have one email per user). So, on Summary!A2 you write:

=Unique(Raw!A2:A)

Now, on column B we'll need all rows aggregated. I'll stick to plain text for simplicity, if you want HTML that's an improvement you can do later.

There's various ways you could aggregate your data, I like to use ArrayFormulas and IFs, so on Summary!B2 you'd have:

=ArrayFormula(concatenate(if(Raw!A$2:A=A2, Raw!B$2:D & if(column(Raw!B$2:D)=column(D$1),char(10),", "), "")))

Then copy this down on Summary column B for all rows that have user on column A.

It is possible to turn this formula to automatically expand down to all users, but it'd become too complex and it's beyond the point here.

Now put some headers on Summary A1 and B1, e.g. "user" and "report", and have FormEmailer read from this sheet instead of Raw.

Remember to uncheck the "html" box or change this formula to return html instead.

Regards,

Henrique G. Abreu

To unsubscribe from this group and stop receiving emails from it, send an email to formemailer+unsubscribe@googlegroups.com.

[sravan kumar]

Hi Henrique,

I have a sheet with 12 columns to send on the mail.

but data consists of multiple rows of data related to same user, when i use '*' to send all the rows as mails each row has been sent as a separate mail. instead i'm looking for, a single email triggered for the respective user with all the rows in the sheet.

is there any way to make the changes in the code

On Tuesday, 27 March 2018 01:12:17 UTC+5:30, Henrique Abreu wrote:

What is your issue?

Henrique G. Abreu

[Henrique Abreu]

Sorry, but have you read my first reply to you in this thread? What's wrong with the approach I suggested? Have you tried it?

Henrique G. Abreu

On Tue, Mar 27, 2018 at 12:14 AM, sravan kumar via FormEmailer <formemailer+APn2wQelmLyLErbfdmUy...@googlegroups.com> wrote:

Hi Henrique,

I have a sheet with 12 columns to send on the mail.

but data consists of multiple rows of data related to same user, when i use '*' to send all the rows as mails each row has been sent as a separate mail. instead i'm looking for, a single email triggered for the respective user with all the rows in the sheet.

is there any way to make the changes in the code

On Tuesday, 27 March 2018 01:12:17 UTC+5:30, Henrique Abreu wrote:

What is your issue?

Henrique G. Abreu

To unsubscribe from this group and stop receiving emails from it, send an email to formemailer+unsubscribe@googlegroups.com.