How to Create a Submit Button to Log Data in Google Sheets

Google Sheets offers a wide variety of features that can help automate tasks and improve efficiency. One such feature is the ability to add a “Submit” button that logs data from one sheet into another.

This is particularly useful when you need to collect data on one sheet (e.g., a calculator sheet) and log it to another sheet automatically, along with timestamps for better tracking.

In this article, we’ll show you how to set up a submit button in Google Sheets using Google Apps Script to log data from one sheet to another.

The Scenario

Imagine you are using Google Sheets to manage inputs in a “Calculator v1.0” sheet. Once users fill in their data, you want to store that data, along with a timestamp, in a “Log” sheet. This process needs to be automatic and triggered by a button press.

To achieve this, you need:

A formula to gather and organize the data you want to log.
A Google Apps Script to log that data into the “Log” sheet when a button is clicked.
The setup of a submit button that triggers the script.

Let’s break it down step-by-step.

1. Organize Your Data with a Formula

The first step is to organize the data that will be logged into one place. You can use a formula to flatten and transpose data from multiple cells into a single row.

For example, in the “Calculator v1.0” sheet, you can use the following formula in cell J5:

=transpose(flatten({C20, C21, C26, C27, C28, C5, C6, C7, C8, D5, C9, C10, C11, C12, D13, C13, C14, C15, C16, C17, C18, C19, textjoin(", ", true, E5:G21)}))

This formula pulls data from a range of cells and consolidates it into a single row. You can customize the formula to match your specific data needs.

2. Write the Google Apps Script

Next, you need to write a script that will log the data from the “Calculator v1.0” sheet to the “Log” sheet. Here’s a simple script to get you started:

function copyData() {
  const ss = SpreadsheetApp.getActive();
  const sourceSheet = ss.getSheetByName('Calculator v1.0');
  
  // Get the data from cell J5
  const val = sourceSheet.getRange('J5:AP5').getValues()[0];
  
  // Add a timestamp at the beginning of the data
  val.unshift(new Date());
  
  // Get the "Log" sheet
  const targetSheet = ss.getSheetByName('Log');
  
  // Append the data to the "Log" sheet
  targetSheet.appendRow(val);
}

How the Script Works:

The script retrieves the data from the “Calculator v1.0” sheet (specifically from the range J5:AP5).
It adds a timestamp to the beginning of the data.
The data is then appended as a new row in the “Log” sheet.

3. Add the Submit Button

The final step is to add a “Submit” button to the “Calculator v1.0” sheet that will trigger the script when clicked.

In Google Sheets, go to Insert > Drawing and create a simple shape (like a rectangle or circle) that will act as your button.
After the drawing is inserted into the sheet, click on the shape, then click the three dots in the upper-right corner and choose Assign script.
In the pop-up box, type copyData (the name of the function we wrote in the script editor) and click OK.

Now, when you click the “Submit” button, the copyData function will run, logging the data from the “Calculator v1.0” sheet into the “Log” sheet, complete with a timestamp.

Conclusion

Using a Google Sheets submit button along with Google Apps Script allows you to automate data logging with ease.

This setup ensures that data from one sheet is logged into another with the press of a button, reducing the risk of manual errors and ensuring that all entries are timestamped.

By following these steps, you can quickly streamline your workflow and keep your data organized for future analysis.

Happy scripting!