Google Sheets: Requests Handling Automation

Hi everyone!
I am new on the forum and generally in Javascript coding.

I am part of a small association that asked me to create a form to group and handle input requests from volunteers.
Needing a free and familiar solution, I chose Google Forms to collect requests. There are 3 fields to fill in: name, request (multiple choice), belonging department (multiple choice).
Editing to my use already existing guides (search "Send email from Google Sheets based on a schedule" on Google), I created a basic program with AppsScript: requests are automatically imported into a Google Sheet, then grouped in JSON format and finally sent via e-mail. Adding a time trigger, I can receive a daily summary e-mail in text. It can be also changed the design into a more pleasing HTML table.

The problem is that for my purpose isn not enough: the list of requests should always be kept compact (without empty rows), updated (for each user only the most recent request is kept and after 24h it is removed) and grouped by belonging department.
I do not have deep knowledge in Javascript coding (neither other volunteers): I can modify the source code for my needs, but I am not able to write it from scratch.
Here is the flowchart and related source code of the program.
I would be willing to work with you to solve this issue.

Regards.

Originally Posted by Haluk

Related with the blank rows in range "Stocks", you can use a filter to exclude them.

So; assuming that the named range "Stocks" is referring to columns A, B and C where the cells A1, B1 and C1 are housing the headers, you can use the following revision.

Please Login or Register  to view this content.

I am not sure what are the other problems.

Hi,
thanks for your reply and suggestion!

I'm sorry I couldn't explain well all the problems, I'll try again to explain what the program can and cannot do.
1) CAN:
a) send emails, based on a daily time trigger, that show all form's replies.

2) CANNOT:
a) copy in another cell the infos to bypass the "column copy/paste/trim block" (in simple terms, I can't move upwards the cells, they are blocked, try yourself).
b) if the name isn't already stored in the sheet, then add it at the bottom cell; instead if there is, remove the old replies and replace them with the most recent one. After 24 hours, remove this reply to make space to new ones.
c) remove empty rows and move upwards the bottom ones is needed for point b) to work: after I've removed old replies, I'm left with some blank rows, so I need to compact the remaining cells in less space.
d) group all rows by the same belonging department to make a clean workflow, referencing the related "Belonging department" cell in the sheet.

This actions need to be automated with code and added in the already existing source code.
I'll also attach all the related screenshots needed to solve this problem.

Let me know!
Regards.

1.png

2.png

3.png

4.png

5.png

My apologies, I am new in the world of forums and I did not know about the cross-posting rule...

For fairness, I will write it below:
The following answer was also cross-posted over another forum (UiPath Forum --> forum.uipath.com/t/appsscript-code-to-send-google-sheet-columns-via-e-mail/477128).
Based on this rule, you should publish your next answers in that forum to maintain a single discussion.


Regarding the source code, here it is:
Source Code (Updated).pdf

Hi veyselemre, thanks for your help!
I've edited the reply: you can find it below.

I showed my association the work and they were impressed.
However, they pointed out 4 edits to your source code to make it 100% perfect.

1) They changed the position of 2 columns ("Belonging department" column is now at the left of "Request"). The source code should work on the new positions (I wouldn't want to mess with variables myself).

2) They would want a blank row between one department and another to keep order.

3) They added the option "delete_previous_request" to the "Request" cell. The sheet already keeps the most recent row for every user: in this case, it should delete the most recent row with "delete_previous_request" under the "Request" column, because the user doesn't want to be on the sheet anymore.

4) They added a column "Keep request after 24h". You can answer "NO" (the request would be deleted after 24h) or "YES" (the sheet would keep the request beyond 24h).
This column does not have to affect other actions (e.g. if a request is kept beyond 24h, it will continue to be grouped by department or replaced with the most recent request).

Feel free to ask me for any troubles.
Any help would be highly appreciated!


Google Sheet: //h t t p s : //docs.google.com/spreadsheets/d/1oLs0Iu_BFMPaLwQvq8USCeBoYxwGnWq53LqvZlZ3fIM/edit?usp=sharing
Google Form: //h t t p s : //forms.gle/hi8XBib7tw6buWdC8


1.PNG

2.PNG

3.PNG

Hi,
I don't know English, I translate with google translate. As far as I understand, I tried to answer your requests.

Please Login or Register  to view this content.

Hi veyselemre,
no problem: I realize perfectly that it can be harsh to create source codes basing yourself only on requirements written by another user.
But, despite the toughness, I can say that you hit the spot!

Thank you very much indeed.