top of page

5 Game-Changing Apps Script Use Cases to Automate Your Sheets

Google Sheets is a powerful tool that allows users to organize, analyze, and visualize data. Apps Script, a scripting language developed by Google, allows users to automate tasks and extend the functionality of Google Sheets. In this article, we'll explore 5 of the best Apps Script use cases in Google Sheets and provide the actual code for each example.



Email Notification


The email notification script is a great way to stay on top of changes made to a spreadsheet. This script sends an email notification to a specified recipient every time a change is made to a particular range of cells.


function sendEmailNotification() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var range = sheet.getRange('A1');
  var recipients = 'example@email.com';
  var subject = 'Cell Updated';
  var body = 'A1 has been updated!';
  var previousValue = range.getValue();
  var currentValue = sheet.getRange('A1').getValue();
  if (currentValue != previousValue) {
    MailApp.sendEmail(recipients, subject, body);
  }
}

ImportJSON


The ImportJSON script allows users to import JSON data from an API into a Google Sheet. This is a great way to quickly and easily analyze data from external sources.


function ImportJSON(url, query, options) {
  var response = UrlFetchApp.fetch(url);
  var data = JSON.parse(response.getContentText());
  var path = query.split('.');
  for (var i = 0; i < path.length; i++) {
    data = data[path[i]];
  }
  return data;
}

Automatic Data Backup


The automatic data backup script is a great way to ensure that data is saved regularly and automatically. This script saves a copy of the Google Sheet to Google Drive at a specified interval, ensuring that users always have a backup of their data.



function autoBackup() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var file = DriveApp.getFileById(sheet.getId());
  var folder = DriveApp.getFolderById('YOUR_FOLDER_ID');
  var backup = file.makeCopy(sheet.getName() + ' Backup', folder);
  var backups = folder.getFilesByName(sheet.getName() + ' Backup');
  while (backups.hasNext()) {
    var oldBackup = backups.next();
    if (oldBackup.getId() != backup.getId()) {
      folder.removeFile(oldBackup);
      oldBackup.setTrashed(true);
    }
  }
}

Merge Data from Multiple Sheets


The merge data from multiple sheets script allows users to combine data from multiple sheets into one sheet. This is a great way to consolidate data from different sources and make it easier to analyze.


function mergeDataFromMultipleSheets() {
  var sheets = ['Sheet1', 'Sheet2', 'Sheet3'];
  var range = 'A1:E10';
  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('TargetSheet');
  targetSheet.clearContents();
  for (var i = 0; i < sheets.length; i++) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheets[i]);
    var data = sheet.getRange(range).getValues();
    targetSheet.getRange(targetSheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);
  }
}

Timestamp


The timestamp script adds a timestamp to a specific range of cells every time the cell is edited. This is a useful way to track when changes were made to the data.


function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var range = e.range;
  if (range.getRow() >= 2 && range.getRow() <= 100 && range.getColumn() == 1) {
    var dateCell = sheet.getRange(range.getRow(), 2);
    dateCell.setValue(new Date());
  }
}


Apps Script is a powerful tool that can help users automate tasks and extend the functionality of Google Sheets. With the code provided in this article, users can take advantage of some of the best Apps Script use cases in Google Sheets. Whether it's sending email notifications, importing JSON data, backing up data automatically, merging data from multiple sheets, or adding timestamps to cells, Apps Script can make Google Sheets even more versatile and useful.


For daily Technology Tips, follow us on Tik Tok!

bottom of page