Google Apps Script Interview Questions

What is Google Apps Script and how does it work?

Answer: Google Apps Script is a cloud-based scripting language that allows you to automate tasks in Google Sheets, Docs, and other Google products. It is based on JavaScript and provides a way to extend the functionality of Google products by creating custom scripts. These scripts can be executed in response to certain events, such as a user opening a document or a form being submitted.

What is the difference between a standalone script and a bounded script in Google Apps Script?

Answer: A standalone script is a script that is not tied to any particular Google product, while a bounded script is a script that is tied to a specific Google product, such as Google Sheets or Google Docs. Standalone scripts can be accessed and executed from anywhere, while bounded scripts can only be accessed from the product they are bound to. Bounded scripts have access to the services and APIs of the product they are bound to, while standalone scripts can access a wider range of Google services.

How do you create a custom menu in Google Sheets using Google Apps Script?

Answer: You can create a custom menu in Google Sheets using the addMenu() method of the SpreadsheetApp class. Here is an example:

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu(‘My Menu’)

      .addItem(‘Menu Item 1’, ‘menuItem1’)

      .addItem(‘Menu Item 2’, ‘menuItem2’)

      .addToUi();

}

function menuItem1() {

  // code to run when menu item 1 is selected

}

function menuItem2() {

  // code to run when menu item 2 is selected

}

This code creates a custom menu called “My Menu” with two menu items, “Menu Item 1” and “Menu Item 2”. The onOpen() function is a special function that is automatically called when the sheet is opened, and it creates the custom menu. The menuItem1() and menuItem2() functions are called when the corresponding menu items are selected.

How do you send an email from Google Sheets using Google Apps Script?

Answer: You can send an email from Google Sheets using the MailApp class. Here is an example:

function sendEmail() {

  var recipient = “example@example.com”;

  var subject = “Test email”;

  var body = “This is a test email”;

  MailApp.sendEmail(recipient, subject, body);

}

This code sends an email to the specified recipient with the specified subject and body.

How do you get the value of a cell in Google Sheets using Google Apps Script?

Answer: You can get the value of a cell in Google Sheets using the getValue() method of the Range class. Here is an example:

function getCellValue() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet1”);

  var cell = sheet.getRange(“A1”);

  var value = cell.getValue();

  Logger.log(value);

}

This code gets the value of cell A1 in the sheet called “Sheet1” and logs it to the console.

How do you get the current date and time in Google Sheets using Google Apps Script?

Answer: You can get the current date and time in Google Sheets using the new Date() constructor. Here is an example:

function getCurrentDateTime() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet1”);

  var cell = sheet.getRange(“A1”);

  var dateTime = new Date();

  cell.setValue(dateTime);

}

This code gets the current date and time and writes it to cell A1 in the sheet called “Sheet1”.

How do you create a trigger in Google Apps Script?

Answer: You can create a trigger in Google Apps Script using the ScriptApp.newTrigger() method. Here is an example:

function createTrigger() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet1”);

  ScriptApp.newTrigger(“myFunction”)

    .forSpreadsheet(sheet)

    .onEdit()

    .create();

}

function myFunction() {

  // code to run when the trigger is fired

}

This code creates a trigger that fires the myFunction() function whenever the sheet named “Sheet1” is edited.

How do you access data from a Google Sheet in a web app using Google Apps Script?

Answer: You can access data from a Google Sheet in a web app using the google.script.run API. Here is an example:

function getData() {

  google.script.run.withSuccessHandler(processData).getData();

}

function processData(data) {

  // code to process the data

}

This code calls a server-side function called getData() using the google.script.run API and specifies a client-side function called processData() to handle the returned data.

How do you deploy a Google Apps Script as a web app?

Answer: You can deploy a Google Apps Script as a web app using the Publish menu in the Apps Script editor. Here are the steps:

1. Open the script editor for your script.
2. Click on the Publish menu and select Deploy as web app.
3. Set the deployment options, such as the version and access level.
4. Click on the Deploy button.
5. Copy the URL of the deployed web app and share it with others.

Once the script is deployed as a web app, anyone with the URL can access it in their web browser. The web app can also be embedded in a Google Site or other web page using an iframe.

How do you send an email from Google Sheets using Google Apps Script?

Answer: You can send an email from Google Sheets using Google Apps Script and the MailApp.sendEmail() method. Here is an example:

function sendEmail() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet1”);

  var email = sheet.getRange(“A1”).getValue();

  var subject = “This is the subject”;

  var message = “This is the message body.”;

  MailApp.sendEmail(email, subject, message);

}

This code gets the email address from cell A1 in the sheet named “Sheet1”, sets the email subject and message body, and then sends the email using the MailApp.sendEmail() method.

You can also add attachments to the email by using the MailApp.sendEmail() method with an optional options parameter. Here is an example:

function sendEmailWithAttachment() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet1”);

  var email = sheet.getRange(“A1”).getValue();

  var subject = “This is the subject”;

  var message = “This is the message body.”;

  var file = DriveApp.getFileById(“1234567890abcdefghijklmnopqrstuvwxyz”);

  MailApp.sendEmail(email, subject, message, {

    attachments: [file.getAs(MimeType.PDF)]

  });

}

This code gets the email address from cell A1 in the sheet named “Sheet1”, sets the email subject and message body, gets a file by ID and attaches it to the email using the options parameter of the MailApp.sendEmail() method.