Geekly Articles each Day
📜 ⬆️ ⬇️

How to make friends Google Drive and Google Calendar? We taste Gas

GAS Shopping Cart I offer the readers of "Habrakhabr" a story about how I managed to increase my purchasing power in a crisis time using Google Apps Script (GAS). Under the cat you will find a non-standard example of use and learn about the possibilities of working with the Google Drive and Google Calendar services.

It all started with the fact that the price of imported goods in Russia has steadily increased. Working as an IT specialist in a large retail network, I had an idea about how you can take advantage of having access to the product base without violating the established rules of the company and, all the more, of the law. Of greatest interest in terms of saving personal money are the so-called “promotions” (discounted rates). It was necessary to organize the daily unloading of "promotions" for the goods of interest and to realize the possibility of receiving timely notifications about the availability of discounts on the Android-smartphone.

Obviously, there are many options for implementing the necessary functionality. The options for creating a console application in any language (for example, Java) that connects to the database and then send SMS via the HTTP API of any of the existing free distribution services were considered. However, I wanted to maximize the use of existing tools, including Google’s corporate service (Mail, Disk, Calendar, etc.).

To achieve the desired result it was necessary to solve the following tasks:
')
  1. Upload data about promotions to a file;
  2. Process a file, add an event to the Google Calendar and set up an SMS alert;
  3. Schedule tasks for daily automatic execution.

Chapter 1. SQLless


There is a goal, there is a distance. The rest is details.

The first point is reduced to writing a non-complex SQL query, which unloads the actual on the day of the promotion for the list of products indicated in the request. In order to simplify this stage as much as possible and not to write a single line of code, it was decided to use native utilities for working with databases from the manufacturer. The support for uploading to CSV is present out of the box, which fully meets the requirements for the task. It was enough to compile a valid SQL query and create a configuration file for it from the database unloading utility. As a result, we have a configuration file, when launched, we get a ready-made CSV file at the output with promotions that are current for the day.


Chapter 2. Fifty shades of GAS


The main task at this stage was to add information from a CSV file to the Google Calendar. Google Apps Script (GAS) provides access to the API of many Google services (Drive, Mail, Calendar, Docs, etc.) using a language based on JavaScript. Thus, for example, you can automate the daily sending of any logs or other files from your Google Drive to Google Mail. This is just one of many possible uses.

I don’t like to search for complex paths, so when uploading a CSV file, we’ll save it directly in the Google Drive folder that is synchronized on the computer. Thanks to this elegant solution, the file almost immediately after saving is available to us from GAS. It remains to read the file, add the event to Google Calendar and enable SMS notification for it.
I will write exactly 30 lines of code. Count aloud ...


function addTarifsFromCSVtoCalendar() { var csvFiles = DriveApp.getFilesByName("TarifNotifier.csv"); if(csvFiles.hasNext()) { var csvFile = csvFiles.next(); var lastModDate = csvFile.getLastUpdated(); var currentDate = new Date(); lastModDate.setHours(0,0,0,0); currentDate.setHours(0,0,0,0); if (lastModDate.getTime() == currentDate.getTime()) { var csvData = csvFile.getBlob().getDataAsString("windows-1251"); var csv2DArray = Utilities.parseCsv(csvData, ','); var calendars = CalendarApp.getCalendarsByName(""); if(calendars.length == 0) { var calendar = CalendarApp.createCalendar(""); } else var calendar = calendars[0]; var events = calendar.getEventsForDay(currentDate); for (var i=0; i<events.length; i++) events[i].deleteEvent(); startTime = new Date(); endTime = new Date(); startTime.setHours(09,30,0,0); endTime.setHours(22,0,0,0); for (var i=0; i<csv2DArray.length; i++) { var event = calendar.createEvent(csv2DArray[i][0] + ' - ' + csv2DArray[i][1], startTime, endTime); event.setDescription(csv2DArray[i]); event.addSmsReminder(30); } } else Logger.log("No updated data!"); } }

So, let's look at what the code is and how it works:

 var csvFiles = DriveApp.getFilesByName("TarifNotifier.csv"); if(csvFiles.hasNext()) { var csvFile = csvFiles.next(); ... }

Access the file with the name TarifNotifier.csv through the Google Drive API. Since files with the same name can be stored in different directories, the variable csvFiles will be passed to an array of all files with the name TarifNotifier.csv . But we know that we have only one file with that name, so we simply appeal to the very first element from the array, if present.

 var lastModDate = csvFile.getLastUpdated(); var currentDate = new Date(); lastModDate.setHours(0,0,0,0); currentDate.setHours(0,0,0,0); if (lastModDate.getTime() == currentDate.getTime()) { ... }

Check the date of the last update of the CSV file and compare it with the current date. The setHours (0,0,0,0) method is used to reset the time in variables, which will allow us to consider only the date when comparing. Promotions are changed only once a day, so if the CSV file has not been updated “today”, this means that there are no new promotions of interest to us in the store.

 var csvData = csvFile.getBlob().getDataAsString("windows-1251"); var csv2DArray = Utilities.parseCsv(csvData, ',');

We read data from a CSV file and parse it into a two-dimensional array. We will need this in order to display each item as a separate event in the calendar.

 var calendars = CalendarApp.getCalendarsByName(""); if(calendars.length == 0) { var calendar = CalendarApp.createCalendar(""); } else var calendar = calendars[0]; var events = calendar.getEventsForDay(currentDate); for (var i=0; i<events.length; i++) events[i].deleteEvent();

Check the availability of the calendar with the name "Discounts" and if it does not exist, then create a new one. Again, since there may be several calendars with the same name, a reference to the array will be passed to the calendars variable. We have already determined that the calendar with the name "Discounts" we have one? So, he is alone. In case the script has already been launched earlier that day, clear the calendar of all events for the current day in order to avoid duplication.

 startTime = new Date(); startTime.setHours(09,30,0,0); endTime = new Date(); endTime.setHours(22,0,0,0); for (var i=0; i<csv2DArray.length; i++) { var event = calendar.createEvent(csv2DArray[i][0] + ' - ' + csv2DArray[i][1], startTime, endTime); event.setDescription(csv2DArray[i]); event.addSmsReminder(30); }

Actually for what it all began. For each row of the CSV file (for each product) we create a new event in the calendar from 09:30 to 22:00 (store time) and set an SMS notification for it, which should work exactly 30 minutes before the event begins.

Chapter 3. Schedueler's list


This list is very good. This list is life itself. And at the edges of it - a solid abyss.

To get the desired result, it remains the smallest - to schedule tasks for daily execution:
  1. We are planning a daily launch of the CSV file upload to Google Drive at 07:30 through the Windows Task Scheduler;
  2. Use the Project Triggers scheduler in Google Apps Script to set up the daily launch of our addTarifsFromCSVtoCalendar () function from 08:00 to 09:00 (Google does not allow you to specify the exact start time).



As a result, the information on fresh promotional rates is updated daily in the calendar of the Android phone by 9 am. At the same time, an SMS notification is sent to the phone associated with the Google account.


Crisis and sanctions are not so terrible now!

If you are interested in this topic, in the following articles I can share other examples of using Google Apps Script:


Links


Source: https://habr.com/ru/post/253293/

More articles:


All Articles