Taking Advantage of Google Apps Script for Skillshare.com

submit.md

This is for my class in Skillshare.com. The class title is Taking Advantage of Google Apps Script.

Here, there are all scripts used in the class of Taking Advantage of Google Apps Script. You can use them by copying and pasting.

Basic Level

Page 12

var res = [];
for (var i = 1; i <= 5; i++) {
  res.push(i);
}
Logger.log(res) // [1.0, 2.0, 3.0, 4.0, 5.0]
console.log(res) // [1.0, 2.0, 3.0, 4.0, 5.0]

Intermediate Level

Intermediate Level 1

Page 4

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

var document = DocumentApp.getActiveDocument();

var slides = SlidesApp.getActivePresentation();

// ID means file ID
var spreadsheet = SpreadsheetApp.openById(id);

var document = DocumentApp.openById(id);


var slides = SlidesApp.openById(id);

Page 6

var value = spreadsheet.getSheetByName("Sheet1").getRange("A1").getValue();
var values = spreadsheet.getSheetByName("Sheet1").getRange("A1:C3").getValues();

var text = document.getBody().getText();

var string = slides.getSlides()[0].getShapes()[0].getText().asString();

Page 8

var value = "a1";
spreadsheet.getSheetByName("Sheet1").getRange("A1").setValue(value);

var values = [["a1", "b1", "c1"], ["a2", "b2", "c2"], ["a3", "b3", "c3"]];
spreadsheet.getSheetByName("Sheet1").getRange("A1:C3").setValues(values);

var text = "sample";
document.getBody().setText(text);
document.getBody().appendParagraph(text);

var text = "sample";
slides.getSlides()[0].getShapes()[0].getText().setText(text);

Page 11

function myFunction() {
  // Retrieve values from source sheet.
  var srcSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var values = srcSheet.getDataRange().getValues(); // All values of Sheet1 are retrieved.
  var copyValues = [];
  for (var i = 0; i < values.length; i++) {
    if (values[i][2]) {
      copyValues.push(values[i]); // Retrieve rows that checkbox is checked.
    }
  }

  // If there are rows for copying, put the values to destination sheet.
  if (copyValues.length > 0) {
    var dstSpreadsheetId = "### spreadsheet ID ###";
    var dstSheet = SpreadsheetApp.openById(dstSpreadsheetId).getSheetByName("Sheet1");
    dstSheet.getRange(dstSheet.getLastRow() + 1, 1, copyValues.length, 3).setValues(copyValues);
  }
}

var copyValues = values.filter(function(e) {return e[2]});

Page 12

function onEdit(e) {
  var range = e.range;
  if (range.getValue()) {
    var row = range.getRow();
    var copyValues = e.source.getRange("A" + row + ":C" + row).getValues();
    var dstSpreadsheetId = "### spreadsheet ID ###";
    var dstSheet = SpreadsheetApp.openById(dstSpreadsheetId).getSheetByName("Sheet1");
    dstSheet.getRange(dstSheet.getLastRow() + 1, 1, 1, 3).setValues(copyValues);
  }
}

Page 14

function myFunction() {
  // Copy template document.
  var docName = "sampleDoc"; // Filename of copied template document.
  var tempDocId = "### template document ID ###"; // Template document ID.
  var id = DriveApp.getFileById(tempDocId).makeCopy(docName).getId();


  // Replace text of copied document.
  var document = DocumentApp.openById(id);
  var body = document.getBody();
  body.replaceText("{replace1}", "replaced1");
  body.replaceText("{replace2}", "replaced2");
  body.replaceText("{replace3}", "replaced3");


  // Save document.
  document.saveAndClose(); // IMPORTANT! Reflect edited document.


  // Send PDF file as email.
  var blob = document.getBlob(); // When blob is retrieved, mimeType is converted to PDF.
  var obj = {to: "email address", subject: "sample subject", body: "sample body", attachments: [blob]};
  MailApp.sendEmail(obj);
}

Page 16

// Retrieve values and formats from Spreadsheet.
function getVeluesFromSpreadsheet(id) {
  var sheet = SpreadsheetApp.openById(id).getSheetByName("Sheet1");
  var range = sheet.getRange("A1:C3");
  return [
    range.getDisplayValues(),
    range.getHorizontalAlignments(),
    range.getBackgrounds(),
    range.getFontColors(),
    range.getFontWeights()
  ];
}

Page 17

// Retrieve alignment for Slides.
function getAlighment(horizontalAlignments) {
  var alignment;
  switch(horizontalAlignments) {
    case "general-left":
      alignment = SlidesApp.ParagraphAlignment.START;
      break;
    case "general-right":
      alignment = SlidesApp.ParagraphAlignment.END;
      break;
    case "center":
      alignment = SlidesApp.ParagraphAlignment.CENTER;
      break;
  }
  return alignment;
}

Page 18

function myFunction() {
  var values, horizontalAlignments, backgrounds, fontColors, fontWeights;
  var id = "### spreadsheet ID ###";
  [values, horizontalAlignments, backgrounds, fontColors, fontWeights] = getVeluesFromSpreadsheet(id);

  // Insert valuers from Spreadsheet as new table.
  var slidesPage = SlidesApp.getActivePresentation().getSlides()[0];
  var rows = values.length;
  var columns = values[0].length;
  var table = slidesPage.insertTable(rows, columns);
  for (var r = 0; r < rows; r++) {
    for (var c = 0; c < columns; c++) {
      var cell = table.getCell(r, c);
      cell.getText().setText(values[r][c]);
      cell.getFill().setSolidFill(backgrounds[r][c]);
      cell.getText().getTextStyle().setBold(fontWeights[r][c] == "bold" ? true : false);
      cell.getText().getTextStyle().setForegroundColor(fontColors[r][c]);
      var alignment = getAlighment(horizontalAlignments[r][c]);
      cell.getText().getParagraphStyle().setParagraphAlignment(alignment);
    }
  }
}

Intermediate Level 2

Page 4

var fileName = "sample.txt";
var files = DriveApp.getFilesByName(fileName);
var file = files.next();
var content = file.getBlob().getDataAsString();
Logger.log(content)

DriveApp.createFile("sample.txt", "sample text", MimeType.PLAIN_TEXT);

var fileName = "sample.txt";
var files = DriveApp.getFilesByName(fileName);
var file = files.next();
file.setContent("new sample text");
var content = file.getBlob().getDataAsString();
Logger.log(content)

Page 6

var fileName = "sample.txt";
var destinationFolderName = "WorkSpace";

var files = DriveApp.getFilesByName(fileName);
var file = files.next();
var fileParent = file.getParents().next();

var dstFolder = DriveApp.getFoldersByName(destinationFolderName).next();

dstFolder.addFile(file);
fileParent.removeFile(file);

Page 8

  var searchQuery = "title contains 'sample'";
  var files = DriveApp.searchFiles(searchQuery);
  while (files.hasNext()) {
    var file = files.next();
    Logger.log(file.getName())
  }

var searchQuery = "mimeType = 'text/plain'";

var searchQuery = "'### folder ID ###' in parents";

var searchQuery = "trashed = true";

Intermediate Level 3

Page 5

function doGet(e) {
  var result = JSON.stringify({queryParameter: e.parameter});
  return ContentService
  .createTextOutput(result)
  .setMimeType(ContentService.MimeType.JSON);
}

curl -L "https://script.google.com/macros/s/#####/exec?key1=value1&key2=value2"

Advanced Level

Page 4

function doPost(e) {
  var data = Utilities.base64Decode(e.parameters.data);
  var blob = Utilities.newBlob(data, e.parameters.mimetype, e.parameters.filename);
  DriveApp.createFile(blob);
  return ContentService.createTextOutput();
}

Page 5

<html>
<head><script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.js"></script></head>
<body>
    <form action="https://script.google.com/macros/s/#####/exec" id="form" method="post">
        Upload a file<div id="data"></div><input name="file" id="uploadfile" type="file">
        <input id="submit" type="submit">
    </form>
    <script>
    $('#uploadfile').on("change", function() {
        var file = this.files[0];
        var fr = new FileReader();
        fr.fileName = file.name;
        fr.onload = function(e) {
            var data = e.target.result.split(",");
            html = '<input type="hidden" name="data" value="' + data[1] + '" >';
            html += '<input type="hidden" name="mimetype" value="' + data[0].split(":")[1].split(";")[0] + '" >';
            html += '<input type="hidden" name="filename" value="' + e.target.fileName + '" >';
            $("#data").empty().append(html);
        }
        fr.readAsDataURL(file);
    });
    </script>
</body>
</html>

Page 8

function doPost(e) {
  var fileId = e.parameters.id;
  var file = DriveApp.getFileById(fileId);
  var obj = {
    file: file.getBlob().getBytes(),
    name: file.getName(),
    mimeType: file.getBlob().getContentType(),
  };
  return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON);
}

Page 9

import numpy as np
import requests
r = requests.post(
    "https://script.google.com/macros/s/#####/exec",
    data={"id": "### file ID ###"}
)
f = open(r.json()["name"], "bw")
f.write(np.array(r.json()["file"], dtype=np.uint8))
f.close()
print("Filename = {0}, MimeType = {1}".format(r.json()["name"], r.json()["mimeType"]))

Page 12

function doGet(e) {
  var id = e.parameter.id;
  var sheetName = e.parameter.sheetname;
  var result = {};
  try {
    var ss = SpreadsheetApp.openById(id).getSheetByName(sheetName);
    result.a1Notation = ss.getDataRange().getA1Notation();
    result.LastRow = ss.getLastRow();
    result.LastColumn = ss.getLastColumn();
  } catch(er) {
    result.Error = er;
  }
  return ContentService
  .createTextOutput(JSON.stringify(result))
  .setMimeType(ContentService.MimeType.JSON);
}

Page 13

curl -L "https://script.google.com/macros/s/#####/exec?id=### spreadsheetId ###&sheetname=Sheet1"