Google Sheets is a powerful tool for managing data and automating workflows. However, it can be time-consuming to perform certain tasks manually. Fortunately, Google Apps Script can help automate these tasks and make your work more efficient.
In this article, we’ll explore 5 useful Apps Script snippets for Google Sheets automation. These snippets can save you time and effort, allowing you to focus on more important tasks.
1. Auto-Resize Columns
When working with Google Sheets, it’s common to have different column widths. However, this can make it difficult to view and edit data. The Auto-Resize Columns snippet helps to automatically adjust column widths to fit the content.
To use this snippet, open your Google Sheet and go to the Script Editor. Paste the following code:
“`
function autoResizeColumns() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
range.autoResizeColumns(1, range.getLastColumn());
}
“`
Save and run the script. Your columns will now be automatically resized to fit the content.
2. Send Emails from Google Sheets
Sending emails manually can be a time-consuming process. With the Send Emails snippet, you can automate this process and send emails directly from your Google Sheet.
To use this snippet, first, you need to enable the Gmail API. Then, open your Google Sheet and go to the Script Editor. Paste the following code:
“`
function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var dataRange = sheet.getDataRange();
var data = dataRange.getValues();
for (var i = 1; i < data.length; i++) {
var emailAddress = data[i][0];
var subject = data[i][1];
var message = data[i][2];
MailApp.sendEmail(emailAddress, subject, message);
}
}
```
Save and run the script. Your emails will now be sent automatically from your Google Sheet.
3. Create PDFs from Google Sheets
Creating PDFs from Google Sheets can be helpful when sharing data. With the Create PDFs snippet, you can automate this process and create PDFs directly from your Google Sheet.
To use this snippet, open your Google Sheet and go to the Script Editor. Paste the following code:
```
function createPDFs() {
var sheet = SpreadsheetApp.getActiveSheet();
var dataRange = sheet.getDataRange();
var values = dataRange.getValues();
var folder = DriveApp.createFolder('PDFs');
for (var i = 1; i < values.length; i++) {
var row = values[i];
var name = row[0];
var email = row[1];
var file = folder.createFile(name + '.pdf', row.join('\n'), MimeType.PDF);
var url = file.getUrl();
var html = 'PDF Link‘;
MailApp.sendEmail(email, ‘Your PDF’, ”, {htmlBody: html});
}
}
“`
Save and run the script. Your PDFs will now be created and emailed to the recipients.
4. Import Data from another Google Sheet
Importing data from another Google Sheet can be useful when working with multiple sheets. With the Import Data snippet, you can automate this process and import data directly from another Google Sheet.
To use this snippet, open your Google Sheet and go to the Script Editor. Paste the following code:
“`
function importData() {
var sourceSheet = SpreadsheetApp.openByUrl(‘SOURCE_SHEET_URL’).getSheetByName(‘SHEET_NAME’);
var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘SHEET_NAME’);
var data = sourceSheet.getDataRange().getValues();
targetSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}
“`
Replace SOURCE_SHEET_URL with the URL of the source sheet and SHEET_NAME with the name of the sheet. Save and run the script. Your data will now be imported from the source sheet to the target sheet.
5. Delete Blank Rows
Deleting blank rows can help to clean up your Google Sheet and make it easier to work with. With the Delete Blank Rows snippet, you can automate this process and delete all blank rows in your Google Sheet.
To use this snippet, open your Google Sheet and go to the Script Editor. Paste the following code:
“`
function deleteBlankRows() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var newData = [];
for (var i = 0; i < data.length; i++) {
if (data[i].join('').length != 0) {
newData.push(data[i]);
}
}
sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}
```
Save and run the script. Your blank rows will now be deleted.
In conclusion, Google Apps Script can help you automate tasks and make your work more efficient. The snippets we've explored in this article are just a few examples of what you can do with Apps Script. By using these snippets and exploring more features of Apps Script, you can streamline your workflow and achieve more in less time.
(Note: Do you have knowledge or insights to share? Unlock new opportunities and expand your reach by joining our authors team. Click Registration to join us and share your expertise with our readers.)