# Google Apps for Spreadshhet
###### tags: `GoogleAppScript`

## wirte a function

You can use two type http method **post** or **get**

function doGet(request){

}

function doPost(request){

}

## recevive parameters

Following code was recevive url's **parameter**，like http://demo.com?tab=time

function doGet(request){
    const tab = request.parameter.tab
}

function doPost(request){
    const dataSet = JSON.parse(request.postData.contents);
}

## setting Document

Spreadsheet's url https://docs.google.com/spreadsheets/d/abcdfredggh

**abcdfredggh** was this document's id, paste it to following code

var SpreadSheet = SpreadsheetApp.openById("documentId");

## Select tab

It was easy to understand from api's name.....

**[0]** index 0 mean first tab, index 1 mean second tab

var Sheet = SpreadSheet.getSheets()[0];

## Select data Scope from tab

### for doGet

#### 1. get lastRow

var lastDataRowNumber = Sheet.getLastRow();

#### 2. get data's range

var data = Sheet.getRange("A2:F" + lastDataRowNumber).getValues();

### for doPost

#### 1. set start position (row)

var StartRow = 3;

## response

### Returning JSON

We usually want api's callback was a json data

return ContentService.createTextOutput(JSON.stringify(result))
         .setMimeType(ContentService.MimeType.JSON);

### Returning HTML

function doGet(){

 var HTMLString = "<style> h1,p {font-family: 'Helvitica', 'Arial'}</style>"
 + "<h1>Hello World!</h1>"
 + "<p>Welcome to the Web App";

 HTMLOutput = HtmlService.createHtmlOutput(HTMLString);
 return HTMLOutput
}

## write your code

Maybe you want to receive each tab's data as a json type response, try this code.

### example for doGet

function doGet(request) {
  const tab = request.parameter.tab
  
  var SpreadSheet = SpreadsheetApp.openById(
    "documentId"
  );
  
  if(tab == 'home'){
    const home = getHome(SpreadSheet);
  }
  if(tab == 'reservation'){
    const reservation = getReservation(SpreadSheet); 
  }
  if(tab == 'shopping'){
    const shopping =  getShooping(SpreadSheet);
  }
  
  const result = {
    home: home,
    reservation: reservation,
    shopping: shopping
    
  }

  return ContentService.createTextOutput(JSON.stringify(result))
         .setMimeType(ContentService.MimeType.JSON);
}

function getHome(SpreadSheet){
  var Sheet = SpreadSheet.getSheets()[0];
  var lastDataRowNumber = Sheet.getLastRow();
  var data = Sheet.getRange("A2:F" + lastDataRowNumber).getValues();

  var homeArray = [];
  data.forEach(function(item) {
    if(!item[0]) return true
    const sort = item[0];
    const product_name = item[1];
    const product_link = item[2];
    const product_pic = item[3];
    const price = item[4];
    const sale_price = item[5];
    var obj = {
      sort: sort,
      product_name: product_name,
      product_link: product_link,
      product_pic: product_pic,
      price: price,
      sale_price: sale_price,
    };

    homeArray.push(obj);
  });
  
  return homeArray

}

function getReservation(SpreadSheet){
  var Sheet = SpreadSheet.getSheets()[1];
  var lastDataRowNumber = Sheet.getLastRow();
  var data = Sheet.getRange("A5:AA" + lastDataRowNumber).getValues();

  var ticketArray = [];
  
  const search_age_tran = {
    "不分齡" : 1,
    "6歲以下" : 2,
    "7歲以上" : 3,
    "親子一起": 4
  }
  
  const search_price_tran = {
    "免費" : 1,
    "200元以下" : 2,
    "250-500元" : 3,
    "550元以上": 4,
    "不限金額" : 5
  }
  
  const search_theme_tran = {
    "maker" : 1,
    "steam" : 2,
    "教育創新" : 3,
    "動手做": 4,
  }
  
  const search_sort_tran = {
    "未售完工作坊" : 1,
    "現場活動" : 2,
    "已售完工作坊" : 3,
  }
  const prefix = 'https://storage.googleapis.com/makerparty-parenting-com-tw/dev-2019/images'
  data.forEach(function(ticket) {
    const code = ticket[0];
    if(!code) return true
    const name = ticket[1];
    const sort = ticket[2];
    const workshop_code = ticket[3];
    const workshop_name = ticket[4];
    const workshop_desc = ticket[5];
    const age = ticket[6];
    const togetther = ticket[7];
    const price = ticket[8];
    const price_desc = ticket[9];
    const session = ticket[10];
    const workshop_link = ticket[11];
    const booth_code = ticket[12];
    const booth_sort = ticket[13];
    const booth_link = ticket[14];
    const reservation_link = ticket[17];
    const session_time = ticket[18];
    const session_desc = ticket[19];
    const workshop_pic = prefix + '/' +code + '/'+ ticket[20];
    const vender_logo = prefix + '/logo/'+ ticket[21];
    const vender_link = ticket[22];
    const search_age = ticket[23];
    const search_price = ticket[24];
    const search_theme = ticket[25];
    const search_sort = ticket[26];
    
    var obj = {
      code: code,
      name: name,
      sort: sort,
      workshop_code: workshop_code,
      workshop_name: workshop_name,
      workshop_desc: workshop_desc,
      age: age,
      togetther: togetther,
      price: price,
      price_desc: price_desc,
      session: session,
      workshop_link: workshop_link,
      booth_code: booth_code,
      booth_sort: booth_sort,
      booth_link: booth_link,
      reservation_link: reservation_link,
      session_time: session_time,
      session_desc: session_desc,
      workshop_pic: workshop_pic,
      vender_logo: vender_logo,
      vender_link: vender_link,
      search_age: search_age,
      search_price: search_price,
      search_theme: search_theme,
      search_sort: search_sort_tran[search_sort]
    };

    ticketArray.push(obj);
  });
  
  return ticketArray

}

function getShooping(SpreadSheet){
  
  var Sheet = SpreadSheet.getSheets()[2];
  var lastDataRowNumber = Sheet.getLastRow();
  var data = Sheet.getRange("A2:G" + lastDataRowNumber).getValues();

  var shoppingArray = [];
  data.forEach(function(item) {
    const category = item[0];
    if(!category) return true
    const sort = item[1];
    const product_name = item[2];
    const product_link = item[3];
    const product_pic = item[4];
    const price = item[5];
    const sale_price = item[6];
    var obj = {
      category: category,
      sort: sort,
      product_name: product_name,
      product_link: product_link,
      product_pic: product_pic,
      price: price,
      sale_price: sale_price,
    };

    shoppingArray.push(obj);
  });
  
  return shoppingArray
}

### example for do Post

function doPost(request) {
  const dataSet = JSON.parse(request.postData.contents);
  var SpreadSheet = SpreadsheetApp.openById("documentId");
  var Sheet = SpreadSheet.getSheets()[0];
  var StartRow = 3;
  dataSet.forEach(function(item){
    					
    Sheet.getRange(StartRow, 1).setValue(item.id);
    Sheet.getRange(StartRow, 2).setValue(item.company_name);
    Sheet.getRange(StartRow, 3).setValue(item.company_name_en);
    Sheet.getRange(StartRow, 4).setValue(item.exhibition);
    Sheet.getRange(StartRow, 5).setValue(item.normal_url);
    Sheet.getRange(StartRow, 6).setValue(item.overseas_url);
    StartRow +=1;
  })
  
  result = {
    message: 'export ' + dataSet.length + 'records',
    records: dataSet.length
  }
  return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(
    ContentService.MimeType.JSON
  );
}

### example for export pdf to your google drive

add `exportAsPDF()` insert to your code

function _exportBlob(blob, fileName) {
  blob = blob.setName(fileName)
  var folder_id = "google drive folder's id"
  var pdfFile = DriveApp.getFolderById(folder_id).createFile(blob)
}

function exportAsPDF() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var blob = _getAsBlob(spreadsheet.getUrl())
  _exportBlob(blob, spreadsheet.getName())
}
function _getAsBlob(url, sheet, range) {
  var rangeParam = ''
  var sheetParam = ''
  if (range) {
    rangeParam =
      '&r1=' + (range.getRow() - 1)
      + '&r2=' + range.getLastRow()
      + '&c1=' + (range.getColumn() - 1)
      + '&c2=' + range.getLastColumn()
  }
  if (sheet) {
    sheetParam = '&gid=' + sheet.getSheetId()
  }
  var exportUrl = url.replace(/\/edit.*$/, '')
      + '/export?exportFormat=pdf&format=pdf'
      + '&size=LETTER'
      + '&portrait=true'
      + '&fitw=true'       
      + '&top_margin=0.75'              
      + '&bottom_margin=0.75'          
      + '&left_margin=0.7'             
      + '&right_margin=0.7'           
      + '&sheetnames=false&printtitle=false'
      + '&pagenum=false'
      + '&gridlines=true'
      + '&fzr=FALSE'      
      + sheetParam
      + rangeParam
      
  Logger.log('exportUrl=' + exportUrl)
  var response = UrlFetchApp.fetch(exportUrl, {
    headers: { 
      Authorization: 'Bearer ' +  ScriptApp.getOAuthToken(),
    },
  })
  
  return response.getBlob()
}