Taking Advantage of Auto-completion of Script Editor for Google Apps Script

GasTips
Google Apps Script / script editor / google api

Gists

Introduction

This is a report for taking advantage of the auto-completion of the script editor for Google Apps Script.

In the current stage, the auto-completion is implemented in the script editor of Google Apps Script. This auto-completion can be used for not only the built-in classes and methods but also the methods for Javascript. This helps develop scripts and applications very much. In the case of the built-in classes and methods and the methods for Javascript, you can see the detailed specifications of the documents like the official documents and developer.mozilla.org.

Google Apps Script can also use Google APIs like Drive API, Sheets API, and so on. In this case, the users can request APIs by the following 2 patterns.

  1. Request directly the endpoint of APIs with the HTTP request.
  2. Request APIs with Advanced Google services.

In the case of the former, this can be used by checking the official documents of the APIs. The official documents of the Google APIs are written with detailed information. In the case of the latter, the official document says as follows.

The script editor’s autocomplete function usually provides enough information to get started.

However, I often see questions related to how to use the APIs with Advanced Google services on Stackoverflow. From this situation, it is considered that when the detailed information for using APIs of Advanced Google services is published, it might be useful for a lot of users. This report introduces the detailed method for simply using Advanced Google services with auto-completion.

Auto-completion of Script Editor

As preparation for testing the following situations, please open the script editor of Google Apps Script on your browser. And, enable Sheets API and Drive API at Advanced Google services. As a sample situation, I would like to introduce the auto-completion of the script editor using Sheets API and Drive API.

Situation 1

In this sample situation, Sheets.Spreadsheets.get() is used. Ref: Method: spreadsheets.get

When you input “Sheets” into the script editor, you can see some candidates by the auto-completion of the script editor. And, when you write Sheets.Spreadsheets.get(), you can see detailed information about the “get” method of “Sheets.Spreadsheets” in a dialog as following image.

From this dialog, you can see that the arguments of Sheets.Spreadsheets.get are as follows.

  • 1st argument is spreadsheetId: string.
  • 2nd argument is optionalArgs: Object.

The value of Sheets_v4.Sheets.V4.Schema.Spreadsheet is returned. Also, you can see the detailed information about this method as follows.

The spreadsheet to request.

Returns the spreadsheet at the given ID. The caller must specify the spreadsheet ID. By default, data within grids is not returned. You can include grid data in one of 2 ways: _ Specify a field mask listing your desired fields using the fields URL parameter in HTTP _ Set the includeGridData URL parameter to true. If a field mask is set, the includeGridData parameter is ignored For large spreadsheets, as a best practice, retrieve only the specific spreadsheet fields that you want. To retrieve only subsets of spreadsheet data, use the ranges URL parameter. Ranges are specified using A1 notation. You can define a single cell (for example, A1) or multiple cells (for example, A1:D5). You can also get cells from other sheets within the same spreadsheet (for example, Sheet2!A1:C4) or retrieve multiple ranges at once (for example, ?ranges=A1:D5&ranges=Sheet2!A1:C4). Limiting the range returns only the portions of the spreadsheet that intersect the requested ranges.

In order to correspond these values and the official document, please open the official document of “Method: spreadsheets.get”.

The values of spreadsheetId and optionalArgs are seen in the official document as follows.

  • spreadsheetId is the spreadsheet ID of the target Spreadsheet.
  • optionalArgs is ranges[], includeGridData, and from Object of optionalArgs: Object, it is found that the value of optionalArgs is like {ranges: ["Sheet1!A1",,,], includeGridData: true}. The value of field is also included in optionalArgs.

About the explanation of spreadsheetId and optionalArgs, you can see them as “Path parameters” and “Query parameters” in the official document. Sheets_v4.Sheets.V4.Schema.Spreadsheet of the returned value can be seen at “REST Resource: spreadsheets” of the official document.

Situation 2

In this sample situation, Sheets.Spreadsheets.batchUpdate() is used. Ref: Method: spreadsheets.batchUpdate

When you input “Sheets” into the script editor, you can see some candidates by the auto-completion of the script editor. And, when you write Sheets.Spreadsheets.batchUpdate(), you can see detailed information on the “batchUpdate” method of “Sheets.Spreadsheets” in a dialog as following image.

From this dialog, you can see that the arguments of Sheets.Spreadsheets.batchUpdate are as follows.

  • 1st argument is resource: Sheets_v4.Sheets.V4.Schema.BatchUpdateSpreadsheetRequest.
  • 2nd argument is spreadsheetId: string.

In the above image, the 3rd argument is not shown. But, in the case of Google API, the property of fields can be used as the query parameter. So, it is considered that optionalArgs is included as the 3rd argument. I guess that the situation without showing optionalArgs of the 3rd argument might be a bug. For example, when { fields: "spreadsheetId" } is used as the 3rd argument of Sheets.Spreadsheets.batchUpdate(), only the Spreadsheet ID is returned.

From the above image, you can see the detailed information about Sheets.Spreadsheets.batchUpdate() as follows.

Applies one or more updates to the spreadsheet. Each request is validated before being applied. If any request is not valid then the entire request will fail and nothing will be applied. Some requests have replies to give you some information about how they are applied. The replies will mirror the requests. For example, if you applied 4 updates and the 3rd one had a reply, then the response will have 2 empty replies, the actual reply, and another empty reply, in that order. Due to the collaborative nature of spreadsheets, it is not guaranteed that the spreadsheet will reflect exactly your changes after this completes, however it is guaranteed that the updates in the request will be applied together atomically. Your changes may be altered with respect to collaborator changes. If there are no collaborators, the spreadsheet should reflect your changes.

From the official document of “Method: spreadsheets.batchUpdate”, resource can be seen at “Request body”. spreadsheetId` is the spreadsheet ID of the target Spreadsheet.

When you want to create the request body of resource, there are 2 patterns.

You can create the request body at “Try this method” in the official document. At “Try this method”, you can also test the created request body. The created request body can be directly used as the value of resource.

As another approach for creating the request body, you can also do it using the auto-completion of the script editor under Advanced Google services enabled. When Sheets API is used with Advanced Google services, the request body can be created as follows. In this sample, a new sheet is inserted using the Sheets API.

function sample1() {
  const spreadsheetId = "###"; // Please set Spreadsheet ID.
  const newSheetName = "sample"; // Please set new sheet name.

  // Create request body.
  const addSheetRequest = Sheets.newAddSheetRequest();
  const sheetProperties = Sheets.newSheetProperties();
  sheetProperties.title = newSheetName;
  addSheetRequest.properties = sheetProperties;
  const request = Sheets.newRequest();
  request.addSheet = addSheetRequest;
  const requests = (Sheets.newBatchUpdateSpreadsheetRequest().requests = [
    request,
  ]);

  // Request Sheets API.
  const res = Sheets.Spreadsheets.batchUpdate({ requests }, spreadsheetId, {
    fields: "spreadsheetId",
  });
  console.log(res); // { spreadsheetId: '###' }
}

Here, in this sample script, requests is [{"addSheet":{"properties":{"title":"sample123"}}}]. It is found that the valid request body can be created. You can create the request body for each Google API using Advanced Google services like this.

Unfortunately, the method for creating the request body using auto-completion with Advanced Google services has never been published in the official document. So, in this case, it is required to use the script by confirming the explanation showing with the auto-completion.

Situation 3

In this sample situation, Drive.Files.insert() is used. Ref: Method: files.insert In the current stage, Drive API v2 can be used with Advanced Google services.

When you input “Drive” into the script editor, you can see some candidates by the auto-completion of the script editor. And, when you write Drive.Files.insert(), you can see detailed information on the “insert” method of “Drive.Files” in a dialog as the following image.

From this dialog, you can see that the arguments of Drive.Files.insert are as follows.

  • 1st argument is resource: Drive_v2.Drive.V2.Schema.File.
  • 2nd argument is mediaData: Blob. If you are not required to use Blob, you can set null instead the Blob.
  • 3rd argument is optionalArgs: Object.

The value of Drive_v2.Drive.V2.Schema.File is returned. Also, you can see the detailed information about this method as follows.

Inserts a new file. This method supports an /upload URI and accepts uploaded media with the following characteristics: - Maximum file size: 5,120 GB - Accepted Media MIME types:/ Note: Specify a valid MIME type, rather than the literal / value. The literal / is only used to indicate that any valid MIME type can be uploaded. For more information on uploading files, see Upload file data. Apps creating shortcuts with files.insert must specify the MIME type application/vnd.google-apps.shortcut. Apps should specify a file extension in the title property when inserting files with the API. For example, an operation to insert a JPEG file should specify something like “title”: “cat.jpg” in the metadata. Subsequent GET requests include the read-only fileExtension property populated with the extension originally specified in the title property. When a Google Drive user requests to download a file, or when the file is downloaded through the sync client, Drive builds a full filename (with extension) based on the title. In cases where the extension is missing, Drive attempts to determine the extension based on the file’s MIME type.

In order to correspond these values and the official document, please open the official document of “Method: files.insert”.

The values of spreadsheetId and optionalArgs are seen in the official document as follows.

  • resource is the request body. You can see this in the official document. Ref
  • mediaData is a blob.
  • optionalArgs is the values showing as “Query parameters” in the official document. Ref In this case, please set the values as JSON objects.

When you want to create the request body of resource, there are 2 patterns.

You can create the request body at “Try this method” in the official document. At “Try this method”, you can also test the created request body. The created request body can be directly used as the value of resource.

As another approach for creating the request body, you can also do it using the auto-completion of the script editor under Advanced Google services enabled. When Drive API is used with Advanced Google services, the request body can be created as follows. In this sample, a new shortcut is created using Drive API.

function sample2() {
  const shortcutName = "sample shortcut"; // Please set shortcut filename.
  const targetFileId = "###"; // Please set the target ID.
  const parentFolderId = "###"; // Please set parent folder ID.

  // Create request body.
  const newFile = Drive.newFile();
  newFile.title = shortcutName;
  newFile.mimeType = MimeType.SHORTCUT;
  const parent = Drive.newParentReference();
  parent.id = parentFolderId;
  newFile.parents = [parent];
  const newFileShortcutDetails = Drive.newFileShortcutDetails();
  newFileShortcutDetails.targetId = targetFileId;
  newFile.shortcutDetails = newFileShortcutDetails;

  // Request Drive API.
  const res = Drive.Files.insert(newFile, null, { fields: "id" });
  console.log(res); // { id: '###' }
}

Here, in this sample script, newFile is {"title":"sample shortcut","mimeType":"application/vnd.google-apps.shortcut","parents":[{"id":"root"}],"shortcutDetails":{"targetId":"###"}}. It is found that the valid request body can be created. You can create the request body for each Google API using Advanced Google services like this.

Unfortunately, the method for creating the request body using auto-completion with Advanced Google services has never been published in the official document. So, in this case, it is required to use the script by confirming the explanation showing with the auto-completion.

Note

From the above explanation, it is found that the detailed information for using each method of APIs of Advanced Google services can be confirmed by the auto-completion of the script editor. It is considered that this indicates the importance of the auto-completion of Advanced Google services with the script editor. I believe that if AI-assisted auto-completion is implemented in future updates, it will be more useful and important for developing applications using Google Apps Script. I would like to expect such a situation.

References

 Share!