Google SpreadSheet + Google Analytics + Slackbot

Small start-ups are difficult to set up daily to check the status data of their products. So it becomes dependent on Google Analytics and Firebase, and there is no big change in the data it sees every day. I wanted to receive only the data that I must see on the Slack channel every day. It looks like bellow.

 

Daily GA Slack bot @DeepNatural In

I had to implement this function myself because the engineer was blank in the squad where I was on the production floor and it was difficult to get help from other product teams. I decided to challenge ourselves because we thought it would be possible to use the script function of a spreadsheet that we have been using frequently.

Install the Google Analytics report through Add-ons on the spreadsheet.

It is easier to install by referring to the link below.

https://developers.google.com/analytics/solutions/google-analytics-spreadsheet-add-on

Google Analytics Spreadsheet Add-on

 

Create a new project and run report. This produces several sheets, and I created one more additional sheet for connection with the slacks.

And I wrote a Google App script referring to previous work I did to link with Slack and load data. I had experience connecting vacation records to spread sheets or making slack bots that issue documents into spread sheets, so I could make it easy.

 

2020/04/17 - [프로덕트 매니지먼트] - 구글 스프레드 시트로 주기별 SQL 데이터 자동으로 수집하기

구글 스프레드 시트로 주기별 SQL 데이터 자동으로 수집하기

The following code is implemented, starting with declaring a sheet that is basically used. This is the part of the function that pulls data from other sheets to the sheet that you want to aggregate. The reason why this is necessary is to manage the history.

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("report");
var lastRow = sheet.getLastRow();
var i = lastRow + 1;
var eventBefore = "Event 2days ago Report"
var eventAfter = "Event Yesterday Report"
var sessionBefore = "Session 2days ago Report"
var sessionAfter = "Session Yesterday Report"
var userBefore = "User 2days ago Report"
var userAfter = "User Yesterday Report"
var range = "A12"
var logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(eventBefore);
date = sheet.getRange(i, 1); // A
user = sheet.getRange(i, 2); // B
session = sheet.getRange(i, 3); // C
event = sheet.getRange(i, 4); // D

The important part of the code below is the postResponse part, which is connected to the slack encoding webhook.

function getReport() {
  setDate();
  getUser();
  getSession();
  getEvent();
  postResponse(Utilities.formatDate(new Date(), "GMT+1", "''dd"), user.getValue(), session.getValue(), event.getValue());
}

function setDate() {
  date.setValue(Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy"));
}

function getEvent() {
  beforeValue = getValue(eventBefore);
  afterValue = getValue(eventAfter);
  
  comparedValue = afterValue - beforeValue;
  
  rate = (comparedValue * 100)/beforeValue;
  
  var value = '';
  
  if (isNaN(rate)) {
    value = comparedValue + '(0%)';
  } else {
    value = comparedValue + '(' + rate + '%' + ')';
  }

  event.setValue(value);
}

function getSession() {
  beforeValue = getValue(sessionBefore);
  afterValue = getValue(sessionAfter);
  
  comparedValue = afterValue - beforeValue;
  
  rate = (comparedValue * 100)/beforeValue;
  
  var value = '';
  
  if (isNaN(rate)) {
    value = comparedValue + '(0%)';
  } else {
    value = comparedValue + '(' + rate + '%' + ')';
  }

  session.setValue(value);
}


function getUser() {
  beforeValue = getValue(userBefore);
  afterValue = getValue(userAfter);
  
  comparedValue = afterValue - beforeValue;
  
  rate = (comparedValue * 100)/beforeValue;
  
  var value = '';
  
  if (isNaN(rate)) {
    value = comparedValue + '(0%)';
  } else {
    value = comparedValue + '(' + rate + '%' + ')';
  }

  user.setValue(value);
}

function getValue(sheetName) {
  var logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  return logSheet.getRange(range).getValue();
}

function postResponse(date, user, session, event) {

  var payload = {
    "channel": "#" + '[Channel Name]',
    "username": "[Bot Name]",
    "icon_emoji": ":mag:",
    "link_names": 1,
    "attachments":[
       {
          "fallback": "[Notification Message Preview]",
          "pretext": date + "일" + " 성장보고:rocket:",
          "mrkdwn_in": ["pretext"],
          "color": "#49A8E4",
          "fields":[
             {
                "title":"전일 대비 방문자 수",
                "value": user,
                "short":false
             },
             {
                "title":"전일 대비 세션 수",
                "value": session,
                "short":false
             },
             {
                "title":"전일 대비 문의 수",
                "value": event,
                "short":false
             }
          ]
       }
    ]
  };

Logger.log(payload);
  var url = '[Your Slack Bot URL]';
  var options = {
    'method': 'post',
    'payload': JSON.stringify(payload)
  };

  return UrlFetchApp.fetch(url,options);
}

If implemented in this way, a bot that connects Google Analytics, spreadsheet, and slack will be finally completed.

 

This is a machine-translated article.