1

I have written the following code

function myFunction(e){
  var options={cc: 'x@abc.com'};
  MailApp.sendEmail(e.values[2], "New SMS Blast request", "A new SMS blast request has been entered, the particulars are:"+
                    "\n\n SMS Blast city:"+e.values[3]+
                    "\n\n SMS Blast Date:"+e.values[7]+
                   "\n\n SMS Blast time:"+e.values[8]+
                   "\n\n SMS Blast objective:"+e.values[4]+
                   "\n\n SMS Blast Base:"+e.values[5]+
                   "\n\n SMS content:"+e.values[10]+
                    "\n\n To approve or deny the request, kindly go to the following link:"+
                   "\n\n xxxxxx"+
                   "\n\n Data team, kindly share the SMS base once the request has been approved and enter the SMS base volume in the tracker"+
                    "\n\n SMS Team, kindly send the SMS once the data has been shared", options)

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var requestSheet= ss.getSheetByName("SMS Blast Request");

  var column = requestSheet.getRange('K:K');
  var columnValues= column.getValues();
  var row = 1;
  while ( columnValues[row-1][0] != "" ) {
    row++;
  }
  if(e.values[6]=="One Time"){
    requestSheet.getRange(row, 1).setValue(e.values[3]);
    requestSheet.getRange(row, 2).setValue(e.values[7]);
    requestSheet.getRange(row, 3).setValue(e.values[8]);
    requestSheet.getRange(row, 4).setValue(e.values[4]);
    requestSheet.getRange(row, 5).setValue(e.values[5]);
    requestSheet.getRange(row, 7).setValue(e.values[9]);
    requestSheet.getRange(row, 8).setValue(e.values[10]);
    requestSheet.getRange(row, 11).setValue("1");
  }
  else{
    var campaignDuration=e.values[14]-e.values[13]+1;
    var numOfSMS;
    var SMSInterval;
    if(e.values[15]=="Daily"){
      numOfSMS=campaignDuration;
      SMSInterval=1;
    }
    else if(e.values[15]=="Weekly"){
      numOfSMS=campaignDuration/7;
      SMSInterval=7;
    }
    else{
      numOfSMS=campaignDuration/2;
      SMSInterval=2;
    }

for(var i=0;i<numOfSMS;i++){
  requestSheet.getRange(row, 1).setValue(e.values[3]);
  requestSheet.getRange(row, 2).setValue(e.values[11]+SMSInterval*i);
  requestSheet.getRange(row, 3).setValue(e.values[14]);
  requestSheet.getRange(row, 4).setValue(e.values[4]);
  requestSheet.getRange(row, 5).setValue(e.values[5]);
  requestSheet.getRange(row, 7).setValue(e.values[15]);
  requestSheet.getRange(row, 8).setValue(e.values[16]);
  requestSheet.getRange(row, 11).setValue("1");
  row=row+1;
    }
  }
}

The above code basically takes the input of a form and then populates a google spreadsheet according to the responses of the form.

I selected the SMS type(e.values[6]): "One Time" and filled in the following values

City: NYC
Date:1/1/2015
Time:1:00:00 AM
Objective: Attachment
Base: Uber
Content: Test

As written in the code, I received a mail with all the values written correctly. However, in the spreadsheet, where the code is supposed to set the values, only city, objective and base are getting set correctly. The date column is getting the value "0" and the rest of the columns are blank.

I have tried many different things to solve this issue: I have tried setting the values using e.namedVAlues[] instead of e.values. I have also tried changing the array indices of e.values inside the setValue() bracket. However, even after changing the index, the function is populating the same values. I have checked both logs and execution transcripts and found the all the elements of e.values[] array are getting correct values. This has led me to believe that the problem lies only with the setValue function and it is not able to set some of these values for some reason.

Can anybody please explain me what is the solution to this problem or what is wrong with the code?

  • 1
    Please format your question properly. – serenesat Jul 2 '15 at 17:54

Your Answer

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

Browse other questions tagged or ask your own question.