ggsrun was updated to v.1.5.1

• v1.5.1 (November 2, 2018)
  1. Removed a bug.
     • When a file information was retrieved, createdTime and modifiedTime couldn’t be seen and the information was incomplete.

You can check ggsrun at https://github.com/tanaikech/ggsrun.

Overview

This is a Golang library to retrieve the file list with the folder tree from the specific folder of Google Drive.

Description

When I create applications for using Google Drive, I often retrieve a file list from a folder in the application. So far, I had created the script for retrieving a file list from a folder for each application. Recently, I thought that if there is the script for retrieving the file list with the folder tree from the folder of Google Drive as a library, it will be useful for me and other users. So I created this.

ggsrun was updated to v.1.5.0

• v1.5.0 (October 27, 2018)
  1. From this version, ggsrun got to be able to download all files and folders in the specific folder in Google Drive. When all files are downloaded from a folder, the same folder structure of Google Drive is created to the local PC.
     • $ ggsrun d -f folderName or folderId
       • When the project file is downloaded, it is downloaded as a zip file. All scripts in the project is put in the zip file.
       • Also when you download a single project, you can use an option --zip or -z. By this, the downloaded project is saved as a zip file.
       • This new function can be also used for the shared folders. When you want to download the files from the shared folder, please use the folder ID of the shared folder.
  2. The file list with the folder tree in the specific folder got to be able to be retrieved.
  3. When the files are downloaded, the progression got to be able to be seen. When you want to see the progression, please use -j when you download files and folders.
  4. Files with large size got to be able to be used. In order to download files with large size (several gigabytes), files are saved by chunks.
  5. Some modifications.

You can check ggsrun at https://github.com/tanaikech/ggsrun.

Gists

This is a sample script for creating a downloaded file as a zip file using Golang. The downloaded file is not created to a file as a temporal file. The zip file is directly created. When you use this, please modify url, downloadedFileName and zipFileName.

Sample script:

package main

import (
    "archive/zip"
    "bytes"
    "fmt"
    "io"
    "io/ioutil"
    "log"
    "net/http"
    "os"
    "time"
)

func main() {
    url := "https://localhost/sample.png"
    downloadedFileName := "sample.png"
    zipFileName := "sample.zip"

    res, err := http.Get(url)
    if err != nil {
        log.Fatal(err)
    }
    body, err := ioutil.ReadAll(res.Body)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Error: %v. ", err)
        os.Exit(1)
    }
    defer res.Body.Close()
    buf := new(bytes.Buffer)
    w := zip.NewWriter(buf)
    fh := &zip.FileHeader{
        Name:     downloadedFileName,
        Modified: time.Now(),
        Method:   8,
    }
    f, err := w.CreateHeader(fh)
    if err != nil {
        log.Fatal(err)
    }
    if _, err := f.Write(body); err != nil {
        log.Fatal(err)
    }
    err = w.Close()
    if err != nil {
        log.Fatal(err)
    }
    file, err := os.Create(zipFileName)
    if err != nil {
        log.Fatal(err)
    }
    if _, err = io.Copy(file, buf); err != nil {
        log.Fatal(err)
    }
    file.Close()
    fmt.Println("Done.")
}

Note:

As an important point, when the file is downloaded, os.FileInfo cannot be used. So in this situation, it uses zip.FileHeader. At that time, please remember to set Method. Method is 0 as the default. This means no compression. The sample script uses 8 to Method. This means the DEFLATE method.

Gists

Benchmark: Reading and Writing Spreadsheet using Google Apps Script

Introduction

Please be careful! This result can be only used for Google Apps Script.

There are a limit executing time for Google Apps Script (GAS). That is 6 minutes for Consumer and Google Apps free edition, and 30 minutes for G Suite and Early Access. ¹ So many users always have to pay attention to reducing the process cost of scripts. So it is very important to know the process cost of various situations. I have already reported the costs for various processes as the reports. ² In this report, the process cost for reading and writing values for Spreadsheet using GAS has been investigated.

Overview

ArrangeStackingOrder is a GAS library for arranging the stacking order of page elements on Google Slides using Google Apps Script (GAS).

Demo

This is a demonstration of this library when this is used as a Google Slides Addon.

Description

Do you have situations that you want to arrange the stacking order of page elements on Google Slides using GAS? I had it before. At that time, I could achieve it by creating a simple script. Recently, I found that users who have the same situation. I thought that if there is a library for this, it will be useful for me and other developers. So I created this. If this was useful for your situation, I’m glad.

Recently, I was invited to Skillshare.com. They said that “will you teach about Google Apps Script?”. I thought that if I can be helpful for other users, I’m glad. So I joined to there and created a presentation. I would like to create more classed in the future. If those were helpful for you, I’m glad.

Gists

Overview

This is a report about the possibility of asynchronous process using event triggers. This is for Google Apps Script (GAS).

Description

onEdit() which is a simple trigger is often used as a trigger when the values are modified on Spreadsheet. When users want to use the script including some methods which are required to be authorized as the onEdit event, a installable trigger of onEdit is used. If the trigger is installed for the function of onEdit(), when the event trigger is run, onEdit() is run 2 times. In order to avoid this, the installable trigger is installed to the functions except for the functions of simple triggers. The functions of simple triggers which is the same events are not used in the project. When I thought about this situation, I thought that both onEdit() which is run by the simple trigger and the function which is run by the installable trigger might be able to be used, simultaneously. So I investigated about this situation.

RangeListApp was updated to v1.0.1.

• v1.0.1 (September 13, 2018)

  1. New method of “expandA1Notations” was added. This method can expand the a1Notations. For example, A1:C2 is expanded to "A1","B1","C1","A2","B2","C2".

You can check this at https://github.com/tanaikech/RangeListApp.

Gists

This is a sample script for expanding a1Notations using Google Apps Script (GAS). In this script, for example, “A1:E3” is expanded to “A1, B1, C1, D1, E1, A2, B2, C2, D2, E2, A3, B3, C3, D3, E3”. When each cell in “A1:E3” is checked, this script might be able to be used. If this was useful for your situation, I’m glad.

Script:

function expandA1Notation(a1Notations) {
  var columnToLetter = function(column) {
    var temp, letter = '';
    while (column > 0) {
      temp = (column - 1) % 26;
      letter = String.fromCharCode(temp + 65) + letter;
      column = (column - temp - 1) / 26;
    }
    return letter;
  };

  var letterToColumn = function(letter) {
    var column = 0, length = letter.length;
    for (var i = 0; i < length; i++) {
      column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
    }
    return column;
  };

  var reg = new RegExp("([A-Z]+)([0-9]+)");
  var res = a1Notations.map(function(e) {
    var a1 = e.split("!");
    var r = a1.length > 1 ? a1[1] : a1[0];
    var rr = r.split(":").map(function(f) {return f.toUpperCase().match(reg)});
    var obj = {
      startRowIndex: Number(rr[0][2]),
      endRowIndex: rr.length == 1 ? Number(rr[0][2]) + 1 : Number(rr[1][2]) + 1,
      startColumnIndex: letterToColumn(rr[0][1]),
      endColumnIndex: rr.length == 1 ? letterToColumn(rr[0][1]) + 1 : letterToColumn(rr[1][1]) + 1,
    };
    var temp = [];
    for (var i = obj.startRowIndex; i < obj.endRowIndex; i++) {
      for (var j = obj.startColumnIndex; j < obj.endColumnIndex; j++) {
        temp.push(columnToLetter(j) + i);
      }
    }
    return temp;
  });
  return res;
}

// When you use this script, please run main().
function main() {
  var a1Notations = ["A1:E3", "B10:W13", "EZ5:FA8", "AAA1:AAB3"];
  var res = expandA1Notation(a1Notations);
  Logger.log(res);
}

Result:

[
  ["A1","B1","C1","D1","E1","A2","B2","C2","D2","E2","A3","B3","C3","D3","E3"],
  ["B10","C10","D10","E10","F10","G10","H10","I10","J10","K10","L10","M10","N10","O10","P10","Q10","R10","S10","T10","U10","V10","W10","B11","C11","D11","E11","F11","G11","H11","I11","J11","K11","L11","M11","N11","O11","P11","Q11","R11","S11","T11","U11","V11","W11","B12","C12","D12","E12","F12","G12","H12","I12","J12","K12","L12","M12","N12","O12","P12","Q12","R12","S12","T12","U12","V12","W12","B13","C13","D13","E13","F13","G13","H13","I13","J13","K13","L13","M13","N13","O13","P13","Q13","R13","S13","T13","U13","V13","W13"],
  ["EZ5","FA5","EZ6","FA6","EZ7","FA7","EZ8","FA8"],
  ["AAA1","AAB1","AAA2","AAB2","AAA3","AAB3"]
]

This script uses 2 methods (https://stackoverflow.com/a/21231012/7108653) for converting from index to letter and from letter to index.