This sample script is for retrieving parameters from a chart. The chart created by both Google Apps Script and manually operation can be used.

Creates Chart

When a chart is created, it supposes following parameters.

var parameters = {
  "title": "x axis",
  "fontName": "Arial",
  "minValue": 0,
  "maxValue": 100,
  "titleTextStyle": {
    "color": "#c0c0c0",
    "fontSize": 10,
    "fontName": "Roboto",
    "italic": true,
    "bold": false
  }
};

.setOption('hAxis', parameters)

Retrieve Parameters From Chart

For the chart created by above parameters, in order to retrieve the parameters, it uses following script.

This sample is for error handling for subprocess.Popen. It confirms whether the execution file is existing. If the execution file is also not in the path, the error message is shown.

import subprocess

res = subprocess.Popen(
    "application",  #  <- Execution file
    stdout=subprocess.PIPE,
    stderr=subprocess.PIPE,
    shell=True
).communicate()

if len(res[1]) == 0:
    print("ok: Application is existing.")
else:
    print("Error: Application is not found.")

This sample is for using constructor between classes at Python.

Sample :

class test1:

    def __init__(self):
        self.msg = "sample text"


class test2:

    def __init__(self):
        self.msg = test1().msg


print(test2().msg)

>>> sample text

When a string without no strings is split by strings.Split(), the created slice is the same to the slice created by make(). The length of the slice doesn’t become zero.

Sample script :

package main

import (
    "fmt"
    "strings"
)

func main() {
    sample1a := strings.Split("", " ")
    fmt.Printf("%v, %v, '%v', %v, %+q\n", sample1a, len(sample1a), sample1a[0], len(sample1a[0]), sample1a[0])

    sample1b := make([]string, 1)
    fmt.Printf("%v, %v, '%v', %v, %+q\n", sample1b, len(sample1b), sample1b[0], len(sample1b[0]), sample1b[0])

    var sample2a []string
    fmt.Printf("%v, %v\n", sample2a, len(sample2a))

    sample2b := []string{}
    fmt.Printf("%v, %v\n", sample2b, len(sample2b))
}

Result :

strings.Split() : [], 1, '', 0, ""
make()          : [], 1, '', 0, ""
var                : [], 0
[]string{}      : [], 0

Description

There are a limit executing time for Google Apps Script (GAS). It’s 6 minutes. So users have to pay attention to the process cost of the script. GAS can use JavaScript 1.7. This means to be able to be used comprehension for GAS.

In this report, the process cost for the comprehension has been investigated. The normal for loop was used as the competitor. As a result, it was found that the comprehension can be used one of methods for reducing the process cost. For 1 dimensional array, the process cost of comprehension is about 60% lower than that of the normal one. For 2 dimensional array, the process cost of comprehension is about 50% lower than that of the normal one. Each data is the average for 10 times measurements.

This sample script is for embedding animation GIF in a cell using custom function on Spreadsheet.

I think that this method is one of various ideas.

Problem

There are some limitations.

1. Images of jpeg and png can be embedded in a cell using =IMAGE(). But when animation GIF is embedded using it, GIF is not played.
2. insertImage() can insert the animation GIF to sheet. But it is not imported to one cell. It floats on several cells.
3. In order to float the animation GIF on one cell, the size of GIF has to be retrieved. But the size of image cannot be retrieved at spreadsheet APIs.
4. =IMAGE() and insertImage() cannot be used by custom functions.

Solution

I thought a method to floating an animation GIF on one cell using insertImage(). By this, I thought that it will be easy to use as a sheet with GIF images.

This sample script performs OCR and imports resultant text to a cell using custom function on Spreadsheet.

Drive API has a function to do OCR. It was used for this sample.

I think that this method is one of various ideas.

Problem

When OCR is performed and imported the result to a cell on spreadsheet, there are some limitations. DriveApp, UrlFetchApp, setFormula() cannot be used for custom functions.

Solution

In order to avoid these limitations, I used Web Apps. From previous research, it has been found that Web Apps can avoid various limitations. Also in the case of this situation, Web Apps could avoid the above limitations.

This sample is for changing file name and reopening the file with new name. The flow is as follows.

1. A file (sample.py) is opened.
2. Rename the file from sample.py to newsample.py.
3. The opened file is replace to the file with new name.

os.rename(oldfilewithpath, newname)
view = self.view.window().find_open_file(oldfilewithpath)
if view:
    view.retarget(newname)

This sample retrieves all files in a folder with spreadsheet. When there are some folders in the folder with spreadsheet, this script can retrieve all files in all folders. This script has to be a container-bound script for spreadsheet.

Script :

function getFileList(){
  var folderlist = (function(folder, folderSt, results){
    var ar = [];
    var folders = folder.getFolders();
    while(folders.hasNext()) ar.push(folders.next());
    folderSt += 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.getFolderById(
    DriveApp.getFileById(
      SpreadsheetApp.getActiveSpreadsheet().getId()
    ).getParents().next().getId()
  ),"",[]);

  var filelist = [];
  for (var i in folderlist){
    var folderid = folderlist[i][folderlist[i].length - 1];
    var temp = [];
    var folder = DriveApp.getFolderById(folderid);
    var files = folder.getFiles();
    var foldername = folder.getName();
    while(files.hasNext()){
      var file = files.next();
      temp.push([foldername, file.getName()]);
    }
    filelist.push(temp);
  }
  return Array.prototype.concat.apply([], filelist);
}

function main() {
  var data = getFileList();
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange(1,1,data.length,data[0].length).setValues(data);
}

Result :

This sample script embeds a map to a cell using custom function on Spreadsheet.

I think that this method is one of various ideas.

Problem

When the map is embeded to a cell on spreadsheet as an image, the function =IMAGE() is suitable for this situation. However, setFormula() for importing =IMAGE() and DriveApp.createFile() for creating images from maps also cannot be used for custom functions.

Solution

In order to avoid these limitations, I used Web Apps. From previous research, it has been found that Web Apps can avoid various limitations. Also in the case of this situation, Web Apps could avoid the above limitations.