1

First of all I am a complete beginner in coding. So I am trying to build a form, that once it is submitted, will send a different gdrive file as excel sheet based on a key word in the form. So far I was able get the file as an PDF but I am still struggling to get it as Excel and based on a keyword.

Hope someone is able to help me or guide me to the right place.

Thanks in advance

  • Welcome. That's a tall order but good on you for having a go. Your question is very general but your problem is very, very specific. In order to help you, you need to provide some (MUCH) more information about your scenario. Please edit your question to provide information about the form, the key word, the file, your existing script, the trigger, whether the script is bound to the form or a spreadsheet, how the file is identified, and anythging else that you consider relevant. – Tedinoz Dec 11 '19 at 8:58
0

i know this is an old post but this answer is for anyone else looking for the same solution to a similar problem quite recently.

The solution is simple

there are two parts to this

first you need to get the sheet to trigger & run a script code based on a cell value.

https://developers.google.com/apps-script/guides/triggers

you need to modify the code above to run on a specifc cell value not on the whole sheet onChange() trigger will run on any changes to the sheet so that will not work unless your code is designed to run and query that specific cell value everytime the trigger is activated.

to better understand what i mean look at the code for conditional formatting

the code below will change the cell color based on the value that has been inputed into the cell - in this case its looking for numbers 0-10 but you can easily modify this code to look for a word insted of a number like in the example i mentioned below the specific word "salary" that will trigger a code to send an email to finance with a excel file attachment "accounts .xls".

/ Adds a conditional format rule to a sheet that causes cells in range A1:B3 to turn red if // they contain a number between 1 and 10. var sheet = SpreadsheetApp.getActiveSheet(); var range = sheet.getRange("A1:B3"); var rule = SpreadsheetApp.newConditionalFormatRule() .whenNumberBetween(1, 10) .setBackground("#FF0000") .setRanges([range]) .build(); var rules = sheet.getConditionalFormatRules(); rules.push(rule); sheet.setConditionalFormatRules(rules);

full example here

https://developers.google.com/apps-script/reference/spreadsheet/conditional-format-rule-builder?hl=ID

the cell value should contain the specific keyword received from the google form.

for example if the word "salary" is entered onto the google sheet from the form into cell A1 - with your trigger set - to run on cell A1 change the code will run looking for the specific word "salary" in cell A1 - if the word "salary" is found it will call the second part of the code - i.e. is to perform the action of sending out an email via scripting to the specified receipents along with the attachment assuming you have the same stored in your google drive folder- in this case "accounting.xls" to the finance team.

codes to send emails & attachments from google drive:

https://developers.google.com/apps-script/reference/mail/mail-app

https://developers.google.com/apps-script/articles/sending_emails?hl=en

https://developers.google.com/apps-script/quickstart/forms

https://developers.google.com/apps-script/reference/drive/drive-app

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.