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”.

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

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

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

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

3. The CSV file is downloaded by “executeDownload()”.

Please put both HTML and GAS to a GAS project.

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

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"

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.

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’.

File upload using HTML form in GAS project

Rule

1. Following scripts have to be made into a project of Google Apps Script.

2. Deploy the GAS project as a web application. Ref

3. 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.

File.txt :

a1
a2
a3
a4
a5
a6

Code :

awk '{array[NR]=$0} END {for (i in array) {if (i>1) {{print array[i-1]","array[i]}}}}' File.txt

Result :

a1,a2
a2,a3
a3,a4
a4,a5
a5,a6

After the all rows are imported to an array, it shows next row to current row under a condition of row > 1.

This is a sample script for retrieving cells without blank cells. Figure 1 shows the sample spreadsheet. In this sheet, the row 14 has one space.

Fig. 1: Sample spreadsheet.

Data is retrieved as follows.

  var data = SpreadsheetApp
             .getActiveSpreadsheet()
             .getActiveSheet()
             .getRange('a1:a30')
             .getValues();

1. Retrieving cells with spaces and no blank cells.

  var Result = [i for each (i in data)if (i)].join('');

Result : Hello World