Streamline your workflow with Google Apps Script

Google Sheets is a spreadsheet application provided by Google. When it comes to spreadsheet software, Microsoft Excel is the most well-known, but Excel needs to be installed on each device to use. Google Sheets, on the other hand, is managed in the cloud, which makes collaborative editing and information sharing flow smoothly even when team members are apart.
While that convenience is a real strength, the very ease of sharing also calls for caution. If a sheet includes personal information, for example, don't enable sharing casually—grant view permissions only to the people who actually need it. Setting appropriate sharing scopes and edit permissions, and using these convenient tools properly, is essential.

Google Apps Script is a scripting language that lets you customize and extend G Suite services, including Google Sheets. Because it is based on JavaScript, many people find it surprisingly approachable, even as a first scripting language.

In this article, we'll walk through how to build a schedule generator tool. The tool works like this: you enter the start and end dates of the schedule you want to create, click [Schedule] > [Create] in the menu bar, and the specified schedule is generated. Because it uses only basic Google Apps Script features, you can grasp the big picture even though it stays simple.

Here is the finished code.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Schedule') // Create the custom menu
    .addItem('Create', 'createSche')
    .addToUi();
}

function createSche() {
  var result = Browser.msgBox("Create a schedule?", Browser.Buttons.OK_CANCEL);
  if (result == "ok"){ // Run only when OK is clicked
    var spreadsheet = SpreadsheetApp.getActive(); 
    var sheet = spreadsheet.getActiveSheet(); // Get the active sheet
    var startday = sheet.getRange("D1"); // Start date
    var endday = sheet.getRange("D2"); // End date
    sheet.getRange("B5").setValue(startday.getValue()); // Insert the start date in the base row
    
    var startDate = Moment.moment(startday.getValue()); 
    var endDate = Moment.moment(endday.getValue()); 
    var period = endDate.diff(startDate, 'd'); // Calculate the span between start and end
    
    var rownum = 6;
    for(i = rownum; i < rownum + period -1; i++) {
      sheet.getRange(i,1,1,11).copyTo(sheet.getRange(i + 1,1));  // Copy rows for the calculated span
    }
    Browser.msgBox("Schedule generation complete.");
  }
}

Now, let's walk through the development steps.

With Google Apps Script, you can start developing immediately without setting up a development environment. From the spreadsheet's menu bar, click [Tools] > [Script editor] to open the editor. This is where you do your development. Deploying is just as easy: simply saving the function is enough.

First, let's create a template on the spreadsheet.
Create input cells for the schedule's start date and end date. Place any dates of your choosing as initial values.

Next, let's create the template for the schedule itself.
Create the base row that marks the start of the table (row 5 in the demo tool) and the copy row that follows (row 6). In cell A5, enter the formula ’=B5’; in cell C5, enter ’=text(B5,”ddd”)’. Then, in cell A6, enter ’=B5+1’; in cell B6, enter ’=B5+1’; and in cell C6, enter ’=text(B6,”ddd”)’.
For cell data types, change them to your preference via [Menu bar] > [Format].

That completes the template.

Now we're moving into development inside the editor.
First, we'll create a custom menu. Because we want the code to run when the spreadsheet opens, we'll use the event handler called onOpen.

function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Schedule')
.addItem('Create', 'createSche')
.addToUi();
}

Save the script and reload the spreadsheet, and the custom menu now appears.

Next is the implementation of the main feature: schedule creation.
We want this to run when the user clicks the custom menu we just made—[Schedule] > [Create]—so we'll name the function createSche, and write our logic inside it.

function createSche() {
}

Before running the logic, we'll show a confirmation message box and run the rest only if the user selects [OK].

var result = Browser.msgBox("Create a schedule?", Browser.Buttons.OK_CANCEL);
  if (result == "ok"){
}

Save what you have so far and click [Schedule] > [Create]. The message box appears.

Google Apps Script supports using libraries. Here, let's use moment.js, a library for handling dates. From the script editor, click [Resources] > [Libraries] and the library settings modal will appear.

Enter the script ID in [Add a library].
moment.js script ID: MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48

Choose a version and enable [Development mode], and you're done. In this article we'll use moment.js to calculate the span between the start date and the end date.

var startDate = Moment.moment(startday.getValue()); 
var endDate = Moment.moment(endday.getValue()); 
var period = endDate.diff(startDate, 'd');

Using a for loop, we generate the schedule for the number of days we calculated earlier.

var rownum = 6;
for(i = rownum; i < rownum + period -1; i++) {
    sheet.getRange(i,1,1,11).copyTo(sheet.getRange(i + 1,1));
}

Save the editor, then enter your desired start and end dates in the spreadsheet. Click [Schedule] > [Create] in the custom menu, and if the specified schedule is generated, you're done.

How did you find it? We hope you can see that, even as a first-timer, you can put together a macro quite easily. There's plenty you can automate with macros: using a spreadsheet as a database, generating documents automatically from a template file, and more. If you can customize Google Sheets—already excellent for data sharing—to fit your own organization, you can drive even greater workflow efficiency. Remote work brings its share of inconveniences, including the inability to meet face to face. We hope this article helps spark efforts to build an internal environment where work flows smoothly across distance, and to advance workflow efficiency more broadly.