Gist

This is a sample script for retrieving images on Spreadsheet.

Unfortunately, there are no methods for retrieving directly images on spreadsheet using GAS. So I use the method which retrieves URL from =image(URL) and retrieves the image from the URL.

In this case, =image(URL) has to be in the cell. Images embedded by insertImage() cannot be retrieved.

At first, please confirm them.

Sample script :

var cell = "A1"; // Cell address with the function of "=image()"
var filename = "samplename"; // Output filename

var image = SpreadsheetApp.getActiveSheet().getRange(cell).getFormula();
var blob = UrlFetchApp.fetch(image.match(/\"(.+)\"/)[1]).getBlob();
DriveApp.createFile(blob.setName(filename));

Flow :

1. Retrieve =image(URL) using getFormula().
2. Retrieve URL from =image(URL).
3. Retrieve file blob using UrlFetchApp.fetch() from the URL.
4. Output the file blob as a file.

This sample is for retrieving array coordinates of duplicated elements.

Script :

var inputdata = ["a", "b", "b", "c", "d", "c", "e", "a", "f", "g"];

var dic = {};
var result = [];
inputdata.forEach(function(e, i){
  if (dic[e]) {
    result[i] = 'Duplicated';
  } else {
    result[i] = null;
  }
  dic[e] = "temp";
});

Logger.log(JSON.stringify(result))
Logger.log([i for (i in result) if(result[i]=='Duplicated')])

Result :

[null,null,"Duplicated",null,null,"Duplicated",null,"Duplicated",null,null]
[2, 5, 7]

Gists

This is a sample of Google Apps Script. This script is for retrieving all files and folders under a folder on Google Drive. All files and folders in the specific folder can be retrieved.

If you want to retrieve file list with all files and folders on Google Drive, please use DriveApp.getRootFolder().getId() as folderId.

When there are a lot of files in the folder, it may be over the limitation time to execute script.

ggsrun was updated to v.1.2.2

1. For Google Docs (spreadsheet, document, slide and drawing), since I noticed that the revision files would not be able to be retrieved using Drive API v3, I modified this using new workaround.
   • The new workaround is to use Drive API v2. drive.revisions.get of Drive API v2 can retrieve not only the revision list, but also the export links. I thought of the use of the export links. This became the new workaround.
   • For the files except for Google Docs, the revision files can be retrieved using Drive API v3.
   • The usage is here.

I don’t know when this workaround will not be able to be used. But if this could not be used, I would like to investigate of other method.

This method was updated at July 12, 2017.

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)'

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

2. Revision ID

Retrieve revision id from file id.

curl -X GET -sSL \
    -H 'Authorization: Bearer ### Access token ###' \
    'https://www.googleapis.com/drive/v3/files/### FileID ###/revisions?fields=revisions(id%2CmodifiedTime)'

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

Gist

Overview

This is a sample script for creating the pseudo browser using Google Spreadsheet.

Description

I unexpectedly noticed this. I think that this is for off-line browsing using HTML data. So there are many limitations. At first, please confirm them.

• Limitations
  • It cannot move from opened site to other outside site. If the outer site is opened as a new wind, your own browser is opened and move there.
  • For URL, it can move directories which is one low. But it cannot move directories more than two deeper.
    • Because it retrieves the HTML data from the inputted URL using UrlFetchApp. It cannot move to the out side of data retrieved by UrlFetchApp.
  • The access to the site opened by inputting URL is run at Google side.
  • Javascript on the opened site is run at your local PC side.
  • User-Agent cannot be changed from Mozilla/5.0 (compatible; Google-Apps-Script).

What I thought the interesting is that the appearance of the site has been maintained at this pseudo browser. And, by using this, the sites which are slow speed at your environment might be able to be opened smoothly.

Gist

This is a sample script for downloading files from Google Drive under no authorization using browser. By using this sample, you can make other users download files from your Google Drive. Even if the other users are not Google users, they can download the files.

Demo

This is a demonstration for downloading files from Google Drive under no authorization using browser. From the top document, You can see that an user who is not owner of Google Drive is downloading files.

gislack is a CLI tool to submit files to both Gist and Slack.

• v1.0.1 (June 30, 2017)
  • Some modifications related to error handling.

Please check it out. https://github.com/tanaikech/gislack

ImgApp was updated to v1.1.0. New method was added.

2. doResize()

Overview

This method is for resizing images.

Description

Unfortunately, there are no methods to resize images at Google Apps Script. As a workaround, there is a method that it imports the image in Google Document and resizes the image using setWidth() and setHeight(). But in this method, the resized blob cannot be retrieved. So although I had thought of other workaround, I had not been able to find it. Recently, I accidentally discovered the other workaround doResize(). Since it was found that this workaround can be surely used, I added this to ImgApp.

1. getSize()

Overview

This method is for retrieving the width and height of image as the unit of pixel.

Description

Unfortunately, there are no methods to directly retrieve the image size at Google Apps Script. As a workaround, there is a method that it imports the image in Google Document and retrieves the size using getWidth() and getHeight(). But in this method, it uses much time and resources on Google. So I thought of retrieving the information of image at the binary level, and created this. By this, the low process cost could be achieved.