lundi 23 mars 2015

send google form via scirpt

I have my family cell phone bills set to autopay and sometimes forget to send each individual their portion of the bill. I wanted to setup an automated system with google apps to send me a form where I can input each person's owed amount. Then after I submit the form, the app will send the people on my plan an email with their amount due. I then want the same prefilled form sent back to me a week or two later that allows me to update my googlesheet column with people's payment status.


I have figured out how to process the form data and then send out the bill emails. However, I cannot find a way to resend a form via a google-scirpt so I can update if an individual has paid me and I have cashed their checks. I figure I will need to capture the responce.id to update the same sheet entry, but I have not been able to find a way to resend the form.

I have googled a bit and tried to some method like sendForm("email",formID) but have not been able to find anything. Everything I have seen online shows me how to send the form from my google drive. However, I was wondering if anyone knows if such a method exists.


Here is a sample of my code so far (I am in no way a professional programmer so feel free to make suggestions to improve the existing code):



/*Define key variables up here like URL for sheets */
var sheetId = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
var sheetName = "Form Responses";
var sheetData =SpreadsheetApp.openById(sheetId).getSheetByName(sheetName).getDataRange();
var lastRow = sheetData.getNumRows();
var monthNames = ["January", "February", "March", "April", "May", "June",
"July", "August", "September", "October", "November", "December"];

var base = [ 1, 2];
function test(){
for (i = 0; i< base.length; i++){Logger.log(i);}
};


function lastBillComplete() {
lastRow = sheetData.getNumRows();

isSuesCheckCashed = sheetData.getValues()[lastRow-1][7];
isNancysCheckCashed = sheetData.getValues()[lastRow-1][8];

if (isSuesCheckCashed == "Yes" && isNancysCheckCashed == "Yes") {
return true;}
else{
return false;}


};


function sendBill(){
// Array of names that emails are being sent to
var emails = ["xxxxxxx@gmail.com","xxxxxxx@gmail.com"]; //the real emails will go here.
var names = ["Sue", "Mom"];

//subject line of email
var subject = "Verizon bill for " + monthNames[new Date(sheetData.getValues()[lastRow -1][2]).getMonth()];


//body of email that changes for each person

for (i = 0; i < names.length; i++){
var amount = sheetData.getValues()[lastRow-1][i+3];
var body = new String("Hi " + names[i] +",\n\nYour portion of this month's bill is $"+amount +". I have attached the .pdf of the bill to the email if you would like to look it over. Please let me know if you have any questions.\n\nTalk to you soon, \nJeremy");
MailApp.sendEmail(emails[i].toLowerCase(), subject, body);

}
};


I realize that probably the best way to set this up is to have the people on my plan just autopay to my bank account. However, the people on my account like to send actual checks, and are resistant to the idea. So I figured I would use the opportunity to improve my scripting abilities.


Thanks in advance, Jeremy


Aucun commentaire:

Enregistrer un commentaire