Motivation

Hi,it's Kei, front-end-developer.Today,I'm sharing with how to generate spreadsheet and send notification to Slack with GAS(Google Apps Script) automatically.This tips would help you to improve your daily productivity.

Every day, we give team members our latest progress and share the progress with spreadsheet. So, we needed to make spreadsheet every day and notify the spreadsheet to related persons. This is waisting time!! That's why I created the system to generate spreadsheet and send notification to Slack with GAS.

Change the new sheet name

First of all, you generate spreadsheet like this. For this article, I made the table that share latest progress and problem with team member.



Open script editor (tool > script editor) and paste the code below on the editor.

function sheetCopy() {
  // Get active spread sheets
  let mySpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // Activate all the way to the right
  mySpreadsheet.getSheets()[0].activate()

  let copySheet = mySpreadsheet.getActiveSheet();

  // Duplicate all the way to the right sheet
  let newSheet = copySheet.copyTo(mySpreadsheet);

  // Change sheet name with new sheet
  const today = new Date();
  const year = today.getFullYear().toString();
  const month = (today.getMonth() + 1).toString();
  const date= today.getDate().toString();
  const title = `${year}/${month}/${date}`;
  newSheet.setName(title)

  // Clear the sheet of contents
  newSheet.getRange('A2:E3').clearContent()

  // Activate the new sheet
  moveActiveSheet(mySpreadsheet,newSheet )

  // Notify to Slack
  notifyToSlack(mySpreadsheet)
}

Activate the new sheet

function moveActiveSheet(spreadSheet,newSpreadSheet) {
  spreadSheet.setActiveSheet(newSpreadSheet)
  spreadSheet.moveActiveSheet(1);
  return newSpreadSheet
}

Notify to Slack

You can find "Slack ID" to check view profile >> more >> Copy member ID.

function notifyToSlack(mySheet){
  const URL = mySheet.getUrl()

  // Message
  let contents = `
  <@Slack ID> 
  Hey Guys！！ It's It's 5 to 18. 
 Please prepare with the meeting.
 Spreadsheet：${URL}
  `

  let options = {
    "method" : "post",
    "contentType" : "application/json",
    "payload" : JSON.stringify(
      {
        "text" : contents,
        "icon_emoji": ":alien",
        "username": "Notification Bot",
        link_names: 1
      }
    )
  };

  UrlFetchApp.fetch(CHANNEL_URL_PRODUCTION, options);
}

Set up new trigger

// Set up trigger for sheetCopy function
function setTrigger(){
  const time = new Date()
  time.setHours(17)
  time.setMinutes(55)
  ScriptApp.newTrigger('sheetCopy').timeBased().at(time).create()
}

Showing all codes in this article

function setTrigger(){
  const time = new Date()
  time.setHours(17)
  time.setMinutes(55)
  ScriptApp.newTrigger('sheetCopy').timeBased().at(time).create()
}

const CHANNEL_URL_TEST = "webhook URL for test"
const CHANNEL_URL_PRODUCTION = "webhook URL for production"

function sheetCopy() {
  // Get active spread sheets
  let mySpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // Activate all the way to the right
  mySpreadsheet.getSheets()[0].activate()

  let copySheet = mySpreadsheet.getActiveSheet();

  // Duplicate all the way to the right sheet
  let newSheet = copySheet.copyTo(mySpreadsheet);

  // Change sheet name with new sheet
  const today = new Date();
  const year = today.getFullYear().toString();
  const month = (today.getMonth() + 1).toString();
  const date= today.getDate().toString();
  const title = `${year}/${month}/${date}`;
  newSheet.setName(title)

  // Clear the sheet of contents
  newSheet.getRange('A2:E3').clearContent()

  // Activate the new sheet
  moveActiveSheet(mySpreadsheet,newSheet )

  // Notify to Slack
  notifyToSlack(mySpreadsheet)
}

function moveActiveSheet(spreadSheet,newSpreadSheet) {
  spreadSheet.setActiveSheet(newSpreadSheet)
  spreadSheet.moveActiveSheet(1);
  return newSpreadSheet
}

function notifyToSlack(mySheet){
  const URL = mySheet.getUrl()

  // Message
  let contents = `
  <@Slack ID> 
  Hey Guys！！ It's It's 5 to 18. 
 Please prepare with the meeting.
 Spreadsheet：${URL}
  `

  let options = {
    "method" : "post",
    "contentType" : "application/json",
    "payload" : JSON.stringify(
      {
        "text" : contents,
        "icon_emoji": ":alien",
        "username": "Notification Bot",
        link_names: 1
      }
    )
  };

  UrlFetchApp.fetch(CHANNEL_URL_PRODUCTION, options);
}