Disclaimer : in this article I am waiting for examples of code that will cause you questions, doubts, perhaps even bloody tears and blindness. In justification for it, there is only one thing to be said - this code worked like Papa Carlo, and fulfilled its mission 100%, and some scripts still work.
First Dodo Pizza Place Rating
One of the main principles of our company - that which cannot be measured, does not exist . Pizza exists, which means you need to somehow evaluate its quality.
It all started in 2013. At that time, there were 7 pizzerias on the network. Then in Dodo they decided to launch a project to control product quality and standards - a rating of pizzerias open to all partners.
He looked like that.
The data was taken from customer calls, reviews in the social. networks and a bit of Dodo IS (our information system).
An obvious plus at that time - the rating began to drive the managing pizzerias to work on quality in order to be on top.
Second Mystery Shop Pizzerias Rating
About a year later, we realized that we needed to evaluate the product itself and took the well-known model - “mystery shoppers”. At that moment, we made a breakthrough - the first in Russia decided that our customers would be the mystery shoppers.
The emergence of a closed group VK
Initially, we found out how they work with mystery shoppers in agencies that provide such services. It turned out that they communicate via email or call directly to offer verification.
This option did not immediately seduce us. We were definitely not going to call, because for us it was a long time, but for a client it was painful and on a par with spam. Mail was used for mass mailings, but they were soon abandoned.
We came up with:
- For communication with secret agents and coordination of checks, they used the usual page in VK.
- To collect photo reports, they made a group in VK with an open wall.
- And for service surveys, we chose Google forms. And what? Conveniently, you don’t need to cut a separate service, and it is well displayed on the mobile phone.
- All instructions were in Google Docs.
Everything is on the knee. Everything is as it should be for a startup.
Photo from a mystery shopper. Yes, they measured the width with a ruler, cut the pizza rim to show the bakedness and quality of the dough.
More examples from photo reports from 2015 are here. 
The rating was constantly changing, new evaluation criteria were added. For example, this is 131 points for evaluating the Abakan-1 pizzeria.
The whole process hell was that all violations in the points were noted manually, no automation. And all this was done by one employee.
The magic of Google spreadsheets
In 2016, we learned (matured) that humanity has long invented functions other than = SUM. And what can be done with the help of scripts and data import functions between tables ... Direct CRM build.
For example, to evaluate each pizzeria in the table was a sheet, and in it evaluation criteria and scores. 1 pizzeria = 1 leaf. The more pizzerias, the more sheets. Every week a new sign. By pressing the magic button, the template sheet in the table was expanded into 120 sheets. Wah, how good!
This stage of automation “Dodo Controlling” took about six months. We moved step by step: we learned something new - implemented, we learned something else new - introduced again.
Limitation as a driver of progress
From the very beginning, we talked with mystery shoppers on behalf of a personal account in VK. And then came the day when we were faced with unsolvable problems of personal pages:
- there is a limit of 10,000 friends;
- there is a limit on sending messages: sometimes we could not send messages, because VK blocked us with the wording “you sent too many messages, come tomorrow”.
Usually VK blocked sending messages around 16:00. It was a happy hour when the team could rest.
Gradually, we ran into almost all the limitations of the VK and almost managed to stop loving this site. But a miracle happened, and VK gave groups the opportunity to connect message widgets, chat bots and other goodies.
In fact, this possibility was earlier than December 2016, but with a critical limitation for us: the group could reply to the message only within 10 days from the moment of sending the last message from the client. When they removed this restriction, the life of our team began to sparkle with new colors. Since then, we have begun the path of automation using the VK API and Google spreadsheets.
Fear and Loathing in a User Script
Initially, to become a mystery shopper, you had to fill out a form, then apply to the group, write us a code word and wait for us to respond. This wait could last up to 48 hours. It was indecently long and the desire to conduct checks after such registration faded away.
With a new store of knowledge, we set about automating registration.
We had several API methods, one form, several dozens of tables, a landing page, a bot in PHP, scripts in the table that were written by the coolest marketer Dodo and the first CFO of Dodo in one person, dozens of message templates for different situations. Then I did not even guess that this is called a user script:
- Mystery shopper filled out a questionnaire (this is an outdated version and now it does not work).
- The data from the form first fell into the database, then into the table. The bot first knocked on the database to check the data on the questionnaire, because the response timeout of the plate in tens of thousands of lines was large, and the database digested it easily).
- After filling out the questionnaire in the form, a message appeared on the screen: “Thank you, it remains to join the group (link) and write the code word“ Sherlock ””.
- Next, the mystery shopper sent an application to join the group and wrote the code word “Sherlock”. The word was the trigger to run the validation script:
- a request was sent to the database whether the agent is suitable for us by age;
- if so, then the person was added to the groups, and in the table opposite the candidate’s questionnaire, the status was ok / not ok;
- More data was painted in turquoise color and it was important. So it was easier to see with our eyes who is right for us and who we can talk to. The script painted inappropriate profiles red;
- Further, the secret buyer was automatically accepted in the group, and a message was sent to the chat with further steps and instructions.
Such is the automation of registration. Now the whole procedure has become almost simple and understandable.
Further, everything went like clockwork. It became clear that we can send messages through tables directly to PM to secret agents via group messages.
And there were just 46 lines of code!
function send() { var range = SpreadsheetApp.getActiveSpreadsheet().getActiveRange(); var ss = range.getValues(); var carray = range.offset(0, -2).getValues(); var marray = range.offset(0, 1).getValues(); var iarray = range.offset(0, 0).getValues(); ss.forEach(function (r, i) { var tt = range.getRowIndex(); var add = tt + i; var check = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('L' + add).getValue(); if (check != '') { SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('K' + add).setValue("").setBackground("#ffff00"); return; } var payload = { "message" : marray[i][0], "user_id" : r[0], "access_token" : " ", "v" : "5.74" }; var options = { // http- "method" : "post", "header" : "Content-type: application/x-www-form-urlencoded", "payload" : payload, "muteHttpExceptions" : true, }; var jsonData = JSON.parse(UrlFetchApp.fetch("https://api.vk.com/method/messages.send", options).getContentText()); if (jsonData['error'] != undefined) { SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('K' + add).setValue("").setBackground("#ff0000"); } Logger.log(jsonData); var log = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Log'); var int = log.getRange('A1').getValue(); var d = new Date(); log.getRange('B' + int).setValue(d); log.getRange('C' + int).setValue(r[0] + ""); log.getRange('A1').setValue(int + 1); Utilities.sleep(400); }); }
In the tables, we generated the message text itself and made personalized mailing using the id page. We also stopped copying and sending messages manually. At peak times, we could send up to 3000 messages in 45 minutes using tables. If we did this by hand, I would not write this article, but would continue to send messages.
Next, I give an example of such a miracle tablet. I tried very hard to make a clear and functional UX / UI. By clicking on the red button, a message distribution was launched with a proposal to conduct a secret check.
More features to automate hell processes
The company has many Google forms that help us collect some metrics from pizzerias on an ongoing basis. Each form has a list of pizzerias that need to be kept up to date. There are 53 such forms in total, each of them was previously updated manually.
A little tired of such work, we realized that this business could be automated. I again turned to our marketer, after an hour or two everything was ready and just 47 lines of code.
function myFunction() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var range = ss.getActiveRange(); var forms = range.offset(0, 1).getValues(); var items = range.offset(0, 2).getValues(); var sources = range.offset(0, 3).getValues(); var ranges = range.offset(0, 4).getValues(); forms.forEach(function (r,i) { var form = FormApp.openById(forms[i]); var values = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sources[i]).getRange(ranges[i]).getValues(); var arr = []; values.forEach(function (el,ei) { if (el[0] != '') { arr.push(el[0]); } }); var item = form.getItems()[Number(items[i])].asListItem(); item.setChoiceValues(arr); Logger.log(item.getTitle()); }); } function getData() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var range = ss.getSheetByName(" ").getRange("A2:C").getValues(); Logger.log(range); } function onOpen() { var ui = SpreadsheetApp.getUi(); // Or DocumentApp or FormApp. ui.createMenu('Custom Menu') .addItem('Change', 'myFunction') .addToUi(); } function update() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var range = ss.getSheetByName(" ").getRange("A2:A"); range.activate(); myFunction(); } function createTimeDrivenTriggers() { ScriptApp.newTrigger('update').timeBased().everyDays(1).create(); }
The forms are different, with different lists of pizzerias and the location of the list of pizzerias in the form. Somewhere we need foreign, somewhere from Russia.
Columns B and C tell the script in which form to update and in which place the form displays the list. And columns D and E, from which sheet and range you need to take the list for updating.
Next, we set a trigger to run the script once a day, and thus were able to defeat the hell process of updating forms first for our department, then for the whole company. This script is still working.
The most difficult snail case
Once we wanted to receive audio recordings from delivery checks.
Where did the audio recording come from: mystery shoppers recorded the courier’s meeting with the client as an audio message in VK. Usually this is 10-15 seconds of audio recording.
How we got them: they took the last 20 messages on the mystery shopper’s VK address, searched for the word “Ulica” among them, then stepped back one message and took away the link to the audio recording. (Spoiler: not the best word, often they wrote us the word "Snail").
There are already more lines of code, get ready.
function getLastAudio() { var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var token = ''; var range = ss.getActiveRange().getValues(); var tt = ss.getActiveRange().getRowIndex(); range.forEach(function (r,i) { var ri = tt + i; var cuid = r[0]; var ci = 0; var payload1 = { "q" : '', 'peer_id' : r[0], "access_token" : token, "v" : "5.73", }; var options1 = { // http- "method" : "post", "header" : "Content-type: application/x-www-form-urlencoded", "payload" : payload1, "muteHttpExceptions" : true }; var jsonData = JSON.parse(UrlFetchApp.fetch("https://api.vk.com/method/messages.search", options1).getContentText()); Logger.log(jsonData); if (jsonData['response']['items'] == undefined) { ss.getRange("B" + ri).setValue(' ').setBackground('#f00'); return; } var cmid = jsonData['response']['items'][0]['id']; var date = new Date((jsonData['response']['items'][0]['date']*1000)); var fdate = Utilities.formatDate(date, "GMT+3", "dd-MM-yyyy HH:mm:ss"); ss.getRange("B" + ri).setValue(fdate); var payload2 = { "user_id" : cuid, "count" : '20', "access_token" : token, "v" : "5.73", }; var options2 = { // http- "method" : "post", "header" : "Content-type: application/x-www-form-urlencoded", "payload" : payload2, "muteHttpExceptions" : true }; var jsonData2 = JSON.parse(UrlFetchApp.fetch("https://api.vk.com/method/messages.getHistory", options2).getContentText()); Logger.log(jsonData2); jsonData2['response']['items'].forEach(function (r,i) { if (r['id'] == cmid) { ci = i + 1; Logger.log(jsonData2['response']['items'][ci]); if (jsonData2['response']['items'][ci] == undefined) { ss.getRange("C" + ri).setValue(" "); } else { if (jsonData2['response']['items'][ci] != undefined && jsonData2['response']['items'][ci]['attachments'] != undefined && jsonData2['response']['items'][ci]['attachments'][0]['doc'] != undefined && jsonData2['response']['items'][ci]['attachments'][0]['doc']['url'] != undefined) { ss.getRange("C" + ri).setValue(jsonData2['response']['items'][ci]['attachments'][0]['doc']['url']); } else { ss.getRange("C" + ri).setValue(jsonData2['response']['items'][ci]['body']); } } } }); }); }
Google spreadsheets are not rubbery, and I am a so-so encoder
So everything was spinning and spinning, until we grew up to 60 thousand mystery shoppers by the beginning of 2018.
So, we have already run into table restrictions. Then one table could contain no more than 2 million cells, and we used 1.6 million cells in one of the busiest department labels, of which 135 thousand cells had all kinds of table functions.
Such a backend. Everything terribly slowed down and when several people worked together, the plate gave out "The document is too popular, come back later."
Then it became clear that I am a bad encoder, the system does not hold the load and I need to be replaced.
And then the real developer came ...
But this is a completely different story and we will write about it later. I hope that these examples will help managers in their projects, where there are many hellish processes. Processes can and should be automated, both tables and zapier (we also used it) can help, and house solutions if there are resources, we did not have them at the beginning.
If you have a person in the company who knows something about scripts, then it suits you for organizing hell processes in tablets. If you yourself want to learn something like this, Telegram has an excellent channel for this, from which I took information about functions and scripts.
Well, masthead for all managers - knowing the tables every day saves time when working with data and allows you to think and understand at least a little what the developer does with the data and what you can do with the data in general.