Google Apps Script Tutorial: Automate Tasks with Spreadsheets

steps

1. Open a Google Sheet that you would like to automate.
2. Navigate to the top menu bar and click on Extensions > Apps Script.
3. A new tab will open, revealing the Google Apps Script Editor. By default, you'll see a file named code.gs. This is where you'll write your script.

steps

1. Replace the default function myFunction with:
   javascriptCopy

   function myFunction() {
     Logger.log("Hello World");
   }

2. Rename your project by clicking on Untitled Project and give it a name, e.g., My First Script.
3. Click the floppy disk icon to save your script.
4. Click the triangular Run button to execute the script.
5. Open the Execution Logs panel in the Script Editor to view the message "Hello World".

steps

1. In your Google Sheet, type Apps Script is awesome into cell A1.
2. Modify the myFunction function to read the value of cell A1 and log it:
   javascriptCopy

   function myFunction() {
     const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
     const value = sheet.getRange("A1").getValue();
     Logger.log(value);
   }

3. Run the script and check the execution logs for the value from A1.
4. Now modify the script to write a value to B1:
   javascriptCopy

   function myFunction() {
     const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
     sheet.getRange("B1").setValue("I learned something new!");
   }

5. Run the script and check your spreadsheet—B1 should now display I learned something new!.

steps

1. Add this function to your script:
   javascriptCopy

   function onOpen() {
     const ui = SpreadsheetApp.getUi();
     ui.createMenu("Custom Menu")
       .addItem("Run My Function", "myFunction")
       .addToUi();
   }

2. Save your script and refresh the spreadsheet.
3. You’ll see a new menu called "Custom Menu" in the spreadsheet. Click it and select "Run My Function" to execute your script.

steps

1. In the Script Editor, click the clock icon in the sidebar to access triggers.
2. Click Add Trigger and:
   • Select function to run: Choose the function you want to trigger (e.g., myFunction).
   • Select event source: Time-driven.
   • Type of time-based trigger: Hour timer.
3. Save your trigger. Authorize the script to make changes if prompted.