Importing Data From Google Spreadsheet into Firebase

For a group project, we have decided to collect resources using a Google Form, which stores the data into a spreadsheet. In order to make this more useful for us, we decided to import the data into Firebase. The following are the steps used to import the data from the Google Spreadsheet into Firebase.

Spreadsheet Setup

1. Make a copy of the spreadsheet
2. On the Tools menu, select the Script editor… option

1. Once you are in the script editor, select Resources > Libraries…

1. Follow the steps here to select the Firebase library: https://sites.google.com/site/scriptsexamples/new-connectors-to-google-services/firebase

1. Follow the steps here to start importing the data into Firebase: https://sites.google.com/site/scriptsexamples/new-connectors-to-google-services/firebase/tutorials/read-and-write-data-in-firebase-from-apps-script

There are following caveats when importing the spreadsheet data over to Firebase:

You cannot have invalid characters for the Firebase key so take a look at the character set limitations below. The key is part of the URLs used to construct the Firebase references. Therefore as a workaround, encoding the email address as base64 will work.

Character Set Limitations

Note that URLs used to construct Firebase references may contain any unicode characters except:

• . (period)
• $ (dollar sign)
• [ (left square bracket)
• ] (right square bracket)
• # (hash or pound sign)
• / (forward slash)

Permission must be provided before the app script can be run against the spreadsheet.

Finally, the app script for importing our data from the spreadsheet to our Firebase looks something like this:

	function writeDataToFirebase() {
	var ss = SpreadsheetApp.openByUrl(<insert spreadsheet url>);
	var sheet = ss.getSheets()[0];
	var data = sheet.getDataRange().getValues();
	var dataToImport = {};
	for(var i = 1; i < data.length; i++) {
	//prepare the key and grab the data key and values
	var emailAddress = data[i][1];
	var lessonCompleted = data[i][2];
	dataToImport[Utilities.base64Encode(emailAddress) + "-" + lessonCompleted] = {
	timestamp:data[i][0],
	emailAddress:emailAddress,
	lessonCompleted:lessonCompleted,
	wisdom:data[i][3],
	};
	}
	var firebaseUrl = <insert firebase url>;
	var base = FirebaseApp.getDatabaseByUrl(firebaseUrl);
	base.setData("students", dataToImport);
	Logger.log(dataToImport);
	}

view raw import-spreadsheet-firebase.js hosted with ❤ by GitHub

Be sure to replace the spreadsheet URL (copy from the exact URL after opening your spreadsheet manually) and the Firebase URL (copied from the Firebase Dashboard). Updated the JSON format manually as needed before executing the script. However, even if you make a mistake, you can go to the Firebase Dashboard and delete all the data.

For reference, to delete the data from the Firebase Dashboard, click on the X and confirm by clicking on the DELETE button as shown below: