Google Sheets Apps Script basic functions

How to use google script in sheets? Script examples, how to run a script, your cheat sheet.

Google apps scripts to:

Set cell value
Get cell value
Get range
Create new sheet
Add event to calendar
Clear sheet
Format date
Delete rows 9 to 11
Filter words "apple" in column C
Get sheet by name
Get last row with data
Send email with data from Google Sheets
Index of
Map
Replace '+1' with '' in column C

Set cell value

To set the value of a cell A1 for example in Google Sheets using Google Apps Script, you can use the setValue() method of the Range object. Here's an example script:

function setCellValue() {
  var sheet = SpreadsheetApp.getActiveSheet();
  // change this to the cell you want to set the value of
  var cell = sheet.getRange("A1");
  // set the value of the cell to "Hello, World!"
  cell.setValue("Hello, World!");
}

Get cell value

To get the value of a cell in Google Sheets using Google Apps Script, you can use the getValue() method of the Range object. Here's an example:

function getCellValue() {
  // connect to active sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  // change A1 to the cell range you want to get the value of
  var cell = sheet.getRange("A1");
  var value = cell.getValue();
  // log the value to the console
  Logger.log(value);
}

In this example, we're getting the active sheet of the spreadsheet using the getActiveSheet() method of the SpreadsheetApp object.
We're then getting the range of the cell we want to get the value of using the getRange() method and passing in the cell range as a string ("A1" in this case). Finally, we're getting the cell's value using the getValue() method of the Range object and see the value in the console using the Logger.log() method.
You can modify this code to get the value of any cell in your Google Sheets document by changing the cell address passed to the getRange() method.
Try it yourself. Change the code to get value of different cell in the spreadsheet.

Get range

To get a range from A1 to B16 (or any other range) using Google Apps Script, you can use the getRange() method of the Sheet class. Here's an example code snippet that retrieves the range A1:B16 on the active sheet:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getRange("A1:B16");

In this example, ss is a variable that holds a reference to the active spreadsheet, sheet holds a reference to the active sheet, and range holds a reference to the range A1:B16 on the active sheet. You can modify the range by changing the string argument passed to the getRange() method.

Append row

To append a row to a Google Sheet using Google Apps Script, you can use the appendRow() method of the Sheet class. Here's an example code snippet that appends a row of data to the active sheet:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var data = ["John", "Doe", "35", "Male"];
sheet.appendRow(data);

In this example, ss is a variable that holds a reference to the active spreadsheet, sheet holds a reference to the active sheet, and data is an array containing the values to be appended to the sheet. The appendRow() method adds a new row to the bottom of the sheet and populates it with the values in the data array.

You can modify the data array to include the values you want to append to the sheet. You can also use other methods of the Sheet class to insert or modify rows in different positions.

Create new sheet

To create a new sheet in a Google Spreadsheet using Google Apps Script, you can use the insertSheet() method of the Spreadsheet class. Here's an example code snippet that creates a new sheet named "New Sheet" and sets it as the active sheet:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.insertSheet("New Sheet");
ss.setActiveSheet(sheet);

In this example, ss is a variable that holds a reference to the active spreadsheet. The insertSheet() method creates a new sheet with the specified name ("New Sheet" in this case) and returns a reference to the new sheet. The setActiveSheet() method sets the new sheet as the active sheet in the spreadsheet.

You can modify the name of the new sheet by changing the string argument passed to the insertSheet() method. You can also use other methods of the Spreadsheet class to access and manipulate existing sheets in the spreadsheet.

Add event to calendar

To add an event to a Google Calendar using Google Apps Script, you can use the CalendarApp.createEvent() method. Here's an example code snippet that creates a new event on the primary calendar:

var event = CalendarApp.getDefaultCalendar().createEvent('New Event',
    new Date('April 25, 2023 08:00:00'),
    new Date('April 25, 2023 10:00:00'),
    {description: 'This is a test event'}
);

In this example, event is a variable that holds a reference to the new event that was created. The createEvent() method takes four arguments:

The event title or name as a string (in this case, "New Event").
The start date and time of the event as a Date object (in this case, April 25, 2023 at 8:00 AM).
The end date and time of the event as a Date object (in this case, April 25, 2023 at 10:00 AM).
An optional Object containing additional event parameters, such as a description.

You can modify the arguments passed to the createEvent() method to create an event with different details. You can also use other methods of the CalendarApp class to access and manipulate existing events in the calendar.

Clear sheet

To clear the contents of a Google Sheet using Google Apps Script, you can use the clear() method of the Range class. Here's an example code snippet that clears the contents of the active sheet:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getDataRange();
range.clear();

In this example, ss is a variable that holds a reference to the active spreadsheet, sheet holds a reference to the active sheet, and range holds a reference to the range that contains all the data on the sheet (i.e., the range of cells from A1 to the last cell containing data). The clear() method removes all the data and formatting from the cells in the range.

You can modify the code to clear a different range of cells by changing the argument passed to the getRange() method. For example, if you only want to clear the contents of cells A1:B16, you can replace the getDataRange() method with getRange("A1:B16").

Format date

To format dates in Google Apps Script, you can use the Utilities.formatDate() method. This method takes a Date object, a time zone, and a format string as arguments, and returns a string representation of the date in the specified format.

Here's an example code snippet that formats the current date and time in the "yyyy-MM-dd HH:mm:ss" format:

var date = new Date();
var timeZone = Session.getScriptTimeZone();
var format = "yyyy-MM-dd HH:mm:ss";
var formattedDate = Utilities.formatDate(date, timeZone, format);
Logger.log(formattedDate);

In this example, date holds a Date object that represents the current date and time, timeZone holds the time zone of the script, format holds the desired date format (in this case, "yyyy-MM-dd HH:mm:ss"), and formattedDate holds the formatted date string. The Logger.log() method outputs the formatted date string to the script's log.

You can modify the format argument to specify a different date format. The format string uses a combination of letters and special characters to represent the various parts of the date and time. For example, "yyyy" represents the year, "MM" represents the month, "dd" represents the day, "HH" represents the hour in 24-hour format, "mm" represents the minute, and "ss" represents the second. You can find a complete list of format codes in the Google Apps Script documentation.

Delete rows 9 to 11

To delete rows 9 to 11 in a Google Sheet using Google Apps Script, you can use the deleteRows() method of the Sheet class. Here's an example code snippet that deletes the rows 9 to 11 in the active sheet:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
sheet.deleteRows(9, 3);

In this example, ss is a variable that holds a reference to the active spreadsheet, sheet holds a reference to the active sheet, and deleteRows() method is used to delete 3 rows starting from row 9 (i.e., rows 9 to 11). The first argument of the deleteRows() method is the starting row index (inclusive), and the second argument is the number of rows to delete.

You can modify the code to delete a different range of rows by changing the arguments passed to the deleteRows() method. For example, if you want to delete rows 5 to 8, you can replace the deleteRows(9, 3) method with deleteRows(5, 4).

Filter words "apple" in column C

To filter words "apple" in column C of a Google Sheet using Google Apps Script, you can use the setColumnFilterCriteria() method of the Filter class. Here's an example code snippet that creates a filter based on the specified criteria:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getDataRange();
var filter = range.createFilter();

var columnIndex = 3; // Column C
var filterCriteria = SpreadsheetApp.newFilterCriteria().whenTextContains("apple").build();
filter.setColumnFilterCriteria(columnIndex, filterCriteria);

In this example, ss is a variable that holds a reference to the active spreadsheet, sheet holds a reference to the active sheet, and range holds a reference to the data range of the sheet. The createFilter() method is used to create a filter based on the data range.

The columnIndex variable specifies the index of the column to filter (in this case, column C, which has an index of 3). The whenTextContains() method of the FilterCriteriaBuilder class is used to specify that the filter should include any cells in the specified column that contain the text "apple". The build() method is used to create the filter criteria.

Finally, the setColumnFilterCriteria() method is used to set the filter criteria for the specified column.

Once you have created and applied the filter, you can use the various methods of the Filter class to manipulate and customize the filter further. For example, you can use the sort() method to sort the filtered data, or the setColumnFilterCriteria() method to specify filter criteria for other columns.

Get sheet by name

To get a specific sheet by name in a Google Spreadsheet using Google Apps Script, you can use the getSheetByName() method of the Spreadsheet class. Here's an example code snippet that retrieves a sheet by name:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = "My Sheet";
var sheet = ss.getSheetByName(sheetName);

In this example, ss is a variable that holds a reference to the active spreadsheet, and sheetName is a string variable that contains the name of the sheet to retrieve. The getSheetByName() method is used to retrieve the sheet with the specified name.

Once you have retrieved the sheet, you can use its methods and properties to manipulate its contents. For example, you can use the getRange() method of the Sheet class to retrieve a range of cells from the sheet, or the getDataRange() method to retrieve the entire data range of the sheet.

Get last row with data

To get the last row with data in a Google Sheet using Google Apps Script, you can use the getLastRow() method of the Sheet class in combination with the getValues() method of the Range class. Here's an example code snippet that retrieves the last row with data in column A:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var data = sheet.getRange("A:A").getValues();
var lastRow = data.filter(String).length;
Logger.log(lastRow)

In this example, ss is a variable that holds a reference to the active spreadsheet, sheet holds a reference to the active sheet, and data holds the values of all cells in column A. The getRange() method is used to retrieve a range of cells that covers all rows in column A. The getValues() method is used to retrieve the values of all cells in the specified range.

The filter() method of the Array class is then used to create a new array that only contains non-empty values. The String function is passed as a parameter to the filter() method, which filters out any empty values. The length property of the resulting array is then used to determine the last row with data in column A.

Once you have determined the last row with data, you can use its value to manipulate the contents of the sheet further. For example, you can use the getRange() method of the Sheet class to retrieve a range of cells that covers only the rows with data, or the deleteRows() method to delete any rows below the last row with data.

Send email with data from Google Sheets

To get the recipient, subject, and body for the email from cells in a Google Sheet using Google Apps Script, you can use the getRange() method of the Sheet class to retrieve the values of the cells, and then pass these values as parameters to the sendEmail() method of the MailApp class. Here's an example code snippet that retrieves the recipient, subject, and body from cells in a sheet named "Email":

function sendEmail() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email");
  var recipient = sheet.getRange("A1").getValue();
  var subject = sheet.getRange("B1").getValue();
  var body = sheet.getRange("C1").getValue();

  MailApp.sendEmail(recipient, subject, body);
}

In this example, the sendEmail() function retrieves the Email sheet from the active spreadsheet, and then retrieves the values of the cells in the first row of the sheet using the getRange() and getValue() methods. The recipient email address is retrieved from cell A1, the subject of the email is retrieved from cell B1, and the body of the email is retrieved from cell C1.

Once the recipient, subject, and body have been retrieved from the sheet, they are passed as parameters to the sendEmail() method of the MailApp class to send the email.

Index of

The indexOf() method in Google Apps Script is used to find the index of the first occurrence of a specified value in a string. Here's an example code snippet that demonstrates how to use the indexOf() method in a Google Sheet:

function findSubstring() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var searchString = "apple";
  var column = 3;
  var numRows = sheet.getLastRow();
  var range = sheet.getRange(1, column, numRows);
  var values = range.getValues();
  var foundRow = -1;

  for (var i = 0; i < values.length; i++) {
    if (values[i][0].indexOf(searchString) != -1) {
      foundRow = i + 1;
      break;
    }
  }

  if (foundRow != -1) {
    Logger.log("Found '%s' in row %s", searchString, foundRow);
  } else {
    Logger.log("'%s' not found in column %s", searchString, column);
  }
}

In this example, the findSubstring() function retrieves the active sheet, and then defines a search string ("apple"), a column to search (column 3, which corresponds to column C), and the number of rows in the sheet using the getLastRow() method.

The function then retrieves a range of cells that covers the entire third column of the sheet using the getRange() method, and then retrieves the values of the cells in the range using the getValues() method.

The indexOf() method is then used to search for the specified search string in each row of the column. If the search string is found in a row, the index of that row is recorded in the foundRow variable and the loop is exited using the break statement.

Finally, the function uses the Logger.log() method to output a message indicating whether the search string was found in the column or not.

Note that the indexOf() method returns -1 if the specified search string is not found in the string being searched.

Map

The map() method in Google Apps Script is used to create a new array by applying a function to each element of an existing array. Here's an example code snippet that demonstrates how to use the map() method in a Google Sheet:

function multiplyArray() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var column = 1;
  var numRows = sheet.getLastRow();
  var range = sheet.getRange(1, column, numRows);
  var values = range.getValues();
  var multiplier = 2;

  var multipliedValues = values.map(function(row) {
    return [row[0] * multiplier];
  });

  var outputRange = sheet.getRange(1, column + 1, numRows);
  outputRange.setValues(multipliedValues);
}

In this example, the multiplyArray() function retrieves the active sheet, and then defines a column to retrieve (column 1, which corresponds to column A), the number of rows in the sheet using the getLastRow() method, and a multiplier value of 2.

The function then retrieves a range of cells that covers the entire first column of the sheet using the getRange() method, and then retrieves the values of the cells in the range using the getValues() method.

The map() method is then used to apply a function to each row in the column. The function takes a single argument, which represents the current row being processed, and returns a new array that contains the multiplied value of the cell in the row and the multiplier value.

The setValues() method is then used to set the values of the new array into a range of cells that covers the second column of the sheet (i.e., column B).

Note that the map() method returns a new array, and does not modify the original array.

Replace '+1' with '' in column C

To replace all occurrences of the string '+1' with an empty string in column C of a Google Sheet using Google Apps Script, you can use the replace() method, as follows:

function replaceString() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var column = 3;
  var numRows = sheet.getLastRow();
  var range = sheet.getRange(1, column, numRows);
  var values = range.getValues();

  var replacedValues = values.map(function(row) {
    return [row[0].replace('+1', '')];
  });

  var outputRange = sheet.getRange(1, column, numRows);
  outputRange.setValues(replacedValues);
}

In this example, the replaceString() function retrieves the active sheet, and then defines column C to retrieve (column 3), the number of rows in the sheet using the getLastRow() method, and a search string ('+1').

The function then retrieves a range of cells that covers column C using the getRange() method, and then retrieves the values of the cells in the range using the getValues() method.

The replace() method is then used to replace all occurrences of the '+1' string with an empty string in each cell value in column C. The map() method is used to apply the replace() method to each row of the column.

The setValues() method is then used to set the values of the new array into a range of cells that covers column C of the sheet.

Note that the replace() method does not modify the original string, but instead returns a new string with the specified replacements.

Published 23 Feb 2023