Learn How to Extend Google Drive with Google Apps Script

Manage Google Drive With Apps Script

Intro

Pre-Requisite

Settings

HomePages

Homepage Triggers

Item Selected Trigger

Oauth Scopes

{
"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

Assigning Apps Scripts Functions to Triggers

// On homepage trigger function
let onDriveHomePageOpen = () => homepageCard();
// On Item selected Trigger function
let onDriveItemsSelected = (e) => itemSelectedCard(e);

Designing Behaviour of Cards

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();
}

Create Non-Contextual Card

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();
}
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();
}
  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

let nxtButtonSection = CardService.newCardSection();
let nxtButtonAction = CardService.newAction()
.setFunctionName("handleNextButtonClick");
// 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);

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

/* 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
}
  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. The string 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

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();
}

Publish Add-On in GCP for Testing

  1. Create a standard GCP project.
  2. Integrate a project with an apps script project.

Final Code

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();
}
/* 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
}

Summary

  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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store