February 20, 2017

Export CSV File from Spreadsheet and Make Download Button

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 :

February 20, 2017

Send E-mail with xlsx File Converted from Spreadsheet

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]
  });
}

February 17, 2017

Retrieving Access Token for Google Drive API using GAS

These GASs retrieve an access token for using Google Drive API. There are 3 parts. Before you use this, please retrieve client ID, client secret and redirect uri from Google , and choose scopes.

1. Retrieving code from web

This is a script to output URL for retrieving “code” from web. Please retrieve “code” by import this URL to your browser. After you run this script, using “url” got from this script, it retrieves “code”.

February 16, 2017

Send E-mail with Excel file converted from Spreadsheet

This sample script sends an e-mail with an Excel file exported from Spreadsheet as an attachment file.

function excelSender() {
  var sheetID = [Sheet ID];
  var xlsxName = [Excel file name];
  var params = {
    "headers" : {Authorization: "Bearer [Retrieved 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: [Mail address],
    subject: "sample subject",
    body: "sample body",
    attachments: [xlsxlFile]
  });
}

Send E-mail with Excel file converted from Spreadsheet

February 16, 2017

Download a CSV File from Spreadsheet Using Google HTML Service

Here, I introduce how to download a CSV file from spreadsheet using Google HTML Service.

Using “onOpen()”, it addes menu for launching a dialog.

Download a CSV File from Spreadsheet Using Google HTML Service

After launching the dialog, “getFileUrl()” is launched by pushing a button. “getFileUrl()” exports a CSV file and outputs download URL.
The CSV file is downloaded by “executeDownload()”.

Please put both HTML and GAS to a GAS project.

February 13, 2017

Making charts at spreadsheet

 var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var chart = sheet.newChart()
    .setChartType(Charts.ChartType.LINE)
    .asLineChart()
    .addRange(sheet.getRange('a1:a21'))
    .addRange(sheet.getRange('b1:b21'))
    .addRange(sheet.getRange('c1:c21'))
    .setColors(["green", "red"])
    .setBackgroundColor("black")
    .setPosition(5, 5, 0, 0)
    .setPointStyle(Charts.PointStyle.MEDIUM)
    .setOption('useFirstColumnAsDomain', true)
    .setOption('height', 280)
    .setOption('width', 480)
    .setOption('title', 'Sample chart')
    .setOption('hAxis', {
      title: 'x axis',
      minValue: 0,
      maxValue: 20,
      titleTextStyle: {
        color: '#c0c0c0',
        fontSize: 20,
        italic: false,
        bold: false
      },
      textStyle: {
        color: '#c0c0c0',
        fontSize: 12,
        bold: false,
        italic: false
      },
      baselineColor: '#c0c0c0',
      gridlines: {
        color: '#c0c0c0',
        count: 4
      }
    })
    .setOption('vAxis', {title: 'y axis',
      minValue: 0,
      maxValue: 800,
      titleTextStyle: {
        color: '#c0c0c0',
        fontSize: 20,
        italic: false,
        bold: false
      },
      textStyle: {
        color: '#c0c0c0',
        fontSize: 12,
        bold: false,
        italic: false
      },
      baselineColor: '#c0c0c0',
        gridlines: {
        color: '#c0c0c0',
        count: 4
      }
    })
    .setOption('legend', {
      position: 'right',
      textStyle: {
        color: 'yellow',
        fontSize: 16
      }
    })
    .build();
    sheet.insertChart(chart);

Making charts at spreadsheet

February 8, 2017

File Upload and Download with File Convert For curl using Drive API

It is necessary to retrieve access token on Google. Scope is as follows.

https://www.googleapis.com/auth/drive

Other mimetypes can be seen here.

Download and convert from Spreadsheet to Excel

curl -X GET -sSL \
        -H "Authorization: Bearer [Your access token]" \
        -o "Excel file name" \
        "https://www.googleapis.com/drive/v3/files/[File ID]/export?mimeType=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

Upload and convert from Excel to Spreadsheet

curl -X POST -sSL \
        -H "Authorization: Bearer [Your access token]" \
        -F "metadata={ \
                     name : '[File name on Google Drive]', \
                     mimeType : 'application/vnd.google-apps.spreadsheet' \
                     };type=application/json;charset=UTF-8" \
        -F "file=@[Your Excel file];type=application/vnd.ms-excel" \
        "https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart"

February 8, 2017

Sample Array Script for Spreadsheet

This is a Sample Array Script for Spreadsheet. It makes an 2D array filled by strings and number. The strings and number are column strings and row number, respectively.

However, because this is a sample, the maximum column number is 26.

function sa(row, col){
  if (col > 26) return;

  var ar = new Array(row);
  for(var i = 0; i < row; i++) ar[i] = new Array(col);
  for (var i = 0; i < row; i++){
    for (var j = 0; j < col; j++){
      ar[i][j] = String.fromCharCode(i + 97) + String(j + 1);
    }
  }
  return ar.map(function(x, i){return x.map(function(y, j){return ar[j][i]})});
}

When “sa(10,10)” is given, following array can be output.

February 7, 2017

Event of onEdit() for Google spreadsheet

About Event Objects

For example, it thinks the situation of input text of ’test’ to ‘A1’ on a sheet.

When you use only ‘onEdit(e)’ without an installing trigger, ’e’ has following parameters.

{authMode=LIMITED, range=Range, source=Spreadsheet, user=, value=test}

In this case, the event cannot send an e-mail because of ‘authMode=LIMITED’.

When you use “onEdit(e)” with an installing trigger of “Edit”, ’e’ has following parameters.

{authMode=FULL, range=Range, source=Spreadsheet, value=test, triggerUid=#####}

In this case, the event can send an e-mail because of ‘authMode=FULL’.

February 5, 2017

File upload using doPost on Google Web Apps

File upload using HTML form in GAS project

Rule

Following scripts have to be made into a project of Google Apps Script.
Deploy the GAS project as a web application. Ref
After updated the script, it has to be updated as a new version.

Form.html :

<html>
  <body>
    <form>
      <input type="file" name="imageFile">
      <input type="button" value="ok" onclick="google.script.run.upload(this.parentNode)">
    </form>
  </body>
</html>

GAS :

function doGet() {
  return HtmlService.createHtmlOutputFromFile('Form.html');
}

function upload(e) {
  var destination_id = '#####'; // Folder ID of destination folder

  // Reference : https://developers.google.com/apps-script/reference/base/blob#getAs(String)
  // You can use 'application/pdf', 'image/bmp', 'image/gif', 'image/jpeg' and 'image/png'.
  var contentType = 'image/jpeg';
  var img = e.imageFile;

  var destination = DriveApp.getFolderById(destination_id);
  var img = img.getAs(contentType);
  destination.createFile(img);
}

When you set ‘image/jpeg’ as “contentType” and upload png file, the uploaded image file is converted to jpeg file and saved it to the destination folder.

Google OAuth Verification & Application Privacy Policy

Registered Application Name: Workspace & Gemini AI Orchestration Engine

Application Purpose & Core Functionality:

This web page serves as the official homepage and privacy compliance interface for the application "Workspace & Gemini AI Orchestration Engine". This specialized developer utility is designed to research, benchmark, and optimize advanced integrations between Google Workspace services, the Google Apps Script API, and Gemini AI models (via Google Vertex AI / Gemini API endpoints).

The application facilitates automated multi-agent scaffolding, programmatic script deployment, project resource management, and structural analysis of Google Apps Script projects. It allows developers and autonomous AI agents (operating via Model Context Protocol / MCP) to securely evaluate execution performance, implement high-performance batch requests, and test agent-to-agent (A2A) workflows within a controlled and structured environment.

Google User Data Policy Compliance Statements:

1. Data Access & Specific Usage

Our application explicitly requests access to specific Google user accounts through OAuth scopes required strictly for interacting with the Google Apps Script API and Google Workspace endpoints. This access is utilized solely to execute user-initiated or agent-orchestrated programmatic operations—such as creating, modifying, deploying, or benchmarking script projects and executing automated workflows. No background automated extraction occurs without explicit session initiation.

2. Data Storage & Zero-Retention Policy

Adhering to a strict Zero-Retention Model, this application does not store, log, or persist any personal data, OAuth tokens, script source codes, or Google account configurations on any external server, database, or persistent storage medium. All data processing and API responses are handled entirely in-memory or securely on the client side within the active session context, ensuring complete cryptographic transient isolation.

3. Data Sharing & Third-Party Non-Disclosure

We maintain absolute data privacy. No data accessed via Google OAuth scopes is shared, sold, rented, or transferred to third-party entities, advertising networks, or data brokers. All data transmissions are strictly point-to-point, encrypted in transit using industry-standard protocols, and limited entirely to the direct channel between the execution environment and Google's official API gateways.

For inquiries regarding this developer application, technical benchmarks, or verification compliance, please refer to the official documentation and repositories linked on this homepage (tanaikech.github.io).