Google Form: Dynamically creating drop down list from a named range in Google Spreadsheet

I love using Google Form for collecting data and surveys. At times, I wish a feature, where inputs for Drop Down List, Checkbox or Multiple choice could be feed from excel sheet. This will help us when form itself is dependent on values from some other source.

We can achieve it with a simple google script. The script is executed from spreadsheet. It updates the Google form with a trigger set 'on change' on spreadsheet.

In below script, I take an existing google form and modify one of the drop down item in it. The values for this drop down item is fed from a named range in current active sheet.

	function editForm() {
	var form = FormApp.openById('<YOUR_FORM_ID_GOES_HERE>');
	// Following line just logs form title in logger.
	// Helps in troubleshooting. :)
	Logger.log('Form Title is '+ form.getTitle());
	// Gets all items inside the form.
	allItems = form.getItems();
	Logger.log(' Total items in this form '+ allItems.length)
	// Setting a value. Not a great idea to do though.
	empLocItemId=0;
	// Iterate through all items in form
	for (i=0; i < allItems.length ; ++i) {
	itemTitle = allItems[i].getTitle();
	Logger.log('Item Title '+ itemTitle);
	// Checking if item's title matches the one
	// which you want to change. I prefer this way
	// instead of directly using form.getItemById('...')
	if (itemTitle == 'Employee Location') {
	empLocItemId = allItems[i].getId();
	}
	}
	// Checking in case item to edit was not found.
	if (empLocItemId == 0) {
	return;
	}
	// Casting item to listItem. Should use .asCheckBoxItem()
	// if using checkbox or .asMultipleChoiceItem() if using
	// multiple choice.
	empLocItem = form.getItemById(empLocItemId).asListItem();
	// Getting object of current spreadsheet from which we need
	// to get values for drop down list.
	ss = SpreadsheetApp.getActive();
	// Getting a known named range from spreadsheet object.
	empLocRange = ss.getRangeByName('EmployeeLocations');
	empLocs = empLocRange.getValues();
	empLocs.sort()
	// Creating choice Array.
	var empLocChoices = []
	for (i=0; i < empLocs.length; ++i) {
	empLoc = empLocs[i][0].toString().trim();
	if (!(empLoc=='')) {
	empLocChoices.push(empLocItem.createChoice(empLoc));
	}
	}
	// Setting the choice array to drop down item.
	empLocItem.setChoices(empLocChoices);
	}

view raw EditForm.gs hosted with ❤ by GitHub

Obscuring data in database

At times, we may share database for development purpose. And we may need to obscure certain data. One example may be to obscure all email address in a particular table. Following sql statement obscures the local part of email address (before @ sign) and prefixes the domain name with two underscores (__).

update  Employeee_Table emp
set emp.email_id = concat(Translate(SUBSTR(emp.email_id, 1,  Instr(emp.email_id, '@')-1), 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', '84cBF75ZKnbEHofTQRSs6etN9zrW03yUmIkvxVijwJOdl1uAhMqCD2pGLXgaPY'), replace((SUBSTR(emp.email_id, Instr(emp.email_id, '@'))),'@','@__'))

Translate function, available in both - IBM DB2 and Oracle, changes character by character. So, in above query all 'A's will be changed '8', 'B's to 4, 'C's to 'c', 'D's to 'B' and so on. Replace function replaces '@' with '@__'

You can randomize string for your use at http://textmechanic.com/String-Randomizer.html

Obscuring domain name would have made key more predictable. So, either we should choose to use different keys for local part and domain name or just obscure local part.

Prefixing '_' with domain name helps in avoiding any accidental email to be sent to correct domain name.

Also, this isn't any encryption and should not be used where security is important. This is just to obscure data. 

Calling an interactive bash script from another script

Lots of time we need to call existing scripts from new scripts. There are scripts which are interactive and have no option to avoid interactive mode by passing values inline as parameters. Some of them may just need a Yes or No while some may need important inputs in interactive mode.

Let us take a example (a bad one though). Suppose we need to call a script - stopMyServer.sh.

$./stopMyServer.sh
### Do you really want to stop Server? [Yes | No]
Yes
### Please enter the reason code to stop the server
### 1. Scheduled Downtime
### 2. Critical Patch
### 3. Performance degradation
1
### Thanks. Shutting down the server. ###

#!/bin/bash

echo Calling stopMyServer.sh

# EOF is meaningless and can be substituted by any word. Any 
# strings passed between the set of EOF (or any word used 
# instead of EOF) will be passed to interactive script. One 
# line for every interactive pause

./stopMyServer.sh <<EOF
Yes
1
EOF

echo stopMyServer.sh was called

Date Arithmetic in Unix Scripting

Unix has date function which is good to add or subtract days / time from current date but has lots of limitation. I found no easy way to do serious date-time arithmetic using it. One possible way is to convert date-time to seconds (since epoch) and then manipulate it.

#Assign a date to variable. 
startDateTime="2013-04-28 00:00:00"

#Converts date to epoch and assign it to variable. 
#One should be careful as it may accept it as date in only few predefined formats
startDateInSecs=`date -d "$StartDateTime" +"%s"`

#Perform manipulation by adding or subtracting time. 
#For example to add 2 hours
let newDateInSecs=$((StartDateInSecs + 2*3600))

#Then print or assign the date to a variable in required format.
NewDateTime=`date -d @"$newDateInSecs" +"%Y-%m-%d %H:%M:%S"`
echo NewDateTime

#Do note @ before variable $newDateInSecs. That shows date-time is in seconds since epoch