Google-apps-script – Google Apps Script send attachment on cell value

google-apps-scriptgoogle-forms

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

Best Answer

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

Related Solutions

Google Apps Script – If Statement Based on Cell Value

If your script runs when the form is submitted, then presumably it is triggered on form submit. In this case, the function will receive an event object, in which the property values is the array of values submitted. This is the preferable way to access just-submitted form values, compared to fetching them from responses sheet.

The function would be like,

function processForm(e) {
  if (e.values[9] == 'something') { 
    // do something
  }
  else {
    // do something else
  }
}

Here the index is 9 because J is the 10th letter of the alphabet, and JavaScript index begins with 0.

Google Sheets – Mail Sheet as Excel Attachment Using Google Apps Script

Hi you can try the following script

function onOpen() { 
  // Try New Google Sheets method
  try{
    var ui = SpreadsheetApp.getUi();
    ui.createMenu('SendMail')
    .addItem('Send Report', 'getGoogleSpreadsheetAsExcel')
    .addToUi(); 
  }

  // Log the error
  catch (e){Logger.log(e)}

}

function getGoogleSpreadsheetAsExcel(){

  try {

    var ss = SpreadsheetApp.getActive();

    var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getId() + "&exportFormat=xlsx";

    var params = {
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
    };

    var blob = UrlFetchApp.fetch(url, params).getBlob();

    blob.setName(ss.getName() + ".xlsx");

    MailApp.sendEmail("rtr.shazzadkabir@gmail.com", "Stock report of today", "The XLSX file is attached", {attachments: [blob]});

  } catch (f) {
    Logger.log(f.toString());
  }
}

Best Answer

Related Solutions

Google Apps Script – If Statement Based on Cell Value

Google Sheets – Mail Sheet as Excel Attachment Using Google Apps Script

Related Topic