How to Create a Spreadsheet Using Google Apps Script?

Google Apps Script is a powerful platform from Google that allows developers to automate and extend the functionality of different Google products. One of the most popular applications of this platform is the creation of spreadsheets. In this article, we will discuss how to use Google Apps Script to create a new spreadsheet.

Step 1: Create a New Spreadsheet

To start, we need to create a new spreadsheet. We can do this by calling the SpreadsheetApp class and using the create method. Here’s the code:

function createNewSpreadsheet() {
    var ss = SpreadsheetApp.create("My New Spreadsheet");
    Logger.log("New Spreadsheet created with the URL: " + ss.getUrl());
}

When we run this function, it will create a new spreadsheet with the given name and return the URL of the newly created spreadsheet.

Step 2: Adding Data to the Spreadsheet

Once we’ve created a new spreadsheet, we can add data to it. We can use the getActiveSheet method to get the active sheet, and then add data to it using the setValue method. Here’s the code:

function addDataToSpreadsheet() {
    var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1u5_8M03Ryn056mIjuAiMdoSP16FTJY3qQkE/edit");
    var sheet = ss.getActiveSheet();
    sheet.getRange("A1").setValue("Name");
    sheet.getRange("B1").setValue("Age");
    sheet.getRange("A2").setValue("John");
    sheet.getRange("B2").setValue(25);
    sheet.getRange("A3").setValue("Jane");
    sheet.getRange("B3").setValue(30);
}

In this function, we’re opening an existing spreadsheet using its URL, getting the active sheet using the getActiveSheet method, and adding some data to it using the setValue method.

Step 3: Formatting the Spreadsheet

We can also format the spreadsheet using Google Apps Script. For example, we can set the font size, font color, background color, and other formatting options. Here’s the code:

function formatSpreadsheet() {
    var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1u5_8M03Ryn056mIjuAiMdoSP16FTJY3qQkE/edit");
    var sheet = ss.getActiveSheet();
    sheet.getRange("A1:B1").setBackground("#4285F4").setFontColor("#FFFFFF").setFontWeight("bold").setFontSize(16);
}

In this function, we’re setting the background color, font color, font weight, and font size of the range A1:B1.

Step 4: Sharing the Spreadsheet

Finally, we can also share the newly created spreadsheet with other users using Google Apps Script. Here’s the code:

function shareSpreadsheet() {
    var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1u5_8M03Ryn056mIjuAiMdoSP16FTJY3qQkE/edit");
    ss.addEditor("user1@example.com");
    ss.addViewer("user2@example.com");
    ss.removeEditor("user3@example.com");
}

In this function, we’re adding an editor and a viewer to the spreadsheet, and removing an existing editor.