SMS mailing with Google Sheets API

Recently, a freelance-employer friend has received a proposal for a small job related to the organization of SMS-mailing, the data for which should be taken from the .xls-table. Plus, there was an optional wish to submit the same table to the corporate website with the obligatory possibility of editing it. At the same time, a very small budget was voiced for a maximum of two man-days.

Minimal research of existing web-based tools for working with spreadsheets has shown that development based on them will take time many times longer. For example, a bunch of PHPExcel and some AJAX-tables poured into the development of the functionality of a full-fledged site. Besides requiring a separate hosting for security purposes. Theoretically, MS Excel + VB script was considered theoretically, but even here there were its own pitfalls. As a result, the choice fell on Google Sheets.

First you need to have a google account. Next, go into it, go to http://sheets.google.com/ . Here you can download the .xls file. After loading the table, the first SMS sending script was written. It is the formation and sending of a specific http POST request.

function sendHttpOneSms(msgStr, receiver) { var payload = { //   sms- "user" : "xxxx", "pass" : "*******", "action": "post_sms", "message": msgStr, "target": receiver }; var options = { //   http- "method" : "post", "payload" : payload, "muteHttpExceptions" : true }; var result = UrlFetchApp.fetch("http://-/sendsms/", options); Logger.log(result.getContentText()); } 

Further we form the elementary daily trigger.
')

 function dailySend() { var sendText = "  "; var sheet = SpreadsheetApp.getActiveSheet(); var data = sheet.getDataRange().getValues(); //      var dateToday = new Date(); //    for(var i=0; i<data.length; i++) //     { var dateSend = parseDate(data[i][3]); if(compareDate(dateToday, dateSend)) //     ,  sendHttpOneSms(sendText, data[i][5]); //    } } 

There are two functions in this trigger. The first is simple - compareDate. Her task is to compare two dates without considering time.

The second function is more complicated, and, despite the small volume, it took me quite a lot of time to write it. The fact is that when I tried to read the date from the cell, I received an incomprehensible five-digit number that was not automatically converted to any other type. Google API here impudently lied, saying that there must be an object of type Date. And here is not a Date, and not even a string, which could also be expected, but a number. It obviously did not correspond to the unix format (the number of milliseconds since midnight on January 1, 1970). It was empirically established that the number most likely represents the number of days from January 1, 1900. After this, the function of converting the date to a digestible format became apparent:

 function parseDate(dec) { return new Date((dec-70*365-19)*24*3600*1000); } 

Now we have to run our trigger. This can be done using the “Triggers of the current project” window (called when you click on the clock image under the menu). You can also do this programmatically by running the script:

 ScriptApp.newTrigger("dailySend") .timeBased() //   -   .atHour(12) //   -  12  .everyDays(1) //   -    .create(); 

useful links

Google Spreadsheet API
Class ClockTriggerBuilder
Google class UrlFetchApp to use http requests