dgtlmart - A Digital Performance Marketing Company in India

Working with Google Spreadsheet

How to integrate SMS in your Google sheet?

Google spreadsheet editor provide platform to integrate SMS API’s using scripting.

Below is the code can be used to integrated SMS API’s and send Email.

                           function smsemailreminder() {

                          var ss = SpreadsheetApp.getActiveSpreadsheet();

                          var sheet1 = ss.getSheetByName(“sheetname”);

                          var range1 = sheet1.getRange(1, 1, 70, 60);

                          var values1 = range1.getValues();

                          var tol = values1[1][17];  // Phone number from Google Sheet call

                         var msg = values1[1][1]; // SMS message content from sheet cell

                             var url = “HTTP API’s to call SMS”;

                          var response = UrlFetchApp.fetch(url);

                            Logger.log(response.getContentText());

                             var emailAddress = values1[1][16];

                            if(emailAddress !== “”){

                             MailApp.sendEmail(emailAddress, “Subject”, “MSG”);

                                     }} 

 

 

Google Spreadsheet Code alternative to importrange formula

                     function importramgealt()

                          {

                      // open the spreadsheets where the data will be stored Part1

                           var sourceTable = SpreadsheetApp.openById(“Spreadsheet1”); // source spreadsheet

                                  var srcSheet = sourceTable.getSheetByName(“Sheet1”);

                                 var targetTable = SpreadsheetApp.openById(“Spreadsheet2”); // target spreadsheet

                        var tarSheet = targetTable.getSheetByName(“Sheet2”);

                         // get the last row in the source sheet

                           var lastRowSource = srcSheet.getLastRow();

                            var lastCol = “AF”;

                               // read the source into an array

                            var aSrc = srcSheet.getRange(“A1:” + lastCol + lastRowSource).getValues();

                               // save src array to destination

                             tarSheet.getRange(“A1:” + lastCol + lastRowSource).setValues(aSrc);

                                   }

 

Google Sheet Script function to check empty cell

 

                                  function checkempty() {

                                      var m=0;

                                     var n=0;

                                  var ss = SpreadsheetApp.getActiveSpreadsheet();

                                 var sheet1 = ss.getSheetByName(‘Sheet1’);

                                var range1 = sheet1.getRange(1, 1,60, 30);

                               var values1 = range1.getValues();

                               for (var k = 1; k < 29; k++)

                                  {

                                    if(values1[k][0] !== “”){

                               if(values1[k][10] === “” || values1[k][11] === “”|| values1[k][12] === “”){

                                values1[k][25]=”Cell Empty”;

                                         }

                                          range1.setValues(values1);

                                     }}}

 

Google SpreadSheet Function for automatic reminders on particular day or time

                                       function callremindermin() {

                                           var ss = SpreadsheetApp.getActiveSpreadsheet();

                                            var sheet1 = ss.getSheetByName(‘Sheet1’);

                                           var range1 = sheet1.getRange(1, 1, 60, 60);

                                               var values1 = range1.getValues();

                                                  var email=values1[2][6];

                                                  var ph=values1[2][8];

                                           var url = “SMS HTTP Api”; // Send SMS for Meeting

                                                 var response = UrlFetchApp.fetch(url);

                                               Logger.log(response.getContentText());

                                           MailApp.sendEmail(email, “Meeting Subject”, “EMAIL boby”); 

                                                      }  

 

Go to EDIT—->current project triggers —->Add new trigger —-> select type and save.