readme.md

how to use

prepare

1. create a spreadSheet on google drive
2. create tabs
3. clicked menu bar 'Tools' > 'Scripts manager' > 'new'
4. copy and paste scripts
5. change YOUR_SPREADSHEET_ID

send feedback email to user

1. fill FAQ number on 'Feedback' tab's column 'faq number'
2. run 'sendMailByFAQNumber' auto send email to users

Tabs

tab - Feedback columns

• datacreate
• timestamp
• user name
• user fbId
• user email
• user feedback
• faq number
• faq type
• faq reply status
• note

tab - FAQ columns

• faq number
• faq type
• is auto reply (YES/NO)
• Email subject
• Email body content

tab - EamilTemplate columns

• email number
• email type
• email subject
• email body header
• email body footer

Reference

• Google app script

autoTimestamp.js

/*timestamp and datacreate
  don't forget to set validate columns to ONLY DATA_VALUES for datacreate and timstamp columns
  Do not allow to enter not valid data!
  I don't know how to do this inside the script.*/
function onEdit(e) {
//########################## SETUP BEGIN ###################################  
  var dc = 'datacreate'; //set datacreate_column_header
  var ts = 'timestamp'; //set timestamp_column_header
//  var dc_fontcolor = '#808080'; //set font-color in HEX or CSS mode for datacreate column
//  var ts_fontcolor = '#808080'; //set font-color in HEX or CSS mode for timestamp column
//  var dc_fontsize = '6'; //set font-size for datacreate column
//  var ts_fontsize = '6'; //set font-size for timestamp column
//  var dc_width = '45'; //set witd in pixels for datacreate column
//  var ts_width = '45'; //set witd in pixels for timestamp column  
//########################### SETUP END  ###################################
  var sheet = e.source.getActiveSheet();
  var row_i = e.source.getActiveRange().getRowIndex(); // returns active row index
  var col_i = e.source.getActiveRange().getColumnIndex(); // returns active column index
  var col_i_dc = getIndexByHeader_(dc, sheet)+1; // index is 0-based - we need 1-based
  var col_i_ts = getIndexByHeader_(ts, sheet)+1; // index is 0-based - we need 1-based
  var last_row = sheet.getLastRow();
  if (row_i != 1) {  // do not change headers
    var Dc = sheet.getRange(row_i, col_i_dc, 1, 1);
    if (Dc.getValue() == '' && col_i != col_i_dc) { //change if value is empty and you do not trying to edit them directly
      Dc.setValue(new Date());
    }
    var Ts = sheet.getRange(row_i, col_i_ts, 1, 1);    
    if (col_i != col_i_ts) { //change if you do not trying to edit them directly
      Ts.setValue(new Date());
    }
  }  
//  sheet.getRange(1, col_i_dc, 1, 1).setComment('The name of this header need for the script, which puts a row-created time.');
//  sheet.getRange(1, col_i_ts, 1, 1).setComment('The name of this header need for the script, which puts a row modified time.');
//  var dc_col_range = sheet.getRange(1, col_i_dc, last_row, 1);
//  var ts_col_range = sheet.getRange(1, col_i_ts, last_row, 1);
//  dc_col_range.setFontColor(dc_fontcolor).setFontSize(dc_fontsize);
//  ts_col_range.setFontColor(ts_fontcolor).setFontSize(ts_fontsize);
//  sheet.setColumnWidth(col_i_dc, dc_width);
//  sheet.setColumnWidth(col_i_ts, ts_width);
}  
  
/*  Library. My helper functions.

Check given value of column_header (only in first row) for exist and return it's column-index-number.
First index would be 0. The last one argument, sheet is optional and defaults to active sheet!
you can put it in cell like formula e.g. A1: = getIndexByHeader_("insert_here_column_header_which_index_you_want_to_find"),
BUT you must put values in formulas into double quotes!
*/
function getIndexByHeader_ (column_header, sheet) { 
  var sheet = sheet || SpreadsheetApp.getActiveSheet();
  var numCols = sheet.getLastColumn();
  var firstRow = sheet.getRange(1, 1, 1, numCols).getValues(); //get firs row
  for (y in firstRow[0]) {
    var col_name = firstRow[0][y];
    if (col_name == column_header) {
      var col_index = Number(y);
    } else {
      if (y >= numCols-1 && typeof col_number == 'undefined') {
        return "Something wrong!!! Can\'t find index for your column name/header."
      }
    } if (col_name == column_header) break;
  }
  return col_index;
}

// Check is the given value is Date? string returns TRUE or FALSE
function isDate_ (obj) {
  return !!(obj && obj.getTimezoneOffset && obj.setUTCFullYear);
}

code.js

/*
 *  psot http request
 */
function doPost(e){
  var app = UiApp.createApplication();
  var panel = app.createVerticalPanel();

  var pData = e.parameters.data;
  var data = Utilities.jsonParse(pData);

  for (var i in data)
    panel.add(app.createLabel(i + ": " + data[i]));

  app.add(panel);

  insertRow_(data.name, data.fbid, data.email, data.feedback, data.photocount);
  /*
  GmailApp.sendEmail(liveallMemberEmails,
                     "LiveAll Feedback from Google - "+data.fbid+" - "+data.name+" - "+data.email+" - "+data.photocount,
                     "name:"+data.name+"\n fbid:"+data.fbid+"\n email:"+data.email+"\n feedback:"+data.feedback+"\n photocount:"+data.photocount);
  */
  return app;
}

/*
 * get http request
 */
function doGet(e) {
  var app = UiApp.createApplication();
  var panel = app.createVerticalPanel();

  var pData = e.parameters.data;
  //return ContentService.createTextOutput(pData).setMimeType(ContentService.MimeType.JSON);

  panel.add(app.createLabel(e.parameters.data));

  autoFillFAQType_();

  app.add(panel);
  return app;
}

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the readRows() function specified above.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
  autoFillFAQType_();
};

liveall.js

//FeedBack spreadsheet id
var spreadsheetId = 'YOUR_SPREADSHEET_ID';

/*
 * insert row to feedback
 */
function insertRow_(name, fbid, email, feedback, note){
  var sheet = getSheet_('Feedback');
  sheet.appendRow([ new Date(), new Date(), name, fbid, email, feedback, '', '', '',note]);
}

/*
 * fill faq type by faq number
 */
function autoFillFAQType_(){
  var spreadSheet = getSheet_('Feedback');
  var rows = spreadSheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var columNumberName = 'FAQ #';
  var columTypeName = 'FAQ type';
  var columnNumberIndex = getColumnIndex_(columNumberName);
  var columnTypeIndex = getColumnIndex_('Feedback', columTypeName);
  for (var i = 0; i <= numRows - 1; i++) {
    if(i != 0){
      var row = values[i];
      var myNumberValue = row[columnNumberIndex];
      var myTypeValue = row[columnTypeIndex];

      if(myNumberValue == '-' || myNumberValue == ' ' || myNumberValue == '' || myTypeValue == undefined){
        continue;
      }

      if(myTypeValue == undefined || myTypeValue == null || myTypeValue == ''){
        var cell = spreadSheet.getRange(i+1, columnTypeIndex+1);
        Logger.log(cell);

        var cellNumber = parseInt(myNumberValue)+1;
        var newValue = '=FAQ!B'+cellNumber;
        cell.setFormula(newValue);
      }
    }
  }
}

function sendMailByFAQNumber(){
  var sheetFeedback = getSheet_('Feedback');
  var sheetFAQ = getSheet_('FAQ');
  var sheetEmailTemplete = getSheet_('EmailTemplete');

  var feedbackRows = getRows_('Feedback');
  var feedbackReplayIndex = getColumnIndex_('Feedback', 'Replay');
  var feedbackFAQNumberIndex = getColumnIndex_('Feedback', 'FAQ #');
  var feedbackFAQTypeIndex = getColumnIndex_('Feedback', 'FAQ type');
  var feedbackFeedbackIndex = getColumnIndex_('Feedback', 'Feedback');
  var feedbackEmailIndex = getColumnIndex_('Feedback', 'Email');

  for(var i = 0; i < feedbackRows.length; i++){
    var row = feedbackRows[i];

    //if feedback Replay is null skip
    //if feedback FAQ # is null or '-' or undefined skip
    if(row[feedbackReplayIndex].length > 1 || row[feedbackFAQNumberIndex] == '' || row[feedbackFAQNumberIndex] == '-' || row[feedbackFAQNumberIndex] == undefined){
      continue;
    }

    var cellFAQType = sheetFeedback.getRange(i+1, feedbackFAQTypeIndex+1, 1, 1);
    var feedbackFAQNumber = row[feedbackFAQNumberIndex]+1;
    var newValue = '=FAQ!B'+feedbackFAQNumber;
    cellFAQType.setFormula('=FAQ!B'+feedbackFAQNumber);

    //if faq auto reply != YES skip
    var isAutoReply = getColumn_('FAQ', 'Auto Reply');
    if(isAutoReply[feedbackFAQNumberIndex-2] != 'YES'){
      continue;
    }

    //when reply eamil to user input "Feedback -> Reply" column "Email"
    var cellReplay = sheetFeedback.getRange(i+1, feedbackReplayIndex+1, 1, 1);
    cellReplay.setValue('Email');

    var faqSubject = sheetFAQ.getRange(2, feedbackFAQNumber+1, 1, 1).getValue();
    var faqAnswer = sheetFAQ.getRange(2, feedbackFAQNumber+2, 1, 1).getValue();
    var emailHeader = sheetEmailTemplete.getRange(2, 4, 1, 1).getValue();
    var emailFooter = sheetEmailTemplete.getRange(2, 5, 1, 1).getValue();
    var feedbackFeedback = row[feedbackFeedbackIndex];
    var feedbackEmail =  row[feedbackEmailIndex];
    var emailContent = feedbackFeedback+"\n\n"+faqAnswer;

    GmailApp.sendEmail(feedbackEmail,
                       faqSubject,
                       '',
                       { 'htmlBody': emailHeader+emailContent+emailFooter});
  }
}

function sendEmailTest_(){
  var nameArray = getColumn_('viplist', 'name');
  var emailArray = getColumn_('viplist', 'email');

  for(var i = 0; i < nameArray.length; i++){
    Logger.log('name: %s, email: %s', nameArray[i], emailArray[i]);
    GmailApp.sendEmail('[email protected]',
                       '恭喜您升級為 LiveAll VIP 帳戶一年',
                       '',
                       {"htmlBody": '<html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"></head><body style="background-image: url(http://www.liveall.com/stationary/images/bg_paper.gif); background-repeat: repeat; margin: 0 auto; width:600px; height: 100%;font-family:Helvetica Neue,Helvetica,Arial,sans-serif;"><table border="none" cellpadding="0" cellspacing="0" class="container" width="600px" style="margin: 0; padding: 0;"><tbody><tr><td colspan="2"><img src="http://www.liveall.com/stationary/images/newsletter_head.gif" width="650px" height="140px" style="display:block;"></td></tr><tr><td style="background-image: url(http://www.liveall.com/stationary/images/newsletter_bg.gif); background-repeat: repeat-y;"><table border="none" cellspacing="0" cellpadding="0" class="conatiner" style="margin:0; padding: 0;"><tbody><tr><td class="content" style="min-height: 200px; padding: 15px 30px 10px 80px; font-size: 12px;"></td><td width="450px" style="color: #666;"><div style="width:400px; line-height: 18px;">您好'+nameArray[i]+',<br><br>恭喜您升級為 LiveAll VIP 帳戶一年。<br><br>現在，您可以享有無限大的相簿分享空間，<br>並可上傳與下載相片原始圖檔。<br><br><br>另外, 我們針對上傳速度也有調整<br><br>趕快試試看 VIP 新功能 ：<a href="http://www.liveall.com/"> http://www.liveall.com </a><br><br><br>我們熱切地期待能夠在 LiveAll<br>再度欣賞到您的精彩新作品！<br></div></td></tr></tbody></table></td></tr><tr><td colspan="2"><img src="http://www.liveall.com/stationary/images/newsletter_foot.gif" width="650px" height="115px" style="display: block;"></td></tr></tbody></table></body></html>'});
      break;
  }

  //GmailApp.sendEmail('[email protected]', '恭喜您升級為 LiveAll VIP 帳戶一年', '', {"htmlBody": '<html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"></head><body style="background-image: url(http://www.liveall.com/stationary/images/bg_paper.gif); background-repeat: repeat; margin: 0 auto; width:600px; height: 100%;font-family:Helvetica Neue,Helvetica,Arial,sans-serif;"><table border="none" cellpadding="0" cellspacing="0" class="container" width="600px" style="margin: 0; padding: 0;"><tbody><tr><td colspan="2"><img src="http://www.liveall.com/stationary/images/newsletter_head.gif" width="650px" height="140px" style="display:block;"></td></tr><tr><td style="background-image: url(http://www.liveall.com/stationary/images/newsletter_bg.gif); background-repeat: repeat-y;"><table border="none" cellspacing="0" cellpadding="0" class="conatiner" style="margin:0; padding: 0;"><tbody><tr><td class="content" style="min-height: 200px; padding: 15px 30px 10px 80px; font-size: 12px;"></td><td width="450px" style="color: #666;"><div style="width:400px; line-height: 18px;">您好'+nameArray[i]+',<br><br>恭喜您升級為 LiveAll VIP 帳戶一年。<br><br>現在，您可以享有無限大的相簿分享空間，<br>並可上傳與下載相片原始圖檔。<br><br><br>另外, 我們針對上傳速度也有調整<br><br>趕快試試看 VIP 新功能 ：<a href="http://www.liveall.com/"> http://www.liveall.com </a><br><br><br>我們熱切地期待能夠在 LiveAll<br>再度欣賞到您的精彩新作品！<br></div></td></tr></tbody></table></td></tr><tr><td colspan="2"><img src="http://www.liveall.com/stationary/images/newsletter_foot.gif" width="650px" height="115px" style="display: block;"></td></tr></tbody></table></body></html>'});
}

spreadsheet.js

//get column index from column name
function getColumnIndex_ (_sheetName, _column_header_name){
  var row = getRow_(_sheetName, 0);
  var columnNumber = row.indexOf(_column_header_name);
  return columnNumber;
}

//get sheets by id
function getSheetsById_(_spreadsheetId){
  return SpreadsheetApp.openById(_spreadsheetId).getSheets();
}

//get sheets by sheet name
//spreadsheetId => sheets
function getSheet_(_sheetName){
  var sheets = getSheetsById_(spreadsheetId);
  for(var i in sheets){
    var sheet = sheets[i];
    if(sheet.getName() == _sheetName){
      return sheet;
    }
  }
}

//get rows from sheet by sheet name
function getRows_(_sheetName){
  var sheet = getSheet_(_sheetName);
  var sheetData = sheet.getDataRange();
  var rows = sheetData.getValues();
  return rows;
}

// get row from sheet by sheet name, row index
function getRow_(_sheetName, rowIndex){
  var rows = getRows_(_sheetName);
  return rows[rowIndex];
}

// get column from sheet
function getColumn_(_sheetName, columnName){
  var columnIndex = getColumnIndex_(_sheetName, columnName);
  var sheet = getSheet_(_sheetName);
  var rowOfColumnArray = sheet.getRange(1, columnIndex+1, sheet.getLastRow(), 1).getValues();
  var rows = [];

  for(var i = 1; i< rowOfColumnArray.length; i++)
    rows.push(rowOfColumnArray[i][0]);

  return rows;
}