Apps Script Questions with Solutions and Coding Examples 9

by

Apps Script Questions with Solutions and Coding Examples 9

How can I use Google Apps Script to send emails from a Google Sheet?
How can I add a timestamp to a Google Sheet when a cell is edited?
How can I retrieve data from an external API using Google Apps Script?
How can I delete rows from a Google Sheet based on a condition?
How can I generate random numbers in Google Apps Script?

#GoogleScripts #GoogleAddons #GoogleAutomation #GoogleForms #GoogleDocs #GoogleDrive #GoogleCalendar #GoogleSlides #GoogleAppsScriptTips #GoogleAppsScriptTutorials #GoogleSheets #GAS #AppsScript #JavaScript #Coding #Automation #Productivity #GSuite #GoogleWorkspace #google #email #data

How can I use Google Apps Script to send emails from a Google Sheet?

function sendEmails() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var startRow = 2; // Assumes header row is row 1

  var numRows = sheet.getLastRow() – 1; // Number of rows to process

  var dataRange = sheet.getRange(startRow, 1, numRows, 4) // Assuming 4 columns of data

  var data = dataRange.getValues();

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

    var row = data[i];

    var emailAddress = row[0];

    var subject = row[1];

    var message = row[2];

    var cc = row[3];

    MailApp.sendEmail(emailAddress, subject, message, {cc: cc});

  }

}

Explanation: This script fetches data from a Google Sheet and sends an email to each recipient. It assumes that the email address is in the first column, the subject is in the second column, the message is in the third column, and the CC email address is in the fourth column.

How can I add a timestamp to a Google Sheet when a cell is edited?

function onEdit(e) {

  var sheet = e.source.getActiveSheet();

  var range = e.range;

  if (sheet.getName() === ‘Sheet1’ && range.getColumn() === 1) {

    range.offset(0, 1).setValue(new Date());

  }

}

Explanation: This script adds a timestamp to the adjacent cell in the same row when a cell in column A is edited. It uses the onEdit trigger to detect the edit event and the offset method to write the timestamp to the adjacent cell.

How can I retrieve data from an external API using Google Apps Script?

function getWeather() {

  var url = ‘https://api.openweathermap.org/data/2.5/weather?q=London,uk&appid=<your-api-key>’;

  var response = UrlFetchApp.fetch(url);

  var json = response.getContentText();

  var data = JSON.parse(json);

  var temperature = data.main.temp – 273.15; // Convert from Kelvin to Celsius

  Logger.log(‘Temperature in London is ‘ + temperature.toFixed(1) + ‘°C’);

}

Explanation: This script retrieves the current temperature in London from the OpenWeatherMap API and logs it to the execution transcript. It uses the UrlFetchApp.fetch method to send a GET request to the API endpoint, parses the response as JSON, and extracts the temperature value from the data.

How can I delete rows from a Google Sheet based on a condition?

function deleteRows() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var data = sheet.getDataRange().getValues();

  for (var i = data.length – 1; i >= 0; i–) {

    if (data[i][0] === ”) {

      sheet.deleteRow(i + 1);

    }

  }

}

Explanation: This script deletes rows from the active sheet if the first cell in the row is blank. It iterates over the rows in reverse order to avoid the issue of row indices changing during deletion.

How can I generate random numbers in Google Apps Script?

function generateRandomNumbers() {

  var numbers = [];

  for (var i = 0; i < 10; i++) {

    var random = Math.random() * 100;

    numbers.push(random);

  }

  Logger.log(numbers);

}

You can generate random numbers in Google Apps Script using the Math.random() method. This method returns a random number between 0 and 1. You can use this method to generate random integers or floats within a specified range.

Here’s an example code snippet that generates a random integer between 1 and 10:

function generateRandomInt() {

  var min = 1;

  var max = 10;

  var randomInt = Math.floor(Math.random() * (max – min + 1)) + min;

  Logger.log(randomInt);

}

In this example, we define the minimum and maximum values of the range we want to generate random integers from. We then use the Math.floor() method to round down the result of Math.random() * (max – min + 1), which will generate a random float between min and max, inclusive. Finally, we add min to this value to ensure that the result is within the specified range.

If you wanted to generate a random float between 0 and 1, you could simply use the Math.random() method by itself:

function generateRandomFloat() {

  var randomFloat = Math.random();

  Logger.log(randomFloat);

}

These are just a couple of examples of how you can use the Math.random() method to generate random numbers in Google Apps Script.

Apps-Script-Quiz-9Download