How to Create a Google Sheets Save Button with Apps Script
Question details
The user needs to automate data transfer in Google Sheets by creating a 'Save' button that moves data from a Data Entry sheet to a Raw Data sheet, starting at row 18.
- Product
- Google Sheets
- Device & OS
- not provided
- Scenario
- Automating data entry by moving submitted information to a designated database sheet.
- Observed behavior
- A script needs to be written and assigned to a clickable button to execute the data transfer.
Ensure you have Edit access to the Google Sheet and know the exact cell ranges of your Data Entry fields to accurately map them in the Apps Script.
Use Google Apps Script to Create a Save Button
Write a custom script and assign it to a drawn button to automate data transfer.
Google Apps Script allows you to extend Google Sheets capabilities. By linking a script to a drawing, you can create interactive buttons that run complex workflows, such as migrating data across sheets.
In your Google Sheets document, click on 'Extensions' in the top menu bar and select 'Apps Script' to open the script editor.
Create a function that reads the input range from your Data Entry sheet. Use getLastRow() to find the next available row on the Raw Data sheet. Add logic to ensure the data is written at or below row 18 using getRange() and setValues().
Go back to your spreadsheet, click 'Insert' > 'Drawing'. Use the shapes tool to design a button, add text like 'Save', and click 'Save and Close' to place it on your sheet.
Click on the drawing you just inserted, click the three vertical dots in the top right corner of the shape, select 'Assign script', and type the exact name of your Apps Script function.
Looking for a powerful desktop spreadsheet tool? Try WPS Office
While Google Sheets offers cloud-based scripting, WPS Spreadsheet provides robust desktop performance, advanced data analysis tools, and excellent compatibility with Microsoft Excel macros and formats. Enjoy a lightweight, free office suite with a familiar interface.
Frequently Asked Questions
Can I undo a script action in Google Sheets?
No, actions performed by Google Apps Script cannot be undone using the standard Undo button (Ctrl+Z). You will need to manually delete or correct the transferred data.
Why is my save button not working when clicked?
Ensure that you have correctly spelled the function name when assigning the script to the drawing. Also, check if you have granted the necessary authorization permissions for the script to run on your Google account.
How do I find the next available row in Apps Script?
You can use the getLastRow() method on your target sheet object to find the last row with content, then add 1 to get the next available empty row. If you need to start specifically at row 18, you can use a conditional statement to check if getLastRow() + 1 is less than 18.
