Send me the record

[Himaladin]

Hi everyone,
The schema of the student table below illustrates the exchange of lecturers in each assessment activity for a number of students. In addition, I also have a list of lecturers that I relate to display this assessment form per student to be displayed in Glide.

I want to create a “Send me the record” button to partially and limitedly email according to the lecturer who inputs the value. The form of the email can be in the form of a pdf attachment or in the form of an Html table.
Can anyone give an idea to collect and display the partial data in tabular or pdf form?
Thank you

[Uzo]

maybe this will help… sample:

Personal Time Card DEMO

[Himaladin]

Hi Uzo,
Can you describe your application workflow?

I have a complete list of lecturers with their email data and I expect the email to be sent simply by pressing the “send me the record” button or some kind of download button without filling in a number of entries except the order of the assessment as requested.
I learned a bit about how to create an Html table template by pulling data from a spreadsheet, but what’s still confusing is how the email can be triggered from the button.
Maybe I need some time to do some testing myself but I haven’t found the concept of thinking, especially regarding who presses the button and the data to be given.

[Uzo]

hi… simply have a button with action to set some value in GS, in my case, I write filter option, week, month, year, or all data… that will set filtering in GS to pull all matched data into PDF sheet… that action also will fire onChange trigger to run a script that creates and email PDF files of that sheet.

[Himaladin]

Ok. The value will be written in the spreadsheet, then does the value need to be deleted so that later it can be requested again for the same report?

[Uzo]

no, it will be overwritten by the next request… you might add some unique value to it (like a timestamp), so it will change the value for the onChange trigger, in case the next one is exactly the same

[Himaladin]

What kind of script from the value?
I haven’t and will try to make one. I’ll need the next few days to try it out. Maybe I’ll bother you again.

[Uzo]

this is a lot of work, especially when you doing it for the first time… you might consider getting my template

[Himaladin]

Previously I had a very long Html email template that I used to upload files with Bootstrap components rendered with webviews in Glide. I only need to partially overhaul especially the trigger from Glide.
Maybe you can give some snippets is enough.

[Himaladin]

First I need to extract the data for me to place the data inside the email template. I think here we need a (vlookup, query, or else) formula to match multiple conditions (name from the requester, how many reviews, and the title of the teacher who requested it). The rest I think I can work out.
Maybe @ThinhDinh would like to help me with the formula. Thank you in advance.

Teacher sheet:

Email template:

[Himaladin]

To reduce column repetition, I changed the assessment data table to be as below.

[Himaladin]

I have had success using the query formula and the if function.
Thank you for all.

[Uzo]

the best practice is to use only vlookup, which will make faster changes… trigger might take data before the query finishes… and your PDF might be incomplete when contains lots of data…
Use SpreadsheetApp.flush(); in your script to assure full upload.