Apps Script Fun Coding Exercises 1

by

Apps Script Fun Coding Exercises 1

Learn more about Google Apps Script – Free coding lesson – Source Code included

Count Cells Function
Get Sheet Names Function
Count Rows in Sheet
Get values from Columns
Get Column Values by Letter

#GoogleAppsScript #GAS #AppsScript #GoogleScript #GoogleSheets #GoogleDocs #GoogleForms #GoogleSlides #GoogleWorkspace #GSuite #GSuiteEdu #GoogleEdu #onlinecourses #elearning #distancelearning #onlinetraining #learningonline #onlineeducation #MOOCs #virtuallearning #edtech #educationtechnology #learningsolutions #traininganddevelopment #professionaldevelopment 

Count Cells Function

Create a function that takes a spreadsheet as a parameter and returns the total number of cells with values in it.

function countCells(spreadsheet) {

  var sheet = SpreadsheetApp.openById(spreadsheet).getActiveSheet();

  var range = sheet.getDataRange();

  var values = range.getValues();

  var count = 0;

  for (var i = 0; i < values.length; i++) {

    for (var j = 0; j < values[0].length; j++) {

      if (values[i][j] != “”) {

        count++;

      }

    }

  }

  return count;

}

Get Sheet Names Function

Create a function that takes a spreadsheet as a parameter and returns an array of all the sheet names in the spreadsheet.

function getSheetNames(spreadsheet) {

  var sheets = SpreadsheetApp.openById(spreadsheet).getSheets();

  var sheetNames = [];

  for (var i = 0; i < sheets.length; i++) {

    sheetNames.push(sheets[i].getName());

  }

  return sheetNames;

}

Count Rows in Sheet

Create a function that takes a spreadsheet and a sheet name as parameters and returns the total number of rows in that sheet.

function countRows(spreadsheet, sheetName) {

  var sheet = SpreadsheetApp.openById(spreadsheet).getSheetByName(sheetName);

  var range = sheet.getDataRange();

  var numRows = range.getNumRows();

  return numRows;

}

Get values from Columns

Create a function that takes a spreadsheet and a sheet name as parameters and returns an array of all the values in the first column of that sheet.

function getColumnValues(spreadsheet, sheetName) {

  var sheet = SpreadsheetApp.openById(spreadsheet).getSheetByName(sheetName);

  var range = sheet.getDataRange();

  var values = range.getValues();

  var columnValues = [];

  for (var i = 0; i < values.length; i++) {

    columnValues.push(values[i][0]);

  }

  return columnValues;

}

Get Column Values by Letter

Create a function that takes a spreadsheet, a sheet name, and a column letter (e.g., “A”, “B”, “C”) as parameters and returns an array of all the values in that column.

function getColumnValuesByLetter(spreadsheet, sheetName, columnLetter) {

  var sheet = SpreadsheetApp.openById(spreadsheet).getSheetByName(sheetName);

  var range = sheet.getRange(columnLetter + “:” + columnLetter);

  var values = range.getValues();

  var columnValues = [];

  for (var i = 0; i < values.length; i++) {

    columnValues.push(values[i][0]);

  }

  return columnValues;

}

Apps-Script-Fun-Coding-Exercises-1Download