Devtantra Blog

Tutorials about Android, Angular, ReactJS, PHP, MySQL and Web Development

Google Script For E-commerce Store Shipping Details

This tutorial will guide you about code on Google script for sending e-commerce store receipt as a email to buyer who fills valid information in Google form.Here i am taking three categories (Book,Mobile,Laptop) and details of these categories are stored in Google spreadsheet like (Stock,name,company name ,etc.) . Let us Begin

Gs

Code

Index

The code is divided into four sections. Each section adds additional functionality to the e-commerce mail script.

  • Section 1: Creating a form and sending a simple, automatic email confirmation after checking id length
  • Section 2: Checking id and stock of chosen category and send email after confirmation
  • Section 3: Sending HTML mail to user
  • Section 4:Editing in stock value of choosing category
  • Description About Used Functions
  • Summary

Section 1: Creating a form and sending a simple, automatic email confirmation after id checking

Create Google Form

You have to create a google form and generate the forms in the following way. Google Form

  1. You should three input methods  in the Google Form:ID, Choose Category and Email address. I am using  these method to manage user e-commerce receipt .

  2. Go to Google drive and create new spreadsheets for your categories and filled those according to your store information.Project Files Structure

    Google(Folder)— Book.xls

    — Mobile.xls

    — Laptop.xls

    — Googleform

    — code.gs

    — mail.html

    Google Spreadsheet

  3. Let’s add some code that will send an email to the user when a new request is entered. open google form response spreadsheet and in the spreadsheet, open the Script Editor (Tools > Script editor)

      4.   Copy, paste, and save the following code in the Script Editor. You’ll be                   prompted to give your script a name when you save it. You can name it               ‘e-commerce mail’ or anything you like.

function idchk (id){
 var inode=id;
 if(inode.toString().length ===5)
 {
 return true;
 }
 else
 {
 return false;
 }
 }
//main function
function onFromSubmit(e){
 var id = e.values[1];//take value of spreadsheet of 2 col latest
 var cat = e.values[2];//take value of spreadsheet of 3 col latest
 var emailaddress = e.values[3];//take value of spreadsheet of 4 col latest 
 
 var bool = idchk(id);
 if(bool === true){
 var message="successful ";
GmailApp.sendEmail(emailaddress,"Shipping details",message);
 }
 else{//id length is not valid
 var message = "choose 5 digit no.";
 GmailApp.sendEmail(emailaddress,"Shipping details",message);
 }

}//end of onFrom Submit

You will then see an ‘Authorization required’ dialog. This dialog will inform you what data and services the script is able to access so that you can choose to authorize the access or not. In this case, the script will have access to send emails. Click the Authorize button to grant the access.

The onFormSubmit will now be called each time when submit button of mouse is clicked and hence act as a event handler.An event is passed each time the submit button is clicked.This event parameter contains information about event occurred . Here e.values give array containing all the values in the form submitted by the user. The ordering of the values matches the ordering of the columns in the spreadsheet.

GmailApp.class contain method -send Email  which will take parameter (emailaddress,”Shipping details”,message) as a parameter.An email will be sent to  variable emailaddress which contain email address as a string.

Section 2:Checking id and stock of chosen category and send email after confirmation

Let’s match id to chosen category, For this i am calling  SendMailtouser() function and in this function i am using openId function of  SpreadsheetApp .

function SendMailtouser(idnum,id,emailaddress,cat){
           var key=idnum;
           var id=id;
           var emailaddress= emailaddress;
           var cat=cat;
           var ss = SpreadsheetApp.openById(key);//open given id sheet 
           var sheet = ss.getSheets()[0];//select sheet 0 means select first sheet  
           
         // Fetch the range of cells A2:D4
          var dataRange = ss.getRangeByName("A2:D4");//Get range from the notations
          var data = dataRange.getValues();//get values of spreadsheet according to datarange fn
          var boolean = false;
        //take data from mobile spreadsheet
          for(var i=0;i<data.length;i++){//for  loop for data 
                                         var row = data[i];//array calling
                                         var id1 = row[0];//id value
                                         
                                         if(id == id1){//checking id exist or not
                                                var stock = row[3];//take stock data
                                                
                                                if(stock > 0){//checking stocks value 
                                                   
                                                   GmailApp.sendEmail(emailaddress, "Shipping Details", "successful" );//message sending to form emailaddres
                                                   
                                                    }
                                                   
                                                   else //if stock is not avaiable
                                                       {
                                                        GmailApp.sendEmail(emailaddress, "shipping details", " transaction failed");//if stock is not avaible
                                                       }
                                                  return boolean =true;//id exist 
                                                       
                                                  break;//break for "for in"loop
                                                }//end of first if of "for in" loop
                                             }//end of "for" loop
                                             
                                 if(boolean != true)//checking boolean variable status 
                                                   {
                                                    message="transaction failed"+"please enter valid id "+id;//message for invalid id
                                                    
                                                    GmailApp.sendEmail(emailaddress,'shipping details',message);
                                                   }
}
  1. SendMailtouser() function is take the id according to category and then it check the id to spreadsheet . if its exist in spreadsheet after that it check the stock value, if stock value is greater than 0 . it send mail to user with “transaction successful” body message;but if it doesn’t in stock it send “transaction failed “.
  2. SendMailtouser() function also send a mail to user, if id is does not exist in given category spreadsheet.

Section 3: Sending HTML Mail To User

Below codes are for my for attaching “mail.html” HTML file . Here i call some variable value from my Google-script like category, id, name, author etc. In Google script you want to take some variable value from your script to html for this you need to write your variable name in between tag with equal sign . like this “<?= variable name ?>” and = sign indicate to Google-script to send value of this variable

<div >
  <p> Transaction successful and you will receive the <?=category?> <p>
  <table style="border:1px dashed gray;background-color:#fff;color:#000;" width="325px"> 
        <thead>
           <tr>
             <th colspan="3" style="border-bottom:1px dashed gray;">Receipt</th>
           </tr>
           <tr >
             <th style="border-bottom:1px dashed gray;"><?= ID?></th>
             <th style="border-left:1px dashed gray;border-bottom:1px dashed gray;"><?=Name?></th>
             <th style="border-left:1px dashed gray;border-bottom:1px dashed gray;"><?=os?></th>
           </tr>
            </thead>
            <tbody>
                <tr>
                    <th><?=id ?></th>
                    <th style="border-left:1px dashed gray;"><?=name ?></th>
                    <th style="border-left:1px dashed gray;"><?=author ?></th> 
                </tr>
            </tbody>
   </table>
</div>

In below code i take a variable template which contains the html file using HtmlService.createTemplateFromFile(“HTML File name without .html extension”) template.evaluate().getContent();

function SendMailtouser(idnum,k,id,emailaddress,cat,Name,ID,op){
var key=idnum;
var ID=ID;
var k=k;
var id=id;
var emailaddress= emailaddress;
var cat=cat;
var op =op;
var Name=Name;
var ss = SpreadsheetApp.openById(key);//open mobile sheet using its id 
var sheet = ss.getSheets()[0];//select sheet 0 means 1 first sheet  
// Fetch the range of cells A2:D4
var dataRange = ss.getRangeByName("A2:D4");//Get range from the notations
var data = dataRange.getValues();//get values of spreadsheet according to datarange fn
var boolean = false;
//take data from mobile spreadsheet
for(var i=0;i<data.length;i++){//for  loop for data 
                  var row = data[i];//array calling
                  var id1 = row[0];//id value
                  k++;//counter
                  if(id == id1){//checking id exist or not
                                var stock = row[3];//take stock data
                                var company_name =row[2];
                                var author_name=row[1];
                                if(stock > 0){//checking stocks value 
                                             var template = HtmlService.createTemplateFromFile('mail');
                                            //variabes of mail.html file
                                            template.main ="Mobile Report"//main variable of mail.html file 
                                            template.category= cat.toLowerCase();
                                           template.ID=ID;
                                           template.Name=Name;
                                           template.os=op;
                                           template.name = company_name;
                                           template.author = author_name;
                                           template.id = id;
                                           var html = template.evaluate().getContent();//recipient = Session.getActiveUser().getEmail();  
                                           GmailApp.sendEmail(emailaddress, "Shipping Details", 'Requires HTML', {htmlBody:html} );//message sending to form emailaddres
                                           stock--;//decremeant in stock variable
                                           var note =createNote(k);//calling closure&nbsp;function
                                           note.setNum(k);//calling closure&nbsp; function internal method setNum()
                                           var nota = note.getNum();//calling closure&nbsp;function internal method getNum()
                                           var range = sheet.getRange (nota, 4);
                                           // Logs "A1:E2"
                                           var s = range.getA1Notation();//get string value of curren column
                                           //Logger.log(s);//can be removed
                                           ss.getRange(s).setValue(stock);
                                           //Logger.log(s);//can be removed
                                          }
                                                   
                                 else //if stock is not available
                                     {
                                      GmailApp.sendEmail(emailaddress, "shipping details", " transaction failed");//if stock is not available
                                     }
                                       return boolean =true;//id exist 
                                       break;//break for "for in"loop
                                                }//end of first if of "for in" loop
                                             }//end of "for" loop
                                             
                                 if(boolean != true){ //checking boolean variable status 
                                              message="transaction failed"+"please enter valid id "+id;//message for invalid id
                                              GmailApp.sendEmail(emailaddress,'shipping details',message);
                                                   }
}

 Section 4: Editing in stock value of choosing category

Every E-commerce store need to take proper data of it stock .If anyone can buy any thing from our store . So this is a responsibility of purchaser to minus one from our stock same that type in this code if anyone can buy anything from store the code is minus one from its stock itself after mail . For this i am using Closure function and the function name is createNote and it return integer value .

//Main Function 
function onFromSubmit(e){
var id = e.values[1];//take value of spreadsheet of 2 col latest
var cat = e.values[2];//take value of spreadsheet of 3 col latest
var emailaddress = e.values[3];//take value of spreadsheet of 4 col latest 
var k=0;
var idnum;
var bool = idchk(id);
if(bool === true){
    switch(cat){//starting of switch case
                case 'MOBILE'://choose category
                //GET mobile SpreadSheet using spreadsheet id function
                 idnum="1IXBdZbdDEn-B5k__UUwzqf8efE16XtABw-NIJhLY0Po";
                 var Name="Mobile Name";
                 var ID="ID";
                 var os="Company Name";
                 SendMailtouser(idnum,k,id,emailaddress,cat,Name,ID,os);
                break;//break the case of mobile
                case 'BOOK':
                //GET Book SpreadSheet using spreadsheet id function
                 idnum="1pYITJ7Ujxuo6nhQGWa5exaRLsByJ-6Hvpun-yCA0uHg";
                 var Name="Author";
                 var ID="ID";
                 var os="Book Name";
                 SendMailtouser(idnum,k,id,emailaddress,cat,Name,ID,os);
                break;//break the case of Book
                case 'LAPTOP':
                 //GET Book SpreadSheet using spreadsheet id function
                 idnum="1cXsTljmCR8tIcW8sU6bECFP6agzVviDIr7r5TnmunPI";
                 var Name="Laptop Name";
                 var ID="ID";
                 var os="Operating system";
                 SendMailtouser(idnum,k,id,emailaddress,cat,Name,ID,os);
                break;//break the case of laptop
                default:
                 var message = "choose again";
                 GmailApp.sendEmail(emailaddress,"Shipping details",message);
                        }
                      }
          else{//id length is not valid
              var message = "choose 5 digit id";
              GmailApp.sendEmail(emailaddress,"Shipping details",message);
              }

}//end of onFrom Submit

//function of check id length 
function idchk (id){
                    var inode=id;
                    if(inode.toString().length ===5)
                    {
                     return true;
                    }
                    else
                    {
                      return false;
                    }
                  }
                  
  //closure Function for get value of notation                
  var createNote = function(no) {
  var num;
  return {
           getNum: function() {
                                return num;
                              },
           setNum: function(newNum){
                                   if(newNum == 1){
                                   num = 2;
                                   }
                                   else if(newNum ==2){
                                   num = 3;
                                   }
                                   else if(newNum ==3){
                                   num =4;
                                   }
    }
  }
}
function SendMailtouser(idnum,k,id,emailaddress,cat,Name,ID,op){
var key=idnum;
var ID=ID;
var k=k;
var id=id;
var emailaddress= emailaddress;
var cat=cat;
var op =op;
var Name=Name;
var ss = SpreadsheetApp.openById(key);//open mobile sheet using its id 
var sheet = ss.getSheets()[0];//select sheet 0 means 1 first sheet  
// Fetch the range of cells A2:D4
var dataRange = ss.getRangeByName("A2:D4");//Get range from the notations
var data = dataRange.getValues();//get values of spreadsheet according to datarange fn
var boolean = false;
//take data from mobile spreadsheet
for(var i=0;i<data.length;i++){//for  loop for data 
                  var row = data[i];//array calling
                  var id1 = row[0];//id value
                  k++;//counter
                  if(id == id1){//checking id exist or not
                                var stock = row[3];//take stock data
                                var company_name =row[2];
                                var author_name=row[1];
                                if(stock > 0){//checking stocks value 
                                             var template = HtmlService.createTemplateFromFile('mail');
                                            //variabes of mail.html file
                                            template.main ="Mobile Report"//main variable of mail.html file 
                                            template.category= cat.toLowerCase();
                                           template.ID=ID;
                                           template.Name=Name;
                                           template.os=op;
                                           template.name = company_name;
                                           template.author = author_name;
                                           template.id = id;
                                           var html = template.evaluate().getContent();//recipient = Session.getActiveUser().getEmail();  
                                           GmailApp.sendEmail(emailaddress, "Shipping Details", 'Requires HTML', {htmlBody:html} );//message sending to form emailaddres
                                           stock--;//decremeant in stock variable
                                           var note =createNote(k);//calling closure&nbsp;function
                                           note.setNum(k);//calling closure&nbsp; function internal method setNum()
                                           var nota = note.getNum();//calling closure&nbsp;function internal method getNum()
                                           var range = sheet.getRange (nota, 4);
                                           // Logs "A1:E2"
                                           var s = range.getA1Notation();//get string value of curren column
                                           //Logger.log(s);//can be removed
                                           ss.getRange(s).setValue(stock);
                                           //Logger.log(s);//can be removed
                                          }
                                                   
                                 else //if stock is not available
                                     {
                                      GmailApp.sendEmail(emailaddress, "shipping details", " transaction failed");//if stock is not available
                                     }
                                       return boolean =true;//id exist 
                                       break;//break for "for in"loop
                                                }//end of first if of "for in" loop
                                             }//end of "for" loop
                                             
                                 if(boolean != true){ //checking boolean variable status 
                                              message="transaction failed"+"please enter valid id "+id;//message for invalid id
                                              GmailApp.sendEmail(emailaddress,'shipping details',message);
                                                   }
}

stock value

Description About Used Functions

  1. onFromSubmit(e) : This function response every time after form submission . In this function i am taking value of array using e.values and i save each value in different different variables. In this function i have used switch case because i have three categories .
    //Main Function 
    function onFromSubmit(e){
              var id = e.values[1];//take value of spreadsheet of 2 col latest
              var cat = e.values[2];//take value of spreadsheet of 3 col latest
              var emailaddress = e.values[3];//take value of spreadsheet of 4 col latest 
              var k=0;
              var idnum;
              var bool = idchk(id);
              if(bool === true){
                switch(cat){//starting of switch case
                            case 'MOBILE'://choose category
                                //GET Book SpreadSheet using spreadsheet id function
                               idnum="1IXBdZbdDEn-B5k__UUwzqf8efE16XtABw-NIJhLY0Po";
                               var Name="Company Name";
                               var ID="ID";
                               var os="Mobile Name";
                               SendMailtouser(idnum,k,id,emailaddress,cat,Name,ID,os);
                            break;//break the case of mobile
                            case 'BOOK':
                               //GET Book SpreadSheet using spreadsheet id function
                               idnum="1pYITJ7Ujxuo6nhQGWa5exaRLsByJ-6Hvpun-yCA0uHg";
                               var Name="Book Name";
                               var ID="ID";
                               var os="Author Name";
                               SendMailtouser(idnum,k,id,emailaddress,cat,Name,ID,os);
                            break;//break the case of Book
                            case 'LAPTOP':
                              //GET Book SpreadSheet using spreadsheet id function
                               idnum="1cXsTljmCR8tIcW8sU6bECFP6agzVviDIr7r5TnmunPI";
                               var Name="Laptop Name";
                               var ID="ID";
                               var os="Operating system";
                               SendMailtouser(idnum,k,id,emailaddress,cat,Name,ID,os);
                            break;//break the case of laptop
                            default:
                              var message = "choose again";
                              GmailApp.sendEmail(emailaddress,"Shipping details",message);
                            }//Main Function 
    function onFromSubmit(e){
     var id = e.values[1];//take value of spreadsheet of 2 col latest
     var cat = e.values[2];//take value of spreadsheet of 3 col latest
     var emailaddress = e.values[3];//take value of spreadsheet of 4 col latest 
     var k=0;
     var idnum;
     var bool = idchk(id);
     if(bool === true){
     switch(cat){//starting of switch case
     case 'MOBILE'://choose category
     //GET Book SpreadSheet using spreadsheet id function
     idnum="1IXBdZbdDEn-B5k__UUwzqf8efE16XtABw-NIJhLY0Po";
     var Name="Company Name";
     var ID="ID";
     var os="Mobile Name";
     SendMailtouser(idnum,k,id,emailaddress,cat,Name,ID,os);
     break;//break the case of mobile
     case 'BOOK':
     //GET Book SpreadSheet using spreadsheet id function
     idnum="1pYITJ7Ujxuo6nhQGWa5exaRLsByJ-6Hvpun-yCA0uHg";
     var Name="Book Name";
     var ID="ID";
     var os="Author Name";
     SendMailtouser(idnum,k,id,emailaddress,cat,Name,ID,os);
     break;//break the case of Book
     case 'LAPTOP':
     //GET Book SpreadSheet using spreadsheet id function
     idnum="1cXsTljmCR8tIcW8sU6bECFP6agzVviDIr7r5TnmunPI";
     var Name="Laptop Name";
     var ID="ID";
     var os="Operating system";
     SendMailtouser(idnum,k,id,emailaddress,cat,Name,ID,os);
     break;//break the case of laptop
     default:
     var message = "choose again";
     GmailApp.sendEmail(emailaddress,"Shipping details",message);
     }
     }
     else{//id length is not valid
     var message = "choose 5 digit id";
     GmailApp.sendEmail(emailaddress,"Shipping details",message);
     }
    
    }//end of onFromSubmit fn
                          }
              else{//id length is not valid
                  var message = "choose 5 digit id";
                  GmailApp.sendEmail(emailaddress,"Shipping details",message);
                  }
    
    }//end of onFrom Submit
  2. createNote(k) : In this function i pass the counter variable value .This is a closure function . Here i used closure function for taking current row index and i have created two inner function in this function body  and the name of those name is setNum and getNum and those functions are return integer value .
    //Closure Function for get value of notation                
      var createNote = function(no) {
      var num;
      return {
               getNum: function() {
                                    return num;
                                  },
               setNum: function(newNum){
                                       if(newNum == 1){
                                       num = 2;
                                       }
                                       else if(newNum ==2){
                                       num = 3;
                                       }
                                       else if(newNum ==3){
                                       num =4;
                                       }
        }
      }
    }
  3. idchk(id) : In this function i pass id which i take from from response spreesheet and by the helps of this function i convert my integer value into string using toSting() function of java-script ,for checking id length because in my categories spreadsheet id no. digits are 5 .
    function idchk(id){
                        var inode=id;
                        if(inode.toString().length ===5)
                        {
                         return true;
                        }
                        else
                        {
                          return false;
                        }
                      }
  4. SendMailtouser(idnum,k,id,emailaddress,cat,Name,ID,op): This very very important function because by the help of this function we call spreadsheet of different different categories and also by the help of this function we send email to user/buyer according to its submitted id and our store stock . And in this function i use many inbuilt functions Like SpreadsheetApp function ,getRange() etc.
    function SendMailtouser(idnum,k,id,emailaddress,cat,Name,ID,op){
               var key=idnum;
               var ID=ID;
               var k=k;
               var id=id;
               var emailaddress= emailaddress;
               var cat=cat;
               var op =op;
               var Name=Name;
               var ss = SpreadsheetApp.openById(key);//open mobile sheet using its id 
               var sheet = ss.getSheets()[0];//select sheet 0 means 1 first sheet  
               //var startRow = 2;//
              //var numRows = 4;//number of cloum
             // Fetch the range of cells A2:D4
              var dataRange = ss.getRangeByName("A2:D4");//Get range from the notations
              var data = dataRange.getValues();//get values of spreadsheet according to datarange fn
              var boolean = false;
            //take data from mobile spreadsheet
              for(var i=0;i<data.length;i++){//for  loop for data 
                                             var row = data[i];//array calling
                                             var id1 = row[0];//id value
                                             k++;//counter
                                             if(id == id1){//checking id exist or not
                                                    var stock = row[3];//take stock data
                                                    var company_name =row[2];
                                                    var author_name=row[1];
                                                    if(stock > 0){//checking stocks value 
                                                       var template = HtmlService.createTemplateFromFile('mail');
                                                       //variabes of mail.html file
                                                       template.main ="Mobile Report"//main variable of mail.html file 
                                                       template.category= cat.toLowerCase();
                                                       template.ID=ID;
                                                       template.Name=Name;
                                                       template.os=op;
                                                       template.name = company_name;
                                                       template.author = author_name;
                                                       template.id = id;
                                                       
                                                      var html = template.evaluate().getContent();  
                                                       GmailApp.sendEmail(emailaddress, "Shipping Details", 'Requires HTML', {htmlBody:html} );//message sending to form emailaddres
                                                       stock--;//decremeant in stock variable
                                                       var note =createNote(k);//calling Closure function
                                                       note.setNum(k);//calling colsuer function internal method setNum()
                                                       var nota = note.getNum();//calling colsuer function internal method getNum()
                                                       var range = sheet.getRange (nota, 4);
                                                       // Logs "A1:E2"
                                                       var s = range.getA1Notation();//get string value of curren column
                                                       //Logger.log(s);//can be removed
                                                       ss.getRange(s).setValue(stock);
                                                       //Logger.log(s);//can be removed
                                                        //ss.getRange(s).setValue(stock);
                                                        }
                                                       
                                                       else //if stock is not avaiable
                                                           {
                                                            GmailApp.sendEmail(emailaddress, "shipping details", " transaction failed");//if stock is not avaible
                                                           }
                                                      return boolean =true;//id exist 
                                                           
                                                      break;//break for "for in"loop
                                                    }//end of first if of "for in" loop
                                                 }//end of "for" loop
                                                 
                                     if(boolean != true)//checking boolean variable status 
                                                       {
                                                        message="transaction failed"+"please enter valid id "+id;//message for invalid id
                                                        
                                                        GmailApp.sendEmail(emailaddress,'shipping details',message);
                                                       }
    }
    

    SpreadsheetApp.openId(id) function is in built function of google script which open spreadsheet according to given id number and i store this in variable ss and then ss is a object of this sheet and after that i select the first of this spreadsheet using getSheets()[“index number”]. After that i pass the range of current spreadsheet into getRange(string) function and after this i check the id if id exist in spreadsheet it check stock or if id not exist it goes to else condition and send mail to user. if id exist and stock is greater than zero it send html mail to user for sending html email to user you need to call html file from spreadsheet fro this i am using HtmlService.createTemplateFromFile(‘File name’) then you need to evaluate this fro this i am using evaluate().getContent() which take content from html mail and after sending mail i call to closure function to get current row no. and then i pass it to getA1notation() function which convert integer value into string and then i call to in built function which setValue(“value”) which set the value of current column.

Final Output


Screenshot-from-2014-05-08-213801

 

 Summary


//Main Function 
function onFromSubmit(e){
var id = e.values[1];//take value of spreadsheet of 2 col latest
var cat = e.values[2];//take value of spreadsheet of 3 col latest
var emailaddress = e.values[3];//take value of spreadsheet of 4 col latest 
var k=0;
var idnum;
var bool = idchk(id);
if(bool === true){
    switch(cat){//starting of switch case
                case 'MOBILE'://choose category
                //GET mobile SpreadSheet using spreadsheet id function
                 idnum="1IXBdZbdDEn-B5k__UUwzqf8efE16XtABw-NIJhLY0Po";
                 var Name="Mobile Name";
                 var ID="ID";
                 var os="Company Name";
                 SendMailtouser(idnum,k,id,emailaddress,cat,Name,ID,os);
                break;//break the case of mobile
                case 'BOOK':
                //GET Book SpreadSheet using spreadsheet id function
                 idnum="1pYITJ7Ujxuo6nhQGWa5exaRLsByJ-6Hvpun-yCA0uHg";
                 var Name="Author";
                 var ID="ID";
                 var os="Book Name";
                 SendMailtouser(idnum,k,id,emailaddress,cat,Name,ID,os);
                break;//break the case of Book
                case 'LAPTOP':
                 //GET Book SpreadSheet using spreadsheet id function
                 idnum="1cXsTljmCR8tIcW8sU6bECFP6agzVviDIr7r5TnmunPI";
                 var Name="Laptop Name";
                 var ID="ID";
                 var os="Operating system";
                 SendMailtouser(idnum,k,id,emailaddress,cat,Name,ID,os);
                break;//break the case of laptop
                default:
                 var message = "choose again";
                 GmailApp.sendEmail(emailaddress,"Shipping details",message);
                        }
                      }
          else{//id length is not valid
              var message = "choose 5 digit id";
              GmailApp.sendEmail(emailaddress,"Shipping details",message);
              }
 
}//end of onFrom Submit
 
//function of check id length 
function idchk (id){
                    var inode=id;
                    if(inode.toString().length ===5)
                    {
                     return true;
                    }
                    else
                    {
                      return false;
                    }
                  }
                  
  //closure Function for get value of notation                
  var createNote = function(no) {
  var num;
  return {
           getNum: function() {
                                return num;
                              },
           setNum: function(newNum){
                                   if(newNum == 1){
                                   num = 2;
                                   }
                                   else if(newNum ==2){
                                   num = 3;
                                   }
                                   else if(newNum ==3){
                                   num =4;
                                   }
    }
  }
}
function SendMailtouser(idnum,k,id,emailaddress,cat,Name,ID,op){
var key=idnum;
var ID=ID;
var k=k;
var id=id;
var emailaddress= emailaddress;
var cat=cat;
var op =op;
var Name=Name;
var ss = SpreadsheetApp.openById(key);//open mobile sheet using its id 
var sheet = ss.getSheets()[0];//select sheet 0 means 1 first sheet  
// Fetch the range of cells A2:D4
var dataRange = ss.getRangeByName("A2:D4");//Get range from the notations
var data = dataRange.getValues();//get values of spreadsheet according to datarange fn
var boolean = false;
//take data from mobile spreadsheet
for(var i=0;i<data.length;i++){//for  loop for data 
                  var row = data[i];//array calling
                  var id1 = row[0];//id value
                  k++;//counter
                  if(id == id1){//checking id exist or not
                                var stock = row[3];//take stock data
                                var company_name =row[2];
                                var author_name=row[1];
                                if(stock > 0){//checking stocks value 
                                             var template = HtmlService.createTemplateFromFile('mail');
                                            //variabes of mail.html file
                                            template.main ="Mobile Report"//main variable of mail.html file 
                                            template.category= cat.toLowerCase();
                                           template.ID=ID;
                                           template.Name=Name;
                                           template.os=op;
                                           template.name = company_name;
                                           template.author = author_name;
                                           template.id = id;
                                           var html = template.evaluate().getContent();//recipient = Session.getActiveUser().getEmail();  
                                           GmailApp.sendEmail(emailaddress, "Shipping Details", 'Requires HTML', {htmlBody:html} );//message sending to form emailaddres
                                           stock--;//decremeant in stock variable
                                           var note =createNote(k);//calling closure&nbsp;function
                                           note.setNum(k);//calling closure&nbsp; function internal method setNum()
                                           var nota = note.getNum();//calling closure&nbsp;function internal method getNum()
                                           var range = sheet.getRange (nota, 4);
                                           // Logs "A1:E2"
                                           var s = range.getA1Notation();//get string value of curren column
                                           //Logger.log(s);//can be removed
                                           ss.getRange(s).setValue(stock);
                                           //Logger.log(s);//can be removed
                                          }
                                                   
                                 else //if stock is not available
                                     {
                                      GmailApp.sendEmail(emailaddress, "shipping details", " transaction failed");//if stock is not available
                                     }
                                       return boolean =true;//id exist 
                                       break;//break for "for in"loop
                                                }//end of first if of "for in" loop
                                             }//end of "for" loop
                                             
                                 if(boolean != true){ //checking boolean variable status 
                                              message="transaction failed"+"please enter valid id "+id;//message for invalid id
                                              GmailApp.sendEmail(emailaddress,'shipping details',message);
                                                   }
}
<div >
  <p> Transaction successful and you will receive the <?=category?> <p>
  <table style="border:1px dashed gray;background-color:#fff;color:#000;" width="325px"> 
        <thead>
           <tr>
             <th colspan="3" style="border-bottom:1px dashed gray;">Receipt</th>
           </tr>
           <tr >
             <th style="border-bottom:1px dashed gray;"><?= ID?></th>
             <th style="border-left:1px dashed gray;border-bottom:1px dashed gray;"><?=Name?></th>
             <th style="border-left:1px dashed gray;border-bottom:1px dashed gray;"><?=os?></th>
           </tr>
            </thead>
            <tbody>
                <tr>
                    <th><?=id ?></th>
                    <th style="border-left:1px dashed gray;"><?=name ?></th>
                    <th style="border-left:1px dashed gray;"><?=author ?></th> 
                </tr>
            </tbody>
   </table>
</div>
0
0

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: