Whenever there is a need to restructure an Excel spreadsheet to an acceptable form to be used for a SaaS platform or custom application, my first inclination is to build something in C# to get the spreadsheet into a form I require.
This week I felt adventurous and decided to break the mundane job of formatting a spreadsheet using an approach I've been reading up on for some time but just never got a chance to apply in a real-world scenario - Google App Scripts.
What Is A Google App Script?
Released in 2009, Google App Scripts is a cloud-based platform that allows you to automate tasks across Google Workspace products such as Drive, Docs, Sheets, Calendar, Gmail, etc. You could think of App Scripts as similar to writing a macro in Microsoft Office. They both can automate repeatable tasks and extend the standard features of the application.
The great thing about Google App Script development is being able to use popular web languages (HTML/CSS/JavaScript) to build something custom. Refreshing when compared to the more archaic option of using VBA in Microsoft Office.
Some really impressive things can be achieved using App Scripts within the Google ecosystem.
Google Sheets App Script
The Google App Script I wrote fulfils the job of taking the contents of cells in a row from one spreadsheet to be copied into another. The aim is to carry out automated field mapping, where the script would iterate through each row from the source spreadsheet and create a new row in the target spreadsheet where the cell value would be placed in a different column.
This example will demonstrate a very simple approach where the source spreadsheet will contain five columns where each row contains numbers in ascending order to then be copied to the target spreadsheet in descending order.
Before we add the script, we need to create two spreadsheets:
- Source sheet: Source - Numbers Ascending
- Target sheet: Destination - Numbers Descending
The source sheet should mirror the same structure as the screenshot (below) illustrates.
The target sheet just needs to contain the column headers.
The App Script can be created by:
- Navigating to Extensions > App Scripts from the toolbar. This will open a new tab presenting an interface to manage our scripts.
- In the "Files" area, press the "+" and select "Script".
- Name the script file: "export-cells-demo.gs".
Add the following code:
// Initialiser.
function run() {
sendDataToDestinationSpreadSheet();
}
// Copies values from a source spreadsheet to the target spreadsheet.
function sendDataToDestinationSpreadSheet() {
var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
// Get source spreadsheet by its name.
var sourceSheet = activeSheet.getSheetByName('Source - Numbers Ascending');
// Select the source spreadsheet cells.
var sourceColumnRange = sourceSheet.getRange('A:E');
var sourceColumnValues = sourceColumnRange.getValues();
// Get target spreadsheet by its name..
var targetSheet = activeSheet.getSheetByName('Destination - Numbers Descending');
// Iterate through all rows from the source sheet.
// Start index at 1 to ignore the column header.
for(var i = 1; i < sourceColumnValues.length; i++) {
// Get the cell value for the row.
var column1 = sourceColumnValues[0,i][0];
var column2 = sourceColumnValues[0,i][1];
var column3 = sourceColumnValues[0,i][2];
var column4 = sourceColumnValues[0,i][3];
var column5 = sourceColumnValues[0,i][4];
// Use getRange() to get the value position by declaring the row and column number.
// Use setValue() to copy the value into target spreadsheet column.
targetSheet.getRange(i+1, 1).setValue(column5);
targetSheet.getRange(i+1, 2).setValue(column4);
targetSheet.getRange(i+1, 3).setValue(column3);
targetSheet.getRange(i+1, 4).setValue(column2);
targetSheet.getRange(i+1, 5).setValue(column1);
}
}
The majority of this script should be self-explanatory with the aid of comments. The only part that requires further explanation is where the values in the target sheet are set, as this is where we insert the numbers for each row in descending order:
...
...
targetSheet.getRange(i+1, 1).setValue(column5);
targetSheet.getRange(i+1, 2).setValue(column4);
targetSheet.getRange(i+1, 3).setValue(column3);
targetSheet.getRange(i+1, 4).setValue(column2);
targetSheet.getRange(i+1, 5).setValue(column1);
...
...
The getRange function accepts two parameters: Row Number and Column Number. In this case, the row number is acquired from the for loop index as we're using the same row position in both source and target sheets. However, we want to change the position of the columns in order to display numbers in descending order. To do this, I set the first column in the target sheet to contain the value of the last column from the source sheet and carried on from there.
All the needs to be done now is to run the script by selecting our "run()" function from the App Scripts toolbar and pressing the "Run" button.
The target spreadsheet should now contain the numbered values for each row in descending order.
Voila! You've just created your first Google App Script in Google Sheets with simple field mapping.
Conclusion
Creating my first Google App Script in a real-world scenario to carry out some data manipulation has opened my eyes to the possibilities of what can be achieved without investing additional time developing something like a Console App to do the very same thing.
There is a slight learning curve involved to understand the key functions required to carry out certain tasks, but this is easily resolved with a bit of Googling and reading through the documentation.
My journey into Google App Scripts has only just begun and I look forward to seeing what else it has to offer!
Ever since I purchased my Nexus 7 last year. I've been trying to find a nice case for it. Failing, I settled for a cheap and cheerful folio case from eBay, which (still to this day) has served me well. But I was dying to have a case that looked different and oozed some unique design elements.
