Show
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 ScriptThe 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:
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:
Add the following code:
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:
The 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. ConclusionCreating 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! Before you go...If you've found this post helpful, you can buy me a coffee. It's certainly not necessary but much appreciated! Leave A CommentIf you have any questions or suggestions, feel free to leave a comment. I do get inundated with messages regarding my posts via LinkedIn and leaving a comment below is a better place to have an open discussion. Your comment will not only help others, but also myself. How do I copy a range from one sheet to another in Google Sheets?Get data from other sheets in your spreadsheet. On your computer, go to docs.google.com/spreadsheets/.. Open or create a sheet.. Select a cell.. Type = followed by the sheet name, an exclamation point, and the cell being copied. For example, =Sheet1! A1 or ='Sheet number two'! B4 .. How do I automatically copy rows in Google Sheets to another sheet based on criteria?To copy the rows based on specific text to another new sheet, the following formula may help you, please do as this: Enter this formula: =filter(original! A:E,original!
How do I pull data from one Google sheet to another based on the criteria?Sync data from one spreadsheet to another. To start, open up the spreadsheet or tab you want to copy to the new Sheet > copy the sheet's URL.. Make a note of the cells you want to import.. Open the new sheet where you want the data to appear.. In the cell begin to type > =IMPORTRANGE (you'll see the code as you begin to type). How do I use Importrange in Google script?How Do I Use IMPORTRANGE in Google Sheets?. Find the URL from your browsers address bar to import.. Enter =IMPORTRANGE( into an empty cell and paste the URL inside quote marks.. Type a comma, then specify the range inside quotation marks eg: “Sheet2! B6:C18 and press enter.. Click the #REF error and click Allow Access.. |