Automating Google Sheets with Google Apps Script: A Case Study

Google Sheets is a powerful tool for data management and analysis. However, when it comes to repetitive tasks and complex workflows, manual handling can become tedious. This is where Google Apps Script comes in. It's a JavaScript-based language designed to extend the functionality of Google Workspace applications like Google Sheets. In this article, we'll explore a practical example of using Google Apps Script to automate a task in Google Sheets.

The Problem

Suppose you manage inventory and use Google Forms to update stock levels in a Google Sheet. You need a way to calculate the balance of stock after each form submission automatically. For instance, if the form submission indicates a "Прих?д на склад" (stock arrival), the quantity should be added to the balance. Conversely, the quantity should be subtracted if the form submission indicates a different operation.

The Solution

We can achieve this automation using Google Apps Script. The script will trigger on form submission, read the submitted data, perform the necessary calculations, and update the Google Sheet accordingly.

Here's the complete script that accomplishes this task:

function onFormSubmit(e) {
  try {
    var sheet = e.range.getSheet(); // Use the range property to get the sheet
    var lastRow = e.range.getRow(); // Use the range property to get the row
    
    // Log the operation
    Logger.log("Form submitted. Sheet: " + sheet.getName() + ", Last Row: " + lastRow);

    // Get the operation type and quantity from the form submission
    var operationType = sheet.getRange(lastRow, 2).getValue();
    var quantity = sheet.getRange(lastRow, 6).getValue();
    
    Logger.log("Operation Type: " + operationType + ", Quantity: " + quantity);

    // Validate the input values
    if (!operationType || isNaN(quantity)) {
      throw new Error("Invalid data: Operation Type or Quantity is missing or incorrect.");
    }

    // Calculate the "Залишок" value based on the operation type
    var balance = (operationType === "Прих?д на склад") ? quantity : quantity * -1;
    
    // Set the calculated "Залишок" value in the last column
    sheet.getRange(lastRow, 7).setValue(balance);

    Logger.log("Balance set to: " + balance);
  } catch (error) {
    Logger.log("Error in onFormSubmit: " + error.message);
  }
}

// Install the trigger to run the script on form submit
function installTrigger() {
  ScriptApp.newTrigger('onFormSubmit')
    .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
    .onFormSubmit()
    .create();
}        

Script Breakdown

1. onFormSubmit(e): This function is triggered whenever the form is submitted. The event object e contains information about the form submission.
2. Logging: Logs are added using Logger.log to help trace the execution flow and diagnose issues.
3. installTrigger(): This function creates a trigger that automatically runs the onFormSubmit function whenever a form submission is made.

Setting Up the Script

1. Open Script Editor: In your Google Sheet, go to Extensions > Apps Script.
2. Copy the Script: Copy and paste the provided script into the script editor.
3. Save the Script: Give your script a name and save it.
4. Install the Trigger: Run the installTrigger function to set up the form submission trigger.

Testing and Debugging

Submit a test form and check if the balance is updated correctly. You can view the logs by going to View > Logs in the Apps Script editor to diagnose any issues.

Conclusion

By using Google Apps Script, you can automate repetitive tasks in Google Sheets, enhancing efficiency and reducing manual errors. This script serves as a starting point for more complex automations and can be customized further to meet specific requirements.

Google Apps Script is a versatile tool that can significantly extend the capabilities of Google Workspace applications. Whether you're managing inventory, automating email responses, or generating reports, mastering Google Apps Script can save time and improve productivity.