Basic Google App Script for work

A simple tutorial for beginners with Google App Script

I. Introduction

This article is for people who wants to automate repetitive tasks and work smarter and faster in the 4.0 world. I will use simple words and explainations for readers. I think this will be a good tutorial for office staffs.

I will show you 4 jobs that Google App Script can help you in your work. I will notice every details you need to pay attention to. Before we start I have some things that you have to know.

First, I just show some simple codes you can use and if you want to customize it but you don't know how to write codes you can use AI. You can ask AI such as ChatGPT, Gemini to customize the code. However, you need to use suitable form and explain in detail for them. You can follow a method that I call "RICE":

• Role: Give AI a role, like your assistant who fixs the code for you.

• Information: Detail information about what do you want from the code

• Context: What job do you do and the context of using that code

• Example: Describe what you want to get such as: "After running the code, I can do A and then get B"

If there's any problems with code wrote by AI, you can take a screenshot to them and write a prompt order to fix that bug. Write detail prompts can be difficult for beginners, but you will get used to it soon.

Next, this is how you run an App Script and some small tips for you.

To run an App Script, click "Extensions" and choose Apps Script

In the above example, this will send mails to email in column B "Email address" with the content from "Email content". When you open App Script it will look like this:

Click "Run" to run the code.

Tips: The first time you run the code in the code editor the whole sheet must be blank because when run the code it create the "Send email" above and when click that it will send an email. But when you run in the code editor it will also send an email so at the first time it must be blank.

If your code have to get your permisson, it will show like this:

You just have to click "Review permissions"

If google show this, just click "Advanced"

After that just click "Go to [Project_name] (unsafe)" Don't worry, Google say that because they don't know the dude who wrote it. You can check the code again if you want, but all codes are safe.

Now you can start to know some examples for App Script (I know you won't read anything above and just see II. App Scipt usages first, if there's any bugs, you will comeback and read I. Instruction)

II. App Script usages

a. Automate email sending

You need 3 column like in the picture.

Every row in "Email address" is one email that google will send to. Like in the photo, after click "Send Email" it will send an email to [e-mail] (That's our email too).

Here's the code:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Send Email')
      .addItem('Send Formatted Email', 'sendFormattedEmail')
      .addToUi();
}

function sendFormattedEmail() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var emailAddresses = sheet.getRange("B2:B").getValues().filter(row => row[0]); 
  var emailTitle = sheet.getRange("C2").getValue(); 
  var emailContent = sheet.getRange("D2").getValue(); 

  var formattedContent = emailContent.replace(/\n/g, "<br>"); 

  emailAddresses.forEach(function(row) {
    var emailAddress = row[0];
    if (emailAddress) {
      MailApp.sendEmail({
        to: emailAddress,
        subject: emailTitle,
        htmlBody: formattedContent
      });
    }
  });

  SpreadsheetApp.getUi().alert("Completed!");
}

You can see that the email is also formated as the original

b. Create repetitive reports or something like that

When you run the code, it will ask what week you want and ID of your doc files. You can create an original file with repetitive form

Just like this and coppy it into files then coppy their IDs.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("📄 Generate Report")
    .addItem("Create Weekly Report", "sendDataToDoc")
    .addToUi();
}

function sendDataToDoc() {
  var ui = SpreadsheetApp.getUi();

  // 1. Ask for the week number
  var weekResponse = ui.prompt("Enter the week number:", ui.ButtonSet.OK_CANCEL);
  if (weekResponse.getSelectedButton() !== ui.Button.OK || !weekResponse.getResponseText().trim()) {
    ui.alert("Operation canceled or no week number provided.");
    return;
  }
  var weekNumber = weekResponse.getResponseText().trim();

  // 2. Ask for the Google Doc ID (of the template)
  var docResponse = ui.prompt("Enter the Google Doc ID (existing template):", ui.ButtonSet.OK_CANCEL);
  if (docResponse.getSelectedButton() !== ui.Button.OK || !docResponse.getResponseText().trim()) {
    ui.alert("Operation canceled or no document ID provided.");
    return;
  }
  var fileId = docResponse.getResponseText().trim();

  // 3. Read from active sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var selectedRow = data.find(row => row[0] == weekNumber);

  if (!selectedRow) {
    ui.alert("Week " + weekNumber + " not found in the spreadsheet.");
    return;
  }

  var [_, revenue, orderCount, techIssues, complaints] = selectedRow;

  try {
    var doc = DocumentApp.openById(fileId);
    var body = doc.getBody();

    var placeholders = {
      "{WEEK}": weekNumber,
      "{REVENUE}": revenue,
      "{ORDER_COUNT}": orderCount,
      "{TECH_ISSUES}": techIssues,
      "{COMPLAINTS}": complaints
    };

    for (var key in placeholders) {
      var foundElement = body.findText(key);
      while (foundElement) {
        var textElement = foundElement.getElement();
        textElement.setText(textElement.getText().replace(key, placeholders[key]));
        foundElement = body.findText(key);
      }
    }

    ui.alert("✅ Document updated successfully!\n\nOpen it here:\n" + doc.getUrl());

  } catch (e) {
    ui.alert("❌ Error accessing or modifying the document:\n" + e.message);
  }
}

Note: Write these phrases in places that will be replaced with information from the sheet.

"{WEEK}": weekNumber,
"{REVENUE}": revenue,
"{ORDER_COUNT}": orderCount,
"{TECH_ISSUES}": techIssues,
"{COMPLAINTS}": complaints

c. Create Google forms

Create questions and answers, each row is an answer. Write [OPEN-TEXT-QUESTION] for open text question

function createFormFromSheet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  const questions = sheet.getRange("B2:B").getValues().flat().filter(q => q);
  const optionsList = sheet.getRange("C2:C" + (questions.length + 1)).getValues().flat();

  const form = FormApp.create("Generated Form");

  for (let i = 0; i < questions.length; i++) {
    const question = questions[i];
    const options = optionsList[i];

    if (options === '[OPEN-TEXT-QUESTION]') {
      form.addTextItem().setTitle(question);
    } else {
      const choices = options.split('\n'); // Không cần trim nếu nhập đúng
      form.addMultipleChoiceItem()
          .setTitle(question)
          .setChoiceValues(choices);
    }
  }

  SpreadsheetApp.getUi().alert("Form created!\nLink: " + form.getEditUrl());
}

Just edit a little bit, you'll have a complete form.

d. Create schedule on Google calendar

Create a menu like that with all columns

It can works very well

Here's the code:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  if (ui) {
    ui.createMenu('Custom Menu')
      .addItem('Create Calendar', 'createCalendarEvents')
      .addToUi();
  }
}

function createCalendarEvents() {
  try {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    if (!spreadsheet) {
      throw new Error('No active spreadsheet. Please open a spreadsheet first.');
    }
    var sheet = spreadsheet.getActiveSheet();
    var data = sheet.getDataRange().getValues();
    var createdEvents = new Set(); 

    for (var i = 1; i < data.length; i++) {
      var startTime = new Date(data[i][1]); 
      if (isNaN(startTime.getTime())) {
        Logger.log('Invalid date at row ' + (i + 1));
        continue;
      }

      var endTime = new Date(startTime.getTime() + 30 * 60 * 1000); 
      var title = data[i][4] || 'No Title'; 
      var location = data[i][3] || ''; 
      var description = data[i][5] || ''; 

      var eventKey = startTime.toISOString() + '-' + title;
      
      if (!createdEvents.has(eventKey)) {
        CalendarApp.getDefaultCalendar().createEvent(
          title,
          startTime,
          endTime,
          { location: location, description: description }
        );
        createdEvents.add(eventKey);
      }
    }
    Logger.log('Calendar events created successfully!');
  } catch (e) {
    Logger.log('Error: ' + e.message);
  }
}

And that's all things I want to share with you. I hope you can learn something to automatic your work.

- Pear -