Khadka's Coding Lounge
Managing Google Drive with Google Apps Script

Managing Google Drive with Google Apps Script

Google Apps Script For Add-Ons

Nibesh Khadka's photo
Nibesh Khadka
·

10 min read

Table of contents

Intro

Let's extend Google Drive with Apps Script to create a simple add-on, use CardService for the UI, where we'll select a few spreadsheets and pass them to the next card with navigation. You can find my other blogs on Google Apps Scripts right here.

Pre-Requisite

You'll need knowledge of JavaScript and Access to google drive. I am using apps script ide but if you want to develop in the local environment you'll find this set-up guide helpful.

Settings

Head over to dashboard, and create a new script file for the project. After that, we'll need to prep our projects as instructed below.

HomePages

According to the documentation, there're two types of homepages when you're developing add-ons for drive: Contextual and Non-Contextual.

Non-Contextual is an initial display when nothing is happening like the first screen to show after the add-on icon is clicked. Contextual is the home page/display that shows up once we perform a specific action like selecting files in the drive.

For the apps scripts functions to get called in the drive, we'll need to assign those functions to the appropriate triggers for the drive add-on in the manifest(appsscript.json) file.

Homepage Triggers

When a user clicks on the add-on icon, drive.homepageTrigger method is called upon. This method then looks for a function then calls the specified function in the manifest(appsscript.json) for further operations.

Item Selected Trigger

For contextual triggers, we'll assign the function in our apps script to drive.onItemSelectedTrigger in the manifest file.

Oauth Scopes

For the drive add-on to work the user must grant access permission. The list of permissions is known as Scopes. Details on drive-specific scopes can be found here. We'll need to provide the scopes in the appsscript.json file again as a list with "oauthScopes".

Note: If your appsscript.json file is hidden then go to settings, then check the Show "appsscript.json" manifest file in editor checkbox.

Check out the manifest file for this project below.

{
 "timeZone": "Asia/Kathmandu",
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/script.storage",
    "https://www.googleapis.com/auth/drive",
    "https://www.googleapis.com/auth/drive.file",
    "https://www.googleapis.com/auth/drive.addons.metadata.readonly"
  ],
  "addOns": {
    "common": {
      "name": "Drive Extension with Apps Script",
      "logoUrl": "provide image URL to be used as logo",
      "layoutProperties": {
        "primaryColor": "#41f470",
        "secondaryColor": "#ab2699"
      }
    },
    "drive": {
      "homepageTrigger": {
        "runFunction": "onDriveHomePageOpen",
        "enabled": true
      },
      "onItemsSelectedTrigger": {
        "runFunction": "onDriveItemsSelected"
      }
    }
  }
}

Using Apps Script to Access Google Drive

Now, on the root project folder create two files, cards and main.

Assigning Apps Scripts Functions to Triggers

main

// On homepage trigger function
let onDriveHomePageOpen = () => homepageCard();

// On Item selected Trigger function
let onDriveItemsSelected = (e) => itemSelectedCard(e);

The onDriveHomePageOpen and onDriveItemsSelected are two functions we assigned in the manifest file earlier on. These functions are in turn calling other functions which we'll create in a while. If you get an error pop up on saving the file, dismiss it for now.

Designing Behaviour of Cards

Let's create a simple homepage card to be assigned to our non-contextual trigger on the cards file.

Create Homepage Card

let homepageCard = () => {
// Create a card with a header section
  let card = CardService.newCardBuilder().setHeader(CardService.newCardHeader());
// create card section 
  let section = CardService.newCardSection();

// add heading 
  let decoratedText = CardService.newDecoratedText()
    .setText("Select Files To Update");

// add text as a widget to the card section
  section.addWidget(decoratedText);

// add the section to the card 
  card.addSection(section);

// return card as build
  return card.build();

}

Cards can be used to create UI for the add-ons for google drive.

This is a beginners blog so I am not focused on styling.

Create Non-Contextual Card

Now, let's have another card that we'll be responsible for the contextual trigger on the same file. But let's divide this code into sections to understand clearly.

1. Create a simple card UI.
let itemSelectedCard = (e) => {

  // Initial UI
  let card = CardService.newCardBuilder().setHeader(CardService.newCardHeader().setTitle("Select Sheets Update Master Sheet"));
  let filesSection = CardService.newCardSection()
  filesSection.setHeader("Selected Files");
  return card.build();
}
2. Display Selected files in UI.
let itemSelectedCard = (e) => {

  // Initial UI
  let card = CardService.newCardBuilder().setHeader(CardService.newCardHeader().setTitle("Select Sheets Update Master Sheet"));
  let filesSection = CardService.newCardSection()
  filesSection.setHeader("Selected Files");

 // New Code starts here 

// # 1
// Create a new array to hold selected files and data
  let selectedSheets = [];

// #2
// Fetch selected files data from drive through event objects
  if (e.drive.selectedItems.length > 0) {

    // Selected spreadsheets
// #3
// Among the selected items we'll be selecting only spreadsheets and push them to selected sheets
    e.drive.selectedItems.forEach(item => {
      if (item.mimeType === "application/vnd.google-apps.spreadsheet")
        selectedSheets.push(item)
    }
    );
  }

  // Create a counter to count the number of widgets added
// #4
// COunter is required to prevent error when pushing the file names into UI incase array is empty
  let widgetCounter = 0;

  for (let i = 0; i < selectedSheets.length; i++) {
    // #5
    // Create decorated text with selected files and 
    // add the decorated text to the card section
    filesSection.addWidget(CardService.newDecoratedText()
      //.setText(selectedSheets[i].title)
      .setText(e.drive.selectedItems[0].title)

    );

 // Increase widget counter per loop
 // #4
    widgetCounter += 1;
  }

  // #6
  // Add files as widgets only if widgetCounter is 1+
  //  It prevent error in case only non-spreadsheet files are selected 
  if (widgetCounter >= 1) {
    card.addSection(filesSection)
 }

  // Create Another card that has files list 
  return card.build();
}

Here(see the code for numbering like #1),

  1. Created an array to hold data on selected items.

  2. Used drive event object to fetch data on selected files.

  3. Among the selected items we filtered only spreadsheets using mimeType.

  4. We created a counter to use as a condition while adding the files as widgets in the card to prevent errors.

  5. Created a decorated text a widget, which will hold the file names of each file.

  6. Now finally added the whole files section to the card builder.

Generate Actions With Button

In Card, interactivity is possible using actions. Also, check out this sample code. Don't forget to add the scope given there, to drive the scope in your manifest file.

Let's add buttons below the files section. This button will collect selected files and pass them to another card which we'll build later on. To less complicate things, I'll break down code into smaller sections.

1. Create Button Ui with Action
  let nxtButtonSection = CardService.newCardSection();
  let nxtButtonAction = CardService.newAction()
    .setFunctionName("handleNextButtonClick");

You've noticed that handleNextButtonClick has been assigned as the function to be triggered on button click. It will handle the navigation, and points toward the next card. We'll create this function it later on.

2. Assign Parameters to be passed.
 // We'll pass only pass ids of files to the next card so that we can fetch them there with id
// #1
  let selectedSheetsIDAsStr = selectedSheets.map(item => item.id).join();

// pass the values as params
// #2
  nxtButtonAction.setParameters({
    "nextCard": "nextCard",
    "selectedSheetsIDAsStr": selectedSheetsIDAsStr,
  });

// add button to the button set 
// #3
  let nxtButton = CardService.newTextButton().setText("Next").setOnClickAction(nxtButtonAction);
  let nxtButtonSet = CardService.newButtonSet().addButton(nxtButton);

In card, parameters need to be passed via action with setParameters method as objects(#2). It's important to remember that both keys and values should be string(hence #1). Buttons can be added as a button set in the card(#3).

You've noticed that nextCard has been assigned as a parameter. That's because the function handleNextButtonClick is a general function that takes the name of the card as a parameter instead of hardcoding. This way it will be more efficient in the long run.

Add Button To Card
//  It prevent error in case only non-spreadsheet files are selected 
  if (widgetCounter >= 1) {
    card.addSection(filesSection)

    // new line
    nxtButtonSection.addWidget(nxtButtonSet);
    card.addSection(nxtButtonSection);
  }

Card Navigation

From what I understood card navigation, in short, takes a list of cards as a stack. New card to navigate to is added to the top of the stack, whereas popped from the stack to return to the previous one.

Let's create a new file, I'll name it helpers, add the following instructions.

helpers

/* This is a greneral nav function
You use it with card action and as a response, it will supply card functions from cardsInventory */
let handleNextButtonClick = (e) => {

// #1
// Extract string nextCard to pass it as key in cards inventory obj
  let nxtCard = cardsInventory[e.commonEventObject.parameters.nextCard];

  // #2
  // Convert String into List of files selected by the user
  let selectFilesIdList = e.commonEventObject.parameters['selectedSheetsIDAsStr'].split(",");

// #3
// use actionResponse to create a navigation route to the next card
  let nxtActionResponse = CardService.newActionResponseBuilder()
    .setNavigation(CardService.newNavigation().pushCard(nxtCard(selectFilesIdList))) // #4, Passing the mastersheet with params
    .setStateChanged(true)
    .build();

  return nxtActionResponse;
}


/**
 *  Create a dictionary that
 is consist of cards for navigation with appropriate keys  
 */

var cardsInventory = {
  'nextCard': nextCard
}

Let's first talk about the cardsInventory object. If you remember we passed the parameter nextCard previously as a string in itemSelectedCard function. This nextCard is the function we'll create next. But the thing is you can't pass a string and use it to reference a variable(check #1 in code). So, we're creating a dictionary that'll match appropriate keys with functions for navigation.

Inside handleNextButtonClick function:

  1. Extract the string which is key to the cardInventory object to fetch the correct card to call. We're using Events Comment Object to extract parameters passed on earlier.

  2. Strings that was passed as selected files id's, we're again converting it to the array.

  3. NewActionResponseBuilder, SetNavigation, NewNavigation, and PushCard combined are used to set a new path to the card of our choosing.

  4. Here, we're passing a list of ids as params.

Next Card To Navigate

We'll create a very simple card just enough to display the list of IDs, to let us know our code is working.

First, let's create a new file **next_card **.

var nextCard = function (lst) {

  let cardService = CardService.newCardBuilder().setHeader(CardService.newCardHeader().setTitle("Select Master Sheet To Update"));

  let filesSection = CardService.newCardSection();

  filesSection.setHeader("Selected Files");

  let widgetCounter = 0;

  let selectedFilesList = [...lst];

  selectedFilesList.forEach(id => {
    filesSection.addWidget(CardService.newDecoratedText()
      .setText(id));
    widgetCounter += 1;
  });

  if (widgetCounter >= 1) {
    cardService.addSection(filesSection);
  }


  return cardService.build();
}

The only thing new to notice here is that I am not using es6 syntax to declare a function. That's because using it caused a scoping issue and the error, function is not defined. Hence, I went to the old school with var.

Publish Add-On in GCP for Testing

To publish an add-on to the GCP follow these two instructions here.

  1. Create a standard GCP project.

  2. Integrate a project with apps script project.

Final Code

cards

let itemSelectedCard = (e) => {

  // Initial UI
  let card = CardService.newCardBuilder().setHeader(CardService.newCardHeader().setTitle("Select Sheets Update Master Sheet"));
  let filesSection = CardService.newCardSection()
  filesSection.setHeader("Selected Files");

  let nxtButtonSection = CardService.newCardSection();
  let nxtButtonAction = CardService.newAction()
    .setFunctionName("handleNextButtonClick");
  let selectedSheets = [];

  if (e.drive.selectedItems.length > 0) {
    // hostApp,clientPlatform,drive,commonEventObject
    // Selected spreadsheets
    e.drive.selectedItems.forEach(item => {
      if (item.mimeType === "application/vnd.google-apps.spreadsheet")
        selectedSheets.push(item)
    }
    );
  }

  // Create a counter to count number of widgets added
  let widgetCounter = 0;

  for (let i = 0; i < selectedSheets.length; i++) {
    // Create decorated text with selected files and 
    // add the decorated text to card section
    filesSection.addWidget(CardService.newDecoratedText()
      //.setText(selectedSheets[i].title)
      .setText(e.drive.selectedItems[0].title)

    );
    widgetCounter += 1;
  }


  // Change list of  selected sheet's id  as string to pass to next card 
  let selectedSheetsIDAsStr = selectedSheets.map(item => item.id).join();

  nxtButtonAction.setParameters({
    "nextCard": "nextCard",
    "selectedSheetsIDAsStr": selectedSheetsIDAsStr,
  });

  let nxtButton = CardService.newTextButton().setText("Next").setOnClickAction(nxtButtonAction);
  let nxtButtonSet = CardService.newButtonSet().addButton(nxtButton);


  // Add files and button section only if the widgets are present
  //  It prevent error in case only non-spreadsheet files are selected 
  if (widgetCounter >= 1) {
    card.addSection(filesSection)

    nxtButtonSection.addWidget(nxtButtonSet);
    card.addSection(nxtButtonSection);
  }

  // Create Another card that has files list 
  return card.build();
}

helpers

/* THis is a greneral nav function
You use it with card action and as reponse it will supply card functions from cardsInventory */
let handleNextButtonClick = (e) => {

  let nextCard = cardsInventory[e.commonEventObject.parameters.nextCard];
  console.log(nextCard)

  // Convert String into List
  let selectFilesIdList = e.commonEventObject.parameters['selectedSheetsIDAsStr'].split(",");

  let nxtActionResponse = CardService.newActionResponseBuilder()
    .setNavigation(CardService.newNavigation().pushCard(nextCard(selectFilesIdList)))
    .setStateChanged(true)
    .build();

  return nxtActionResponse;
}


/**
 *  Create a dictionary that
 is consist of cards for navigation with appropriate keys  
 */

var cardsInventory = {
  'nextCard': nextCard
}

Khadka's Coding Lounge

Summary

Alright, let's recall things we did in the project.

  1. Defined appscrits.json files with the appropriate scopes and triggers required for Drive Add-on.

  2. Created a simple card UI to interact with users.

  3. Fetched selected files from drive with apps script.

  4. Used Actions and Button Sets to let users interact with our card UI.

  5. Created a simple navigation logic to move between two cards.

Show Some Support

This is Nibesh Khadka from Khadka's Coding Lounge. Find my other blogs on Google Apps Scripts here. I am the owner of Khadka's Coding Lounge. We make websites, mobile applications, google add-ons, and valuable tech blogs. Hire us!, Like, Share, and Subscribe to our newsletter.

I make Google Add-Ons and can also write Google Apps Scripts for you. If you need my services let me know directly or from fiverr or upwork.

Hire Us

apps scriptJavaScript#GSuitegoogle apps scriptTutorial