Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-apigoogle-forms

geting google form title and values in script issues

I am looking to get a few values from a previously submitted row from a google form response and then send these values, along with the title of the form, along to an end user.

however, I think I may be going about it the wrong way, I can pull up the spreadsheet and sheet and use this to edit a field, but i can not see how to pull the values back out from here, it just comes back as "range". what is the right syntax to get a values from a range?

is there any way to pull the title of the linked form? or the spreadsheet if that is not possible, as that is just "formName" +(responses). enter image description here

here is the code i have so far, it is probably just missing something simple here (the e value that it accepts is a formatted URL):

function doGet(e){
  var params = JSON.stringify(e);
  var message = "thank you, your Decision has been sent.";



  var id = '10kf5QDeBc-vr6XJnkFxQh-ch_poQhqBQWQbgJ';
 var ss = SpreadsheetApp.openById(id);
 // var ss = SpreadsheetApp.getActiveSpreadsheet();
 // var sheet = ss.getActiveSheet();
var sheet = ss.getSheets()[0];
var column = 20; 
var datecolumn = 5;
var row = e.parameter.row;
//var title = sheet.getTitle();
var sheetname = ss.getSheetName();
var sheetnamesheet = sheet.getName();
 var eventDaterange = sheet.getRange(row, datecolumn);
 var eventDate = eventDaterange.getvalues();


 var approval_cell = sheet.getRange(row, column);



//set default to declined
  approval_cell.setValue("declined");

  var approvedMessage = "<HTML><body>"+
              "<h2>your request has been Approved </h2><br />"
 +"<P>" + title +"   " + sheet
 +"<p>" +  "  for this date:   "+ eventDate;

  var deniedMessage = "<HTML><body>"+
              "<h2>your request has been denied </h2><br />"
  +"<P>" + ss +"   " + sheet
  +"<p>" +  "  for this date:   "+ eventDate;

  var aprovalResponce = (e.parameter.approval == 'true') ? approvedMessage :     deniedMessage;

  //var msg = "Your manager said this:  " + aprovalResponce;
  var msg = aprovalResponce
 var replyEmail = e.parameter.reply;



   if (e.parameter.approval == 'true') {
     approval_cell.setValue("APPROVED");

  } else {
    approval_cell.setValue("DECLINED");

  }


  //send the actual email out
  //MailApp.sendEmail(replyEmail, "Approval Request", msg);
  MailApp.sendEmail(replyEmail, "Approval Request", {htmlBody: msg});

  return HtmlService.createHtmlOutput(message);
}
Solution

  • To get values from a range, you use var values = range.getValues(); This will return a result in an 2D array of values, which is like a grid representing the range, i.e. values[row][column]. So, if our range is, say, "A1:C3", and we want the values in A1 and C3, we could do this:

    var range = sheet.getRange("A1:C3");
var values = range.getValues();
var valueOne = values[0][0];
var valueTwo = values[2][2];

var msg = '"Form name" + valueOne + ValueTwo'

    I don't know how to get the form name though, perhaps something as simple as e.source.title? If not, if you are using multiple forms, you may have to do this by getting the form Id from the form being submitted, and then using a switch statement to match that ID to a list of form names within your script.