Most Commonly Used Google Script Functions in Google Sheets for 2022

Reading Time: 3 minutes

In this guide, we’re going to learn about the most commonly used Google Script functions in Google Sheets in 2022.

What is Google Apps Script?

The Google Apps Script is a fast scripting platform used for app development. You can create business apps that integrate with Google Workspace apps like Gmail, Calendar, Drive, etc.

How to Create Apps Script?

Are you looking for how to create google apps script? Refer to https://blog.tryamigo.com/how-to-create-a-google-apps-script/ 

Widely used Google Script Functions in Google Sheets

1. appendRow function 

• Using the appendRow function, you can write results in new cells.
• You can append a new row to an already existing sheet.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();

   for (var i = 1; i<5; i++) {
      var result = i + 5;
     sheet.appendRow([i,result]);
   }
}

• Store the existing data in a sheet variable.
• Use a for loop to iterate through values.
• Increase the values by 5.
• Store the incremented value in a result variable.
• Use the appendRow function to print the result variable.
• The output is shown below:

2. getValues function

• Using the getValues function, you can create a multidimensional array.
• This multidimensional array can load multiple values from your sheet.
• The values may be of type Number, Date, Boolean or String, depending on the value of the cell.
• The function returns a rectangular grid of values for the specified range.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var data = sheet.getDataRange().getValues();

   for (var i = 1; i < data.length; i++) {
   var result = data[i][0] * 10;
   sheet.getRange(i+1, 2).setValue(result); 
   }
}

• Use getDataRange() function to use the data range.
• Use getValues() function to create a multidimensional array
• Store the array in a variable named data.
• Use for loop to iterate over length of the array.
• Multiply the values by 10.
• Set the new values in the next column.
• The output is shown below:

3. newChart function

• The newChart function helps you automatically generate charts from selected data ranges.
• Helps you create easy-to-read charts without manually creating them from the menu toolbar.

function myFunction(){
}

function SalaryChart(){ 
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheets()[0];
var SalaryChart = sheet.newChart() .setChartType(Charts.ChartType.LINE) .addRange(sheet.getRange('A2:B6')) .setPosition(5, 5, 0, 0) .build();
sheet.insertChart(SalaryChart);
}

• Create a new function called SalaryChart().
• Use newChart() function to create a chart.
• Specify the parameters as per your need.
• You can create line charts, bar charts and pie charts.
• Now insert the chart into your sheet.
• The output is:

See Also

Most Commonly Used Google Script Functions for 2022: Learn about widely used advanced script functions.