Overview

This is a GAS library for copying the rich text with the text styles from Google Document to Google Spreadsheet or from Google Spreadsheet to Google Document using Google Apps Script (GAS).

Description

Google Spreadsheet can use the rich text as the cell value. But I thought that it is difficult for me to directly edit the rich text in a cell. So I wanted to copy the rich text, that I edited at the Google Document, to the cell of Google Spreadsheet. But, unfortunately, when the rich text in Google Document is manually copied to a cell in Google Spreadsheet, the text style is removed. By this, only text values are copied. It seemed that this was the current specification. So in order to achieve above, I created this as a library.

Gists

This is a simple sample script for uploading a file using the file input tag of HTML. As the important point, the file is sent as the byte array for using Google Apps Script. By this, at Google Apps Script side, the byte array can be converted to a blob using a simple script.

HTML & Javascript

<input id="file" type="file" onchange="saveFile(this)" />
<script>
  function saveFile(f) {
    const file = f.files[0];
    const fr = new FileReader();
    fr.onload = function(e) {
      const obj = {
        filename: file.name,
        mimeType: file.type,
        bytes: [...new Int8Array(e.target.result)]
      };
      google.script.run.withSuccessHandler(e => console.log(e)).saveFile(obj);
    };
    fr.readAsArrayBuffer(file);
  }
</script>

Google Apps Script

function saveFile(e) {
  var blob = Utilities.newBlob(e.bytes, e.mimeType, e.filename);
  DriveApp.createFile(blob);
  return "Done.";
}

Overview

This is for the Google Apps Script Library Database.

Description

Since Google Apps Script was released on August 19th, 2009, it is used by a lot of users. Ref1, Ref2 By this, now there are a lot of useful libraries of Google Apps Script (GAS) in all over the world. But when I want to search a GAS library, I always use Google search engine. Unfortunately, in the current stage, the libraries cannot be directly searched by a database. On January 11th, 2020, a proposal for the database of Google Apps Script Library has been proposed by Andrew Roberts. Ref3 When I have discussing about this with him, I thought that I tried to think of a sample database. So I prepared this.

ManifestsApp was updated to v1.0.4.

• v1.0.4 (February 12, 2020)

  • “runtimeVersion” got to be able to be got and set. Now, ‘STABLE’, ‘V8’, ‘DEPRECATED_ES5’ can be used as the value of “runtimeVersion”. For example, you can enable V8 with the following script.

    var r = ManifestsApp.setProjectId(projectId).setRuntimeVersion("V8");
    Logger.log(r);
    

  • As one important point, when “STABLE” and “DEPRECATED_ES5” are used for the Google Apps Script project created before 2020 as the value of “runtimeVersion”, the error of Syntax error: Illegal character. occurs. So please be careful this. This has been reported at the issuetracker.

Gists

This is a sample script for rearranging the columns on Google Spreadsheet using Google Apps Script.

Sample script

In this sample script, the columns are rearranged with an array including the rearranged column indexes.

function rearrangeColumns(sheet, ar) {
  var obj = ar.reduce(function(ar, e, i) {
    return ar.concat({ from: e + 1, to: i + 1 });
  }, []);
  obj.sort(function(a, b) {
    return a.to < b.to ? -1 : 1;
  });
  obj.forEach(function(o) {
    if (o.from != o.to) sheet.moveColumns(sheet.getRange(1, o.from), o.to);
    obj.forEach(function(e, i) {
      if (e.from < o.from) obj[i].from += 1;
    });
  });
}

// Please run this function.
function main() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rearrangedColumnIndexes = [4, 3, 1, 0, 2];
  rearrangeColumns(sheet, rearrangedColumnIndexes);
}

• rearrangedColumnIndexes is the indexes of rearranged columns.

Gists

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.¹ So users always have to pay attention to reducing the process cost of the scripts. Especially, it is very important to know the process cost for the array processing, because the array processing is often used for spreadsheet and Google APIs. I have already reported “Benchmark: Loop for Array Processing using Google Apps Script”.² At February 7, 2020, the V8 runtime got to be able to be used in my account. By this, it is considered that it is possibly changed the result of benchmark without V8 ². So I measured about this. In this report, the process cost of “loop” for the array processing using GAS has been investigated with V8 runtime.

Gists

In my environment, at February 7, 2020, when I opened the script editor of Google Apps Script, the following notification could be seen.

By this, I could notice that finally, the V8 Runtime has already been added to Google Apps Script. I think that this will be also the great news for a lot of users including me.

Overview

DateFinder is a GAS library for searching the date objects from the cell range on the sheet in the Spreadsheet and retrieving the searched range as the RangeList object using Google Apps Script (GAS).

Description

There is the Class TextFinder for searching the text from cells of the Spreadsheet using the Google Apps Script. But in this case, the date object in the cell is used as the string. Namely, the values for searching are used as the same with the values retrieved by getDisplayValues(). So for example, when there are the date objects in the cells with the various formats, the date cannot be searched by the Class TextFinder. So I created this library. When this library is used, the date objects in the cells can be retrieved by the date object and/or the range between 2 dates.

• v1.2.5 (January 29, 2020)

  1. An option for selecting whether the top directory is created was added.
     • $ goodls -u [URL] --notcreatetopdirectory or $ goodls -u [URL] -ntd
     • When this option is NOT used (default situation), when a folder including sub-folders is downloaded, the top folder which is downloaded is created as the top directory under the working directory. When this option is used, the top directory is not created and all files and sub-folders under the top folder are downloaded under the working directory.
     • This feature request was implemented.

The detail information and how to get this are https://github.com/tanaikech/goodls.

Overview

EncodeApp is a GAS library for retrieving the encoding set (charset) and doing URL encode with the specific encoding set using Google Apps Script (GAS).

You can see the detail information here https://github.com/tanaikech/EncodeApp