Build an Order Form Using Google Spreadsheet Service and Google Apps Script

Google SpreadSheet is a free, convient and powerful tool to use for everybody. Not only the super easy usability but also programable capability allowed users to customize Google SpreadSheet to have more advanced functionalities. By using on-line editing and debugging tool, writting javascript and calling Google Spreadsheet Service seems to be an easy job to do.

In the Spreadsheet window, click the "Tool" and select "code editor".

There are four important classes needed to be noticed:

• SpreadsheetApp
• SpreadSheet
• Range
• Cell

Basic Operation

A user has to call these classes one by one in order to get the object to be controlled. For example:

// get active spreadsheet.
var ss = SpreadsheetApp.getActiveSpreadsheet(); 

// get spreadsheet named "Order Form". 
var sheet = ss.getSheetByName("Order Form");

// get range from first row to the max row, first column to the max column.
var seriesRange = sheet.getRange(startingRow, 1, sheet.getMaxRows(), 1);

// get the first cell.
var aCell = seriesRange.getCell(1, 1);

Create Customized Menu

Google SpreadSheet allows us to cusmize menu. The demo code is:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Order Form')
      .addItem('Clear From', 'clearForm')
      .addSeparator() 
      .addItem('Submit Order', 'cloneSheet')
      // .addSubMenu(ui.createMenu('Sub-menu')
      //     .addItem('Second item', 'menuItem2'))
      .addToUi();
  
  // Set the starting cursor location.
  setStartingPoint_();
}

How to refresh (clear) the spreadsheet?

function clearForm() {
  var ui = SpreadsheetApp.getUi()
  var response = ui.alert('Clear Form', 'Do you want to clear all data?', ui.ButtonSet.OK_CANCEL);
  if (response == ui.Button.CANCEL) {
    return;
  }
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  var sheet = ss.getSheetByName('Empty Order Form');
  sheet.copyTo(ss).setName('Clear Order Form');
  newSheet = ss.getSheetByName('Clear Order Form');
  ss.setActiveSheet(newSheet);
  ss.moveActiveSheet(0);

  var orderFormSheet = ss.getSheetByName('Order Form');
  ss.deleteSheet(orderFormSheet);

  newSheet.setName('Order Form');  
  
  setStartingPoint_();
}

function setStartingPoint_() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Order Form");
  activeRange = sheet.getRange(5,2);
  sheet.setActiveRange(activeRange);
}

How to insert a row and do other logics?

function insertRow() {
  var startingRow = 4
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Order Form");
  var seriesRange = sheet.getRange(startingRow, 1, sheet.getMaxRows(), 1);
  var seriesValues = seriesRange.getValues();
  var seriesSelected = sheet.getRange((startingRow-1),1).getValue();   // .getRange("A3")
  
  // if series not selected, show warning dialog box 
  if (seriesSelected == "") {
    SpreadsheetApp.getUi().alert("Please select the series. Thank you.");
    return;
  };
  
  // find the row number of the title of series.
  // It also represents the sequence of the series, 
  // which can be used to determin the sequence of sheet.
  i = 0;
  while (seriesValues[i][0] != seriesSelected) {
    i++;
  };
  
  // Compensate the difference of starting point.
  seriesSelectedRow = i + startingRow;      
  
  // find the last row of the model.
  // Compensate the difference of starting point.
  var modelRow = seriesSelectedRow; 
  
  // "modelRow + 1" means start testing from the next row of the sub title. 
  while (!sheet.getRange(modelRow+1, 2).isBlank()) {
    modelRow += 1;
  };
  
  // if first model row is blank, then modelRaw = seriesSelectedRaw + 1
  if (modelRow == seriesSelectedRow) {
    modelRow += 1;
  };
  
  Logger.log("seriesSelectedRow: "+seriesSelectedRow)
  Logger.log("modelRow: "+modelRow)
    
  // This inserts one row after the last row of the model and focus on it.
  sheet.insertRowsAfter(modelRow,1);
  lastRowRange = sheet.getRange(modelRow+1,2);
  sheet.setActiveRange(lastRowRange);
  
  // Set the "TOTAL" formula
  sheet.getRange(modelRow+1, 7).setFormulaR1C1("=R[0]C[-2]*R[0]C[-1]")
}

Searching the right data

function onEdit() {
  var startingRow = 4
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Quit if not at the "Order From" sheet.
  if (ss.getSheetName() != "Order Form") {
    return
  };
  
  var sheet = ss.getSheetByName("Order Form");  //getActiveSheet();
  var seriesRange = sheet.getRange(startingRow, 1, sheet.getMaxRows(), 1);
  var ar = sheet.getActiveRange();
  var specSelected = ar.getValue();
  
  var row = ar.getRow();  // getA1Notation();
  Logger.log(row);
  
  // Quit if row at the heading part.
  if (row < startingRow) {
    return
  } 
  
  // find the series name where the avtive range belongs.
  
  var i = (row - 1);
  Logger.log(i);
 
  while (sheet.getRange(i,1).isBlank()) {
    i--;
  };
  
  // find the sequence of the series
  var seriesName = sheet.getRange(i,1).getValue()
  Logger.log(seriesName);
  
  var seriesSequence = 0;
  var j = 0;
  while (seriesRange.getValues()[j][0] != seriesName) {
    if (!seriesRange.getValues()[j][0] == '' ) {
      seriesSequence++;
    }; 
    j++;    
  };
  seriesSequence++;     // Cpmpensate the sequence.
  Logger.log(seriesSequence);
  
  // Get the worksheet containing the series data.
  var seriesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[seriesSequence];
  var seriesSheetName = seriesSheet.getName();
  Logger.log(seriesSheet.getName());
  
  specRange = seriesSheet.getRange(1, 1, seriesSheet.getLastRow(), 4);
  specValues = specRange.getValues();
  Logger.log("seriesSheet:"+seriesSheet.getName());
  Logger.log(specValues);
 
  // find the price associated with the spec
  k = 0;
  while (specValues[k][0] != specSelected) {
    k++;
  };
  
  // Set price to order form
  priceSelected = specValues[k][3];
  sheet.getRange(row, 5).setValue(priceSelected);
  Logger.log(priceSelected);
}

How to find out the current cell where you are in edit?

function currentCell() {
  return SpreadsheetApp.getActiveRange().getA1Notation();
}

How to duplicate to a new spreadsheet?

// copy data from current SpreadSheet to a new SpreadSheet  
function cloneSheet() {
 
  // Show dialog box to ask if continue
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert('Submit Order', 'Do you want to submit order?', ui.ButtonSet.OK_CANCEL);

  if (response == ui.Button.CANCEL) {
    return;
  }
  
  // source doc
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Order Form");
  
  // Get full range of data
  var SRange = sheet.getDataRange();
  // var SRange = sheet.getRange("A:G");  
 
  // get A1 notation identifying the range
  var A1Range = SRange.getA1Notation();
 
  // get the data values in range
  var SData = SRange.getValues();
  var SFormat = SRange.getNumberFormats();
  
  // target spreadsheet
  // var tss = SpreadsheetApp.openById(ssB);
  var ssNew = SpreadsheetApp.create('Mekk_2017_Order_'+ getDateTime_());
  
  // target sheet
  // var ts = tss.getSheetByName('Target Spreadsheet'); 
  var sheetNew = ssNew.getActiveSheet();
  
  // Clear the Google Sheet before copy
  // ts.clear({contentsOnly: true});
 
  // set the target range to the values of the source data
  sheetNew.getRange(A1Range).setValues(SData);
  sheetNew.getRange(A1Range).setNumberFormats(SFormat);
  
  // Sets the first column to a width which fits the text
  sheetNew.autoResizeColumn(1);
  sheetNew.autoResizeColumn(2);
  sheetNew.getRange('A3').clear();
  
  // Get the current folder
  var ssId = ss.getId();
  var parentFolders = DriveApp.getFileById(ssId).getParents();
  
  while (parentFolders.hasNext()) {
   var folder = parentFolders.next();  // parentFolders.next() is the current folder. 
  }; 
  
  // Get the id of new spreadsheet
  var ssNewFile = DriveApp.getFileById(ssNew.getId());
  var ssNewName = ssNewFile.getName();
  
  // Put the Spreadsheet to the current folder
  folder.addFile(ssNewFile);
  
  // Remove the Spreadsheet from the root folder
  DriveApp.getRootFolder().removeFile(ssNewFile);
  
  // user can also close the dialog by clicking the close button in its title bar.
  SpreadsheetApp.getUi().alert("The Order has been placed. Thank you.");  
}

How to get a proper datetime format?

function getDateTime_() {
 // This formats the date as Greenwich Mean Time in the format
 // year-month-dateThour-minute-second.
 
 var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'"); 
 
 return formattedDate;
}