There are a lot of APIs on Google. When we use Google Drive APIs, they usually have “fields” as a resource. The parameter “fields” gives various information which is selected to us. This is one of important parameters. And this can be used at Google Apps Script (GAS) although that version is v2. About how to use it, there are some documents. But it is difficult to find how to use it at GAS. So I would like to write down here as a memorandum. Most parameters for APIs have to be expressed as JSON. However, the expressions are different for each API. I would like to introduce this using some samples. This is for GAS.

I introduce 2 kinds of methods. One is to use curl. Another is to use wget. At this time, I could know that wget can be also used as same as curl.

In order to use this, at first, please retrieve your access token and enable Drive API.

1. File ID

Retrieve file id from file name.

curl -X GET -sSL \
    -H 'Authorization: Bearer ### Access token ###' \
    'https://www.googleapis.com/drive/v3/files?q=name="### FileName ###"&fields=files(id,name)'

wget -q --header='Authorization: Bearer ### Access token ###' \
    'https://www.googleapis.com/drive/v3/files?q=name="### FileName ###"&fields=files(id,name)' \

Reference : https://developers.google.com/drive/v3/reference/files/list

I have never heard this. I would like to use this from now.

package main

import "fmt"

type st struct {
    data1 int
    data2 int
}

func main() {
    c1 := make(chan *st, 1)
    c2 := make(chan *st, 1)
    c1 <- &st{1, 2}
    c2 <- <-c1
    close(c1)
    close(c2)
    res, _ := <-c2
    fmt.Println(res.data2)
}

>>> 2

This is a sample script of GAS for converting file name to file id on Google Drive. Drive API is used for this. So please enable Drive API at Advanced Drive Services and Google API Console.

function nameToId(filename){
  return [i.id for each (i in Drive.Files.list({q: "title='" + filename + "' and trashed=false"}).items)];
}

This script transposes from an array with n rows x m columns to the array with m rows x n columns. In this script, you can use array of n != m .

array = [
    [a1, b1, c1, d1, e1],
    [a2, b2, c2, d2, e2],
    [a3, b3, c3, d3, e3],
    [a4, b4, c4, d4, e4],
    [a5, b5, c5, d5, e5],
    [a6, b6, c6, d6, e6],
    [a7, b7, c7, d7, e7],
    [a8, b8, c8, d8, e8],
    [a9, b9, c9, d9, e9],
    [a10, b10, c10, d10, e10]
]

var result = [];
for (i in array[0]){
    result.push(array.map(function(e,j){return e[i]}));
}

result = [
    [a1, a2, a3, a4, a5, a6, a7, a8, a9, a10],
    [b1, b2, b3, b4, b5, b6, b7, b8, b9, b10],
    [c1, c2, c3, c4, c5, c6, c7, c8, c9, c10],
    [d1, d2, d3, d4, d5, d6, d7, d8, d9, d10],
    [e1, e2, e3, e4, e5, e6, e7, e8, e9, e10]
]

Overview

In this article, files can be downloaded without authorization.

Description

When we download files from Google Drive, it usually has to use Drive API. In order to use Drive API, access token is required. If you want to make your friends download files from your Google Drive, the authorization process is to take time. Also Web Link for each files can be used. But it has to set for each files. So I proposal this.

This is a sample script for creating a folder tree including all folders in Google Drive. For each element, parent folder and sub folder are retrieved.

Script :

  var results = (function(folder, folderSt, results){
    var ar = [];
    var folders = folder.getFolders();
    while(folders.hasNext()) ar.push(folders.next());
    folderSt += folder.getName() + "(" + folder.getId() + ")#_aabbccddee_#";
    var array_folderSt = folderSt.split("#_aabbccddee_#");
    array_folderSt.pop()
    results.push(array_folderSt);
    ar.length == 0 && (folderSt = "");
    for (var i in ar) arguments.callee(ar[i], folderSt, results);
    return results;
  })(DriveApp.getRootFolder(),"",[]);

Result :

[
    [foldername1(folder1 id)],
    [foldername1(folder1 id), sub foldername1s(sub folde1s id)],
    [foldername1(folder2 id)],
    [foldername2(folder2 id), sub foldername2s1(sub folde2s1 id)],
    [foldername2(folder2 id), sub foldername2s1(sub folde2s1 id), sub foldername2s2(sub folde2s2 id)]
]

By the way :

ar.length == 0 && (folderSt = "");

if (ar.length == 0) {
  folderSt = "";
}

Both scripts are the same meaning.

Released a CLI tool for easily retrieving accesstoken from Google OAuth2. The title is gogauth. When I have seen stackoverflow, I knew that it is difficult to retrieve accesstoken from Google OAuth2. So I made this.

Features of this CLI tool are as follows.

1. Retrieves easily accesstoken from Google OAuth2 for using Drive API.

2. If you have PhantomeJS, this retrieves “code” from Google using it. So you don’t need to launch your browser for retrieving “code”.

This is a script to export a CSV file from spreadsheet and make an user download it. When the users download it, they can download by push a button made by this script.

In order to use this script, put both HTML and script in a GAS project.

html :

This file name is “download.html”.

<!DOCTYPE html>
<html>
  <body>
    Download CSV?
    <form>
      <input type="button" value="ok" onclick="google.script.run
                                              .withSuccessHandler(executeDownload)
                                              .saveAsCSV();" />
    </form>
  </body>
  <script>
    function executeDownload(url) {
      window.location.href = url;
    }
  </script>
</html>

Script :

This is a script to send e-mail with a xlsx file converted from spreadsheet as an attachment file. Access token is necessary to use this script.

function excelSender() {
  var accesstoken = "[your accesstoken]";
  var sheetID = "[sheet id]";
  var xlsxName = "[output xlsx file name]"
  var params = {
    "headers" : {Authorization: "Bearer " + accesstoken},
    "muteHttpExceptions" : true
  };
  var dUrl = "https://www.googleapis.com/drive/v3/files/" + sheetID + "/export?mimeType=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
  var xlsxlFile = UrlFetchApp.fetch(dUrl, params).getBlob().setName(xlsxName);
  MailApp.sendEmail({
    to: "[your mail address]",
    subject: "sample mail",
    body: "sample mail with an excel file",
    attachments: [xlsxlFile]
  });
}