Google App Scripts 自定义函数(custom function)笔记

1. 自定义函数不可以使用setValue(), 但是通过onOpen添加到menu上的fucntion可以使用setValue()

2. onEdit 可以使用setValue

function onEdit(event)
{
  var sheets = SpreadsheetApp.getActiveSpreadsheet();
  var tab    = event.source.getActiveSheet();
  var cell   = event.source.getActiveRange();
  var target = cell.getA1Notation().toString();
  var value  = cell.getValue();
  
  // only for X Column
  if(cell.getColumn() == 24)
  {
    if(target.search(':')*1 < 0)
    {
      var format = cell.getFormula();
      target = target.replace( /^\D+/g, '');
      tab.getRange('Z'+target).setValue(value?' 20160712':'');
    }
    else
    {
      var targetArray = target.split(':');
      var targetStart = targetArray[0].replace( /^\D+/g, '').trim()*1;
      var tragetEnd   = targetArray[1].replace( /^\D+/g, '').trim()*1;
      
      for (var i = targetStart; i <= tragetEnd; i++) 
      {
        var value = tab.getRange('X'+i).getValue();
        tab.getRange('Z'+i).setValue(value?'GOOD JOB==>'+tab.getName()+'===>'+i+'===>'+value:'');
      }
    }
  }
}

3. 自定义函数下获取当前表格(cell)的信息(行数,列数,sheet名称等)

获取列数:

function test() {
  return SpreadsheetApp.getActiveSheet().getActiveCell().getColumn(); 
}

获取行数

function test() {
  return SpreadsheetApp.getActiveSheet().getActiveCell().getRow(); 
}

获取位置

function test() {
  return SpreadsheetApp.getActiveSheet().getActiveCell().getA1Notation(); 
}

获取sheet名称

function test() {
  return SpreadsheetApp.getActiveSheet().getName(); 
}

其他:

function getRangeInfo()
{
  //var range_position = $sheet_active.getActiveRange().getColumn();//.getA1Notation(); //.getValues();
  //$sheet_active.getRange(range_position).copyTo($sheet_active.getRange(range_position), {contentsOnly:true});
  
  var getValues      = $sheet_active.getActiveRange().getValues();
  var getA1Notation  = $sheet_active.getActiveRange().getA1Notation();
  var getColumn      = $sheet_active.getActiveRange().getColumn();
  var getLastColumn  = $sheet_active.getActiveRange().getLastColumn();
  var getHeight      = $sheet_active.getActiveRange().getHeight();
  var getWidth       = $sheet_active.getActiveRange().getWidth();
  var getLastRow     = $sheet_active.getActiveRange().getLastRow();
  var getRow         = $sheet_active.getActiveRange().getRow();
  var getRowIndex    = $sheet_active.getActiveRange().getRowIndex();
  var getNumColumns  = $sheet_active.getActiveRange().getNumColumns();
  var getNumRows     = $sheet_active.getActiveRange().getNumRows();
  var getGridId      = $sheet_active.getActiveRange().getGridId();
  var getDataNumRows = $sheet_active.getDataRange().getNumRows();
    
  Browser.msgBox("getValues:"+getValues+"getDataNumRows:"+getDataNumRows+"\n"+"getA1Notation:"+getA1Notation+"\n"+"getColumn:"+getColumn+"\n"+"getLastColumn:"+getLastColumn+"\n"+"getHeight:"+getHeight+"\n"+"getWidth:"+getWidth+"\n"+"getLastRow:"+getLastRow+"\n"+"getRow:"+getRow+"\n"+"getRowIndex:"+getRowIndex+"\n"+"getNumColumns:"+getNumColumns+"\n"+"getNumRows:"+getNumRows+"\n"+"getNumRows:"+getNumRows);
}

如何理解上面的各个函数,看下图一目了然:

传另一个cell到自定义函数

A hack for functions that require an actual range reference, like yours, is to pass the range as a string.

=get_color( "B7" )

Where the function would be something like this:

/**
 * Get the color of text in the given cell.
 *
 * @param  {"B32"}  cell  Cell reference, enclosed in quotes.
 * @returns               The text color from the given cell.
 * @customfunction
 */
function get_color( cell ) {
  if (typeof cell !== "string")
    throw new Error( "Cell reference must be enclosed in quotes." );

  var range = SpreadsheetApp.getActiveSheet().getRange( cell );
  return range.getFontColor();
}

Note: The comment block seeds the Google Sheets auto-completion help for this function. See Did you know? (Custom Functions in Google Apps Script) for more about custom functions.

获取其他sheet下的表格值

function recordValue() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var t = ss.getSheetByName("CH");
    var marketValue = ss.getSheetByName("OVERVIEW").getRange('E4').getValue();
    var r = t.getRange(t.getLastRow() + 1, 2, 1, 2).setValues([
        [Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'MM-dd-yyyy'), marketValue]
    ]);
}

核心用法是:

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("History").getRange("B1").getValue();

引申出来可以copy复制:

function test(input){
  return SpreadsheetApp.getActiveSheet().getRange('C14').getValue();
}

4. sleep(milliseconds)

Utilities.sleep(1000) //The maximum allowed value is 300000 (or 5 minutes). 最大五分钟

5. 打包压缩/解压缩zip(blobs) / unzip(blob)

在计算机中,BLOB是指二进制长对象。BLOB是一个大文件,典型的BLOB是一张图片或一个声音文件,由于它们的尺寸,必须使用特殊的方式来处理(例如:上传、下载或者存放到一个数据库)。根据Eric Raymond的说法,处理BLOB的主要思想就是让文件处理器(如数据库管理器)不去理会文件是什么,而是关心如何去处理它。但也有专家强调,这种处理大数据对象的方法是把双刃剑,它有可能引发一些问题。在数据库中存放体积较大的多媒体对象就是应用程序处理BLOB的典型例子。

Takes a Blob representing a zip file and returns its component files.

 var googleFavIconUrl = "https://www.google.com/favicon.ico";
 var googleLogoUrl = "https://www.google.com/images/srpr/logo3w.png";

 // Fetch the Google favicon.ico file and get the Blob data
 var faviconBlob = UrlFetchApp.fetch(googleFavIconUrl).getBlob();
 var logoBlob = UrlFetchApp.fetch(googleLogoUrl).getBlob();

 // zip now references a blob containing an archive of both faviconBlob and logoBlob
 var zip = Utilities.zip([faviconBlob, logoBlob], "google_images.zip");

 // This will now unzip the blobs
 var files = Utilities.unzip(zip);

6. 接发送邮件

Methods

Method Return type Brief description
getRemainingDailyQuota() Integer Returns the number of remaining emails a user can send for the rest of the day.
sendEmail(message) void Sends an email message.
sendEmail(recipient, subject, body) void Sends an email message.
sendEmail(recipient, subject, body,options) void Sends an email message with optional arguments.
sendEmail(to, replyTo, subject,body)

getRemainingDailyQuota()

 var emailQuotaRemaining = MailApp.getRemainingDailyQuota();
 Logger.log("Remaining email quota: " + emailQuotaRemaining);

sendEmail(message)

 // This code fetches the Google and YouTube logos, inlines them in an email
 // and sends the email
 function inlineImage() {
   var googleLogoUrl = "http://www.google.com/intl/en_com/images/srpr/logo3w.png";
   var youtubeLogoUrl =
         "https://developers.google.com/youtube/images/YouTube_logo_standard_white.png";
   var googleLogoBlob = UrlFetchApp
                          .fetch(googleLogoUrl)
                          .getBlob()
                          .setName("googleLogoBlob");
   var youtubeLogoBlob = UrlFetchApp
                           .fetch(youtubeLogoUrl)
                           .getBlob()
                           .setName("youtubeLogoBlob");
   MailApp.sendEmail({
     to: "recipient@example.com",
     subject: "Logos",
     htmlBody: "inline Google Logo<img src='cid:googleLogo'> images! <br>" +
               "inline YouTube Logo <img src='cid:youtubeLogo'>",
     inlineImages:
       {
         googleLogo: googleLogoBlob,
         youtubeLogo: youtubeLogoBlob
       }
   });
 }

sendEmail(recipient, subject, body, options)

 // Send an email with two attachments: a file from Google Drive (as a PDF) and an HTML file.
 var file = DriveApp.getFileById('1234567890abcdefghijklmnopqrstuvwxyz');
 var blob = Utilities.newBlob('Insert any HTML content here', 'text/html', 'my_document.html');
 MailApp.sendEmail('mike@example.com', 'Attachment example', 'Two files are attached.', {
     name: 'Automatic Emailer Script',
     attachments: [file.getAs(MimeType.PDF), blob]
 });

sendEmail(to, replyTo, subject, body)

 MailApp.sendEmail("recipient@example.com",
                   "replies@example.com",
                   "TPS report status",
                   "What is the status of those TPS reports?");

实例:google app scripts 群发邮件

Tutorial: Simple Mail Merge

Hugo Fierro, Google Apps Script team
Ikai Lan, Google Apps Script team
Originally published March 2009, updated December 2012

Goal (结合Google 表单群发邮件)

This tutorial shows an easy way to collect information from different users in a spreadsheet using Google Forms, then leverage it to generate and distribute personalized emails.

Time to Complete

Approximately 15 minutes

Prerequisites (准备工作)

Before you begin this tutorial, you should already be familiar with:

Index

This tutorial is divided into the following sections:

Section 1: Preparing a spreadsheet and running the example

    1. Open our Spreadsheet template for this tutorial.
    2. Create a personal copy of the spreadsheet by clicking on ‘Make a Copy’. You can now edit the spreadsheet and start writing scripts.
Google App Scripts 自定义函数(custom function)笔记
Google App Scripts 自定义函数(custom function)笔记
  1. Submit data to the spreadsheet by clicking on the ‘Form’ menu, then ‘Go to live form’. Make sure you enter your own email address.

    Google App Scripts 自定义函数(custom function)笔记
    Google App Scripts 自定义函数(custom function)笔记
  2. If you go back to the spreadsheet, you should see a new row with the data you entered in the form

    Google App Scripts 自定义函数(custom function)笔记
    Google App Scripts 自定义函数(custom function)笔记
  3. Select the menu item Tools > Script editor.
  4. In the script editor, select the menu item Run > sendEmails. After you authorize the script, as many emails as rows in the spreadsheet will be sent.
  5. Finally, check your email. It may take a few seconds for the message to be delivered, so you may want to refresh.

    Google App Scripts 自定义函数(custom function)笔记
    Google App Scripts 自定义函数(custom function)笔记

Continue reading Section 2 to learn more about how this example’s script works.

Section 2: Understanding the code

In this section, we will explain relevant parts of the script. The full code is available in Section 5.

  1. Function sendEmails
      var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows() - 1, 4);

    The code above gets a range that contains all the data in the spreadsheet. Note that unbounded ranges are not supported so we compute the range using the actual total number of rows in the Sheet.

      var templateSheet = ss.getSheets()[1];
      var emailTemplate = templateSheet.getRange("A1").getValue();

    The code above retrieves the string template that will be used to generate personalized emails. It is defined in the “A1” cell in the second sheet called ‘Email Template’.

    Google App Scripts 自定义函数(custom function)笔记
    Google App Scripts 自定义函数(custom function)笔记

    Note the four template markers, like ${"First Name"} . They correspond to column names in the data Sheet and specify where the data from a given row should be displayed.

      // Create one JavaScript object per row of data.
      var objects = getRowsData(dataSheet, dataRange);

    Reads all the data in a given range of spreadsheet cells. See Reading spreadsheet data using JavaScript Objects tutorial

      // Generate a personalized email.
      // Given a template string, replace markers (for instance ${"First Name"}) with
      // the corresponding value in a row object (for instance rowData.firstName).
      var emailText = fillInTemplateFromObject(emailTemplate, rowData);

    Given the template string described above, prepares the text of an email that contains the information submitted by a user using a form.

      MailApp.sendEmail(rowData.emailAddress, emailSubject, emailText);

    Sends the actual email.

  2. Function fillInTemplateFromObject
      // Search for all the variables to be replaced, for instance ${"Column name"}
      var templateVars = template.match(/\$\{\"[^\"]+\"\}/g);

    The JavaScript function match find instances of a given pattern of text (See Regular Expressions).

    var variableData = data[normalizeHeader(templateVars[i])];

    Tries to retrieve the value in a data object corresponding to a marker. This is done by normalizing the marker name and checking if the data object has a value associated with the normalized name.

      email = email.replace(templateVars[i], variableData || "");

    Replaces a marker in the email template string with a value or simply remove the marker if no value for it has been found.

Section 3: Exercise: modifying the form and the personalized email

At this point you should be able to modify the form and personalized emails.

  1. Click on the ‘Form’ menu, then ‘Edit Form’ and add a new question to retrieve the name of the department.
  2. Open the live form and submit some data (once again, make sure you put your own email address to be able to see the results).
  3. Notice how a new column has been created in the Spreadsheet that corresponds to the new question in the Form.
  4. Now, modify the template email (cell “A1” in the second sheet) and add a marker to display the department name somewhere in the email. Remember that the marker name has to match the column name in the data Sheet.
  5. Open the Script Editor. Update the variable dataRange in the sendEmails function to read the new department column:
    var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows() - 1, 5);
  6. Run the function sendEmails and check your email.
  7. Check that the department names appear in the email. If it does not, verify the marker name in the template string.

Section 4: More powerful templates

Are you interested in using a more advanced template library?Check out open source JavaScript template libraries, for instance EJS. You can quickly copy & paste the code in your script and start using it.

Also, if you would like to store your templates somewhere else (e.g. a web server), you will be very soon able to download the template using our URLFetch service.

Section 5: Full code

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheets()[0];
  var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows() - 1, 4);

  var templateSheet = ss.getSheets()[1];
  var emailTemplate = templateSheet.getRange("A1").getValue();

  // Create one JavaScript object per row of data.
  var objects = getRowsData(dataSheet, dataRange);

  // For every row object, create a personalized email from a template and send
  // it to the appropriate person.
  for (var i = 0; i < objects.length; ++i) {
    // Get a row object
    var rowData = objects[i];

    // Generate a personalized email.
    // Given a template string, replace markers (for instance ${"First Name"}) with
    // the corresponding value in a row object (for instance rowData.firstName).
    var emailText = fillInTemplateFromObject(emailTemplate, rowData);
    var emailSubject = "Tutorial: Simple Mail Merge";

    MailApp.sendEmail(rowData.emailAddress, emailSubject, emailText);
  }
}


// Replaces markers in a template string with values define in a JavaScript data object.
// Arguments:
//   - template: string containing markers, for instance ${"Column name"}
//   - data: JavaScript object with values to that will replace markers. For instance
//           data.columnName will replace marker ${"Column name"}
// Returns a string without markers. If no data is found to replace a marker, it is
// simply removed.
function fillInTemplateFromObject(template, data) {
  var email = template;
  // Search for all the variables to be replaced, for instance ${"Column name"}
  var templateVars = template.match(/\$\{\"[^\"]+\"\}/g);

  // Replace variables from the template with the actual values from the data object.
  // If no value is available, replace with the empty string.
  for (var i = 0; i < templateVars.length; ++i) {
    // normalizeHeader ignores ${"} so we can call it directly here.
    var variableData = data[normalizeHeader(templateVars[i])];
    email = email.replace(templateVars[i], variableData || "");
  }

  return email;
}





//////////////////////////////////////////////////////////////////////////////////////////
//
// The code below is reused from the 'Reading Spreadsheet data using JavaScript Objects'
// tutorial.
//
//////////////////////////////////////////////////////////////////////////////////////////

// getRowsData iterates row by row in the input range and returns an array of objects.
// Each object contains all the data for a given row, indexed by its normalized column name.
// Arguments:
//   - sheet: the sheet object that contains the data to be processed
//   - range: the exact range of cells where the data is stored
//   - columnHeadersRowIndex: specifies the row number where the column names are stored.
//       This argument is optional and it defaults to the row immediately above range;
// Returns an Array of objects.
function getRowsData(sheet, range, columnHeadersRowIndex) {
  columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;
  var numColumns = range.getEndColumn() - range.getColumn() + 1;
  var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);
  var headers = headersRange.getValues()[0];
  return getObjects(range.getValues(), normalizeHeaders(headers));
}

// For every row of data in data, generates an object that contains the data. Names of
// object fields are defined in keys.
// Arguments:
//   - data: JavaScript 2d array
//   - keys: Array of Strings that define the property names for the objects to create
function getObjects(data, keys) {
  var objects = [];
  for (var i = 0; i < data.length; ++i) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
      var cellData = data[i][j];
      if (isCellEmpty(cellData)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}

// Returns an Array of normalized Strings.
// Arguments:
//   - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    var key = normalizeHeader(headers[i]);
    if (key.length > 0) {
      keys.push(key);
    }
  }
  return keys;
}

// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
//   - header: string to normalize
// Examples:
//   "First Name" -> "firstName"
//   "Market Cap (millions) -> "marketCapMillions
//   "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function normalizeHeader(header) {
  var key = "";
  var upperCase = false;
  for (var i = 0; i < header.length; ++i) {
    var letter = header[i];
    if (letter == " " && key.length > 0) {
      upperCase = true;
      continue;
    }
    if (!isAlnum(letter)) {
      continue;
    }
    if (key.length == 0 && isDigit(letter)) {
      continue; // first character must be a letter
    }
    if (upperCase) {
      upperCase = false;
      key += letter.toUpperCase();
    } else {
      key += letter.toLowerCase();
    }
  }
  return key;
}

// Returns true if the cell where cellData was read from is empty.
// Arguments:
//   - cellData: string
function isCellEmpty(cellData) {
  return typeof(cellData) == "string" && cellData == "";
}

// Returns true if the character char is alphabetical, false otherwise.
function isAlnum(char) {
  return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    isDigit(char);
}

// Returns true if the character char is a digit, false otherwise.
function isDigit(char) {
  return char >= '0' && char <= '9';
}

Summary

Congratulations, you have completed this tutorial and have created your own simple Mail Merge application leveraging Google Forms. It should allow you to create new Forms and send personalized emails without having to modify any code. Feel free to reuse the script and adjust it to your needs!

我个人的建议是,再写一个onOpen函数,把这个功能添加到前台menu中

function onOpen() 
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Send Bulk Emails",
    functionName : "sendEmails"
  }];
  sheet.addMenu("Email App", entries);
};

根据以上代码,也可以进行修改,以便可以只对选中的行进行操作(onEdit)

7. 复制表格

copyTo(destination, options)

Copies the data from a range of cells to another range of cells. By default both the values and formatting are copied, but this can be overridden using advanced arguments.

 // The code below will copy only the values of the first 5 columns over to the 6th column.
 var sheet = SpreadsheetApp.getActiveSheet();
 sheet.getRange("A:E").copyTo(sheet.getRange("F1"), {contentsOnly:true});
 }

Parameters

Name Type Description
destination Range a destination range to copy to; only the top-left cell position is relevant
options Object a JavaScript object that specifies advanced parameters, as listed below

Advanced parameters

Name Type Description
formatOnly Boolean designates that only the format should be copied
contentsOnly Boolean designates that only the content should be copied

更多用法:https://developers.google.com/apps-script/reference/spreadsheet/range#copytodestination-options

实例:复制模板sheet

function copyTemplate()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = Utilities.formatDate(new Date(), 'America/Los_Angeles', 'MM/dd');
  var sheetNew = ss.getSheetByName('Template').copyTo(ss);
  
  var old = ss.getSheetByName(sheetName);
  if (old) ss.deleteSheet(old); // or old.setName(new Name);
  
  SpreadsheetApp.flush(); // Utilities.sleep(2000);
  sheetNew.setName(sheetName);

  ss.setActiveSheet(sheetNew);
}

8. 通过文件类型获取google drive文件,并且通过创建时间排序

function getAllSpreadsheets() {
  var output = '<!DOCTYPE html><html><head><base target="_top"></head><style>table{border-collapse: collapse;}table, th, td {border: 1px solid black;}</style><body>';
  output += '<table><tr><th colspan="4">List All Spreadsheets From ikeepstudying.com@gmail.com\'s Google Drive </th></tr>';
  output += '<tr><th>No.</th><th>File Name</th><th>File Url</th><th>File Created Date</th></tr>';
  var files = DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);
  
  var result = [];
  while (files.hasNext()) {
    var file = files.next();
    result.push([Utilities.formatDate(file.getDateCreated(), 'America/Los_Angeles', 'yyyy-MM-dd'),file.getName(),file.getUrl()]);
  } 
  result = result.sort().reverse(); // 降序,如果只是sort()的话,就是升序
  
  //Logger.log(result);
  
  var total = result.length;
  for (var i=0; i<total; i++) {
    output += '<tr><td>'+(i*1+1)+'</td><td>'+result[i][1]+'</td><td>'+result[i][2]+'</td><td>'+result[i][0]+'</td></tr>';
  }
  return output += '</table></body></html>';
}

 

更多参考:

Google App Scripts 内置常用函数 custom functions (字符串,翻译,日期,getRang,getSheetValues等)

Google Apps Script 入门 Beginner guide to coding with Google Apps Script

本文:Google App Scripts 自定义函数(custom function)笔记

Leave a Reply