Google Action Script 介紹

透過程式碼寄送Email

function myFunction() {
   var now = new Date();
  
  GmailApp.sendEmail("justgps@gmail.com", "current time", "The time is: " + now.toString());
}

SpreadSeet 

function testSheet() {
  var sh = SpreadsheetApp.openById("0009ii4vb35B456nJoeK4hvl0nOS-uEPuvQ8Vn-8");
  var sheet = sh.getSheetByName("people");   // sheet.getName()  取得名稱
  
  sheet.appendRow(['1', '劉智漢', '27899963', 'email@email.com', 'picture', '1', '1', '1', '1']);
  
  Logger.log(sh.getName());  // 取得spreadSheet名稱  sh.getNumSheets()  // 取得編號
}
將Sheet內容轉成Json
https://spreadsheets.google.com/feeds/list/YOURID/1/public/values?alt=json
透過JS取得JSON資料
 //https://spreadsheets.google.com/feeds/list/YOURID/1/public/values?alt=json 
 const feedID = 'https://spreadsheets.google.com/feeds/list/1NpsE9GGeotJXT8mU6B6OWcnUvzEGB2EZVKsKs5WRlfU/1/public/values?alt=json';
 document.addEventListener('DOMContentLoaded', getData);
 
 function getData() {
     fetch(feedID).then(function (res) {
         return res.json()
     }).then(function (data) {
         console.log(data);
         let tempArr = [];
         let sheetName = data.feed.title.$t;
         data.feed.entry.forEach(function (el) {
             let holder = {};
             for (let key in el) {
                 console.log(key);
                 if (key.substring(0, 3) == "gsx") {
                     console.log(key.split('$')[1]);
                     holder[key.split('$')[1]] = el[key].$t;
                 }
             }
             tempArr.push(holder);
             console.log(el);
         })
         console.log(tempArr);
     })
 }

建立DOC文件

function myfunction() {
   var doc = DocumentApp.create('New Doc');
   Logger.log(doc.getId());
}

行事曆 Google Calendar

// 建立行事曆
function addEvent(){
  var cal = CalendarApp.getDefaultCalendar();  // 取得預設行事曆
  var event = cal.createEvent('First Event',new Date('June 20, 2019 20:00:00 UTC'),new Date('June 20, 2019 22:00:00 UTC'));
  Logger.log('Id'+event.getId());
}

// 刪除行事曆
function delEvents(){
  var startTime = new Date();
  var endTime = new Date();
  endTime.setDate(startTime.getDate()+14);
  var cal = CalendarApp.getDefaultCalendar();
  var events = cal.getEvents(startTime, endTime);
  if(events && events.length >0){
    for(var i=0;i<events.length;i++){
     events[i].deleteEvent() 
    }
  }
}

// 取得時區
var timezone = Session.getScriptTimeZone();
抓取區間的行事曆
function lister(){
  var cal = CalendarApp.getDefaultCalendar();
  var startTime = new Date();
  var endTime = new Date();
  endTime.setDate(startTime.getDate()+7);
  var events = cal.getEvents(startTime, endTime);
  if(events && events.length > 0 ){
    for(var x=0;x<events.length;x++){
      Logger.log(events[x].getStartTime());
      Logger.log(events[x].getEndTime());
      Logger.log(Utilities.formatDate(events[x].getStartTime(), Session.getScriptTimeZone(), 'MMM dd yyyy'));
      Logger.log(Utilities.formatDate(events[x].getStartTime(), Session.getScriptTimeZone(), 'HH:mm'));
      Logger.log(Utilities.formatDate(events[x].getEndTime(), Session.getScriptTimeZone(), 'HH:mm'));
    }
  }
}

// example 2
function listEvents(){
  var startTime = new Date();
  var endTime = new Date();
  endTime.setDate(startTime.getDate()+7);
  
  var cal = CalendarApp.getDefaultCalendar();
  var events = cal.getEvents(startTime, endTime);
 
  var data = [];
  var days = [];
  data.push("Events for today " + Utilities.formatDate(startTime, Session.getScriptTimeZone(), 'MMM dd yyyy'));
  if(events && events.length > 0 ){
    for(var x=0;x<events.length;x++){
      var obj = {
        start : Utilities.formatDate(events[x].getStartTime(), Session.getScriptTimeZone(), 'HH:mm'),
        end : Utilities.formatDate(events[x].getEndTime(), Session.getScriptTimeZone(), 'HH:mm'),
        date : Utilities.formatDate(events[x].getStartTime(), Session.getScriptTimeZone(), 'MMM dd yyyy'),
        title : events[x].getTitle(),
        location : events[x].getLocation(),
        desc: events[x].getDescription()
      }
      days.push(obj);
      data.push(events[x].getTitle() + ' ' + events[x].getId());
      
    }
    return days;
  }else{
    return ['nothing']
  }
  Logger.log(data);
  Logger.log(days);
}
將行事曆資料塞入Sheet中
function addToSheet(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('feed');
  sheet.clear();
  sheet.appendRow(['Start','End','Date','Title','Location','Desc','Full']);
  
  var startTime = new Date();
  var endTime = new Date();
  endTime.setDate(startTime.getDate()+7);
  
  var cal = CalendarApp.getDefaultCalendar();
  var events = cal.getEvents(startTime, endTime);
 
  if(events && events.length > 0 ){
    for(var x=0;x<events.length;x++){
      var arr = [Utilities.formatDate(events[x].getStartTime(), Session.getScriptTimeZone(), 'HH:mm'),Utilities.formatDate(events[x].getEndTime(), Session.getScriptTimeZone(), 'HH:mm'),Utilities.formatDate(events[x].getStartTime(), Session.getScriptTimeZone(), 'MMM dd yyyy'),events[x].getTitle(),events[x].getLocation(),events[x].getDescription(),events[x].getId()];
      Logger.log(arr);
      sheet.appendRow(arr);
    }
  }
}

Trigger

function createTrigger(){
 ScriptApp.newTrigger('addToSheet')
.timeBased()
.everyHours(1)
.create() 
}

透過GS取得JSON

function doGet(e){
  var output = JSON.stringify({
    status:'success',
    data:listEvents()
  }) 
  return ContentService.createTextOutput(output).setMimeType(ContentService.MimeType.JSON);
} 

// 部屬之後,就可以取得此程式的 URL,供外界使用

透過Javascript 取得GS產生出的JSON

 //https://spreadsheets.google.com/feeds/list/YOURID/1/public/values?alt=json 
 const feedID = 'https://spreadsheets.google.com/feeds/list/1NpsE9GGeotJXT8mU6B6OWcnUvzEGB2EZVKsKs5WRlfU/1/public/values?alt=json';
// 使用GS取得資料
 const url = 'https://script.google.com/macros/s/AKfycbw_4vsuSvNmwc7UU4a5T1Y6UpDGrtb8kb28PFnoLiM8eBAP18UN/exec';
 document.addEventListener('DOMContentLoaded', api);
 const output = document.querySelector('.output');
 
 function api() {
     fetch(url).then(function (res) {
         return res.json()
     }).then(function (data) {
         console.log(data);
         outputData(data.data);
     })
 }
 
 function outputData(data) {
     console.log(data);
     for (let i = 0; i < data.length; i++) {
         let div = document.createElement('div');
         div.innerHTML = `${data[i].title} ${data[i].date} 
        ${data[i].start} - ${data[i].end}`;
         output.appendChild(div);
         console.log(data[i]);
     }
 }
 
 function getData() {
     fetch(feedID).then(function (res) {
         return res.json()
     }).then(function (data) {
         let tempArr = [];
         let sheetName = data.feed.title.$t;
         data.feed.entry.forEach(function (el) {
             let holder = {};
             for (let key in el) {
                 if (key.substring(0, 3) == "gsx") {
                     holder[key.split('$')[1]] = el[key].$t;
                 }
             }
             tempArr.push(holder);
         })
         outputData(tempArr);
     })
 }