Adding to the Google Sheets User Interface & Cross Referencing Workbooks
Enhance Google Sheets With Apps Script PART III
Submit Data Across Separate Spreadsheets
Part II of this series discusses how to write a function that prompts the user for information and relays the information to a data tab, and then assign the function to a button within a Google Sheets spreadsheet. This method of gathering data may not seem practical as the user could easily just open up the data tab and manually type the same information in the respective cells. A more practical use of a user prompted form within Google Sheets would be to allow the information to be pushed to another spreadsheet. For example, a manager could install a data collection button on its employee’s spreadsheets and upon entering data, all of the data could be dumped in the manager’s spreadsheet and only seen at the manager’s discretion.
This is done simply by setting the desired data recipient spreadsheet as the active workbook before activating the correct tab and executing appendRow(). This is completed by utilizing openById() which is part of the SpreadsheetApp Class. Each Google spreadsheet has a unique ID that can be found in the url. For example, if the desired recipient spreadsheet url is:
“https://docs.google.com/spreadsheets/d/123456789/edit#gid=0”
then it’s unique ID is 123456789. The following code snippet will activate the tab called Employee List on the recipient spreadsheet and assign it to a new variable newSheet.
var newSS = SpreadsheetApp.openById("1234567890");
SpreadsheetApp.setActiveSpreadsheet(newSS);
var newSheet = newSS.setActiveSheet(newSS.getSheetByName("Employee List"));After the desired sheet is activated, appendRow() can be executed just like in Part II.
newSheet.appendRow([text,text2,text3]);Now once the user gets through the dialogues, the responses are entered into the Employee List tab on the recipient spreadsheet and not on the same spreadsheet that the user is using (although the data can still be entered in both). To give the user confirmation, the simple line:
ui.alert('Thank you! Your response was recorded');can be added after appendRow().
*PRO TIP: The built in function “IMPORTRANGE()” is another method of cross referencing spreadsheets. For example, setting cell A1 as =IMPORTRANGE(“source_spreadsheet_id”, “source_tab_name!A:C”) in the recipient spreadsheet will import the values in columns A,B,C from the source spreadsheet to the recipient spreadsheet.
Add Buttons to the User Interface
To further improve the user experience, instead of clicking on a custom created button, the button can be added as part of the Google Sheets user interface. This can be done easily by utilizing the default trigger onOpen(). Triggers allow functions to execute automatically when a certain event happens, which in this case is opening the spreadsheet. Adding the following lines in the script editor will create a menu (named Add Data) and an item (named Add Employee and linked to the addEmployee function), and add them as part of the user interface.
function onOpen() {
var UI= SpreadsheetApp.getUi();
UI.createMenu('Add Data')
.addItem('Add Trade', 'addTrade')
.addToUi();
}
Now a user is able to utilize the user interface of Google Sheets to submit data to a separate data spreadsheet (managed by whomever) at any time while using the spreadsheet. This simple use of Apps Script can enhance the user experience and efficiency of a large group of employees or clients when entering and managing data or information. For example, a sales manager could manage a master spreadsheet of the department sales log while each individual salesman could have a personal sales log and could simply click “Add Sale” to add the details of a sale that he/she made and this information would be submitted to both the personal and master sales log. A video demonstrating the finished product of the exercises from Parts I & II is shown below.
For reference, the final script for this exercise is as follows:
I hope you learned something from this article and I encourage you to give it a try! Please feel free to comment/respond if you have any questions, need help, would just like to chat, or would like to see an article on another Google Sheets feature!
Thank you :)
Part IV explains how the fairly new feature to Google Sheets, recording macros, can be a helpful scripting tool that can save lots of time.
