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

1. google apps script string replace 文本代替

var FILE = SpreadsheetApp.openById("xyz");
var CONTENT = FILE.getSheetByName("Sheet1");
var A1 = CONTENT.getRange("I17").getValue();
var A1String = A1.toString().replace(".", "");

2. google apps script string split 文本分割成数组

function myFunction() {
  var array1 = splitTest();
  Logger.log(array1);
}

function splitTest() {
  var array1 = [{}];
  var string1 = "A, B, C, D";

  array1 = string1.split(", ");
  return array1
}

3. google apps script date format 日期格式

https://developers.google.com/apps-script/reference/utilities/utilities

https://developers.google.com/apps-script/reference/spreadsheet/sheet

处理日期和时间

AdWords 脚本经常需要处理日期和时间。常见的使用情形包括检索特定日期范围的报告,安排广告系列或广告组在特定时间投放,以及将脚本上次运行时间输出到电子表格。本指南介绍了在 AdWords 脚本中处理日期和时间的重要概念以及常见问题和推荐的解决方式。

基本概念

要在 AdWords 脚本中处理日期和时间,需要使用 JavaScript 的内置日期对象。JavaScript 日期对象代表特定时刻。有几种方法创建新的日期对象:

// Create a date object for the current date and time.
var now = new Date();

// Create a date object for a past date and time using a formatted string.
var date = new Date('February 17, 2016 13:00:00 -0500');

// Create a copy of an existing date object.
var copy = new Date(date);

新脚本用户常常困惑于日期对象对时区的处理方式。日期对象很自然地被认为是单个时区内的时钟时间,然而这并不正确。例如,在上面的代码段中,某些用户误以为 date 只对与创建它的时区时差为 -5 小时的那一个时区有效。按照这种错误的观点,在其他时区中使用时,需要对 date 进行“转换”。

对日期对象的正确认知方式是,它是独立于任何时区的特定时刻。虽然特定时刻在不同时区时钟上的显示不同,但是是同一时刻。例如,考虑这一代码段:

// Create two date objects with different times and timezone offsets.
var date1 = new Date('February 17, 2016 13:00:00 -0500');
var date2 = new Date('February 17, 2016 10:00:00 -0800');

// getTime() returns the number of milliseconds since the beginning of
// January 1, 1970 UTC.
// True, as the dates represent the same moment in time.
Logger.log(date1.getTime() == date2.getTime());

// False, as the dates are separate objects, though they happen to
// represent the same moment in time.
Logger.log(date1 == date2);

因为日期对象代表时间中的特定时刻,所以不需要进行跨时区的“转换”。相反,它可以被输出为针对特定时区的带格式字符串。

要以特定格式和时区的字符串输出日期,请使用 Utilities.formatDate(date, timeZone, format)。例如:

var date = new Date('February 17, 2016 13:00:00 -0500');

// February 17, 2016 13:00:00 -0500
Logger.log(Utilities.formatDate(date, 'America/New_York', 'MMMM dd, yyyy HH:mm:ss Z'));

// February 17, 2016 10:00:00 -0800
Logger.log(Utilities.formatDate(date, 'America/Los_Angeles', 'MMMM dd, yyyy HH:mm:ss Z'));

// 2016-02-17T18:00:00.000Z
Logger.log(Utilities.formatDate(date, 'Etc/GMT', 'yyyy-MM-dd\'T\'HH:mm:ss.SSS\'Z\''));

这些示例直接使用时区 ID指定时区。要检索与运行脚本的 AdWords 帐户相关联的时区,请使用 AdWordsApp.currentAccount().getTimeZone()

常见问题

记录日期对象时的默认时区

使用 Logger.log() 直接记录日期对象时,将使用默认格式和时区进行输出。例如:

var date = new Date('February 17, 2016 13:00:00 -0500');

// Wed Feb 17 10:00:00 GMT-08:00 2016
Logger.log(date);

与 AdWords 帐户的关联时区无关,默认时区为美国/洛杉矶(太平洋时间)。要使用自定义格式和时区将日期对象输出为字符串,以用于记录或其他目标,请务必使用 Utilities.formatDate(date, timeZone, format)

创建日期对象时的默认时区

如果使用不提供时区偏移量的字符串创建日期对象,则与 AdWords 帐户的关联时区无关,时区将被认定为美国/洛杉矶(太平洋时间)。例如:

// Create a date without specifying the timezone offset.
var date = new Date('February 17, 2016 13:00:00');

// Wed Feb 17 13:00:00 GMT-08:00 2016
Logger.log(date);

如果使用字符串创建日期对象,则一定要加入时区偏移量,以确保日期对象表示您真正需要的时刻。

日期对象方法的默认时区

JavaScript 日期对象有几种认定默认时区的方法,包括 getFullYear()getMonth()getDate()getDay()getHours()getMinutes() 及其对应的 set___()方法和 getTimezoneOffet()。在 AdWords 脚本中,与 AdWords 帐户的关联时区无关,默认时区为美国/洛杉矶(太平洋时间)。因此,除非您的 AdWords 帐户是在这个时区,否则一般而言,您应避免使用这些方法。

相反,要在您的帐户时区中检索日期对象的年、月、日期、星期、小时或分钟,请一定以您需要的指定日期或时间部分的格式使用 Utilities.formatDate(date, timeZone, format),并使用 AdWordsApp.currentAccount().getTimeZone() 检索帐户的时区。

从已设置格式的日期字符串创建日期对象

可以将已设置格式的日期字符串传递到日期构造函数,从而创建日期对象。例如:

var date = new Date('February 17, 2016 13:00:00 -0500');

构造函数只能解析特定日期字符串格式。为了确保您的日期字符串被正确解析,请务必采用 MMMM dd, yyyy HH:mm:ss Z 格式提供时。

例如,以下代码为当前帐户时区今天中午构造日期对象:

var now = new Date();
var timeZone = AdWordsApp.currentAccount().getTimeZone();
var noonString = Utilities.formatDate(now, timeZone,
                                      'MMMM dd, yyyy 12:00:00 Z')
var noon = new Date(noonString);

不要使用“z”模式来创建将传递给日期构造函数的日期字符串,原因是构造函数不一定始终能够对其进行解析。只使用“Z”模式。

日期数学运算

一些脚本需要对日期进行简单的数学运算,如找到给定日期之前或之后 X 天的日期。执行日期数学运算时,请一定使用 getTime()。对日期对象调用 getTime() 将返回自 UTC 1970 年 1 月 1 日起的毫秒数。您可以对这个值执行数学运算,然后使用 setTime() 将新的数值应用于某个日期对象,或在创建新的日期对象时将其作为参数提供。

例如:

var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
var now = new Date();
var yesterday = new Date(date.getTime() - MILLIS_PER_DAY);

在这个例子中,yesterday 是恰好 24 小时前。

报告

当使用 AdWordsApp.report(query, optArgs) 检索报告时,该 AWQL 查询要求将日期指定为 8 位整数 (YYYYMMDD)。同样,许多 AdWords 脚本对象中提供的 getStatsFor() 方法要求将日期指定为 8 位整数。使用 Utilities.formatDate(date, timeZone, format),采用这种格式来设置日期对象的格式。

例如,要检索一至三天前的报告:

var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
var now = new Date();
var from = new Date(now.getTime() - 3 * MILLIS_PER_DAY);
var to = new Date(now.getTime() - 1 * MILLIS_PER_DAY);

var timeZone = AdWordsApp.currentAccount().getTimeZone();
var report = AdWordsApp.report(
  'SELECT CampaignName, Clicks ' +
  'FROM   CAMPAIGN_PERFORMANCE_REPORT ' +
  'DURING ' + Utilities.formatDate(from, timeZone, 'yyyyMMdd') + ','
            + Utilities.formatDate(to, timeZone, 'yyyyMMdd'));

电子表格

AdWords 脚本经常将输出写到电子表格,包括日期对象。当通过传递日期对象在电子表格中设置单元格时,将使用电子表格的时区来解释该日期。例如,假设我们有一个电子表格的时区设置为太平洋时间:

// Suppose today is February 17, 2016 13:00:00 -0500 (Eastern Time)
var now = new Date();
spreadsheet.getRange('A1').setValue(now);

A1 的值将是 17-Feb-16 10:00:00。

为了确保将日期对象写入您所需的电子表格,请将电子表格的时区设置为与您 AdWords 帐户的时区一致:

spreadsheet.setSpreadsheetTimeZone(AdWordsApp.currentAccount().getTimeZone());

您也可以手动设置电子表格的时间

4. How do I add formulas to Google Spreadsheet using Google Apps Script? 添加格式

This is done using the setFormula for a selected cell. Below is an example of how to do this.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var cell = sheet.getRange("B5");
cell.setFormula("=SUM(B3:B4)");

You can also use setFormulaR1C1 to create R1C1 notation formulas. Example below.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var cell = sheet.getRange("B5");
// This sets the formula to be the sum of the 3 rows above B5
cell.setFormulaR1C1("=SUM(R[-3]C[0]:R[-1]C[0])");

To add several formulas to several fields use setFormulas. Example below

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

// This sets the formulas to be a row of sums, followed by a row of averages right below.
// The size of the two-dimensional array must match the size of the range.
var formulas = [
  ["=SUM(B2:B4)", "=SUM(C2:C4)", "=SUM(D2:D4)"],
  ["=AVERAGE(B2:B4)", "=AVERAGE(C2:C4)", "=AVERAGE(D2:D4)"]
];

var cell = sheet.getRange("B5:D6");
cell.setFormulas(formulas);

5. Javascript 字串处理(String Operations)

  • 寻找字串(search)
    stringObject.search(searchstring)
    stringObject.search(寻找的字串)
    大小写必须相符var str = “test String”;
    alert(str.search(“Str”));
    alert(str.search (“str”));
    输出结果:5
    输出结果:-1
  • 传回字串内相符的字串(match)
    无相符字串则传回null,大小写要相符var str = “test String”;
    alert(str.match(“TEST”));
    alert(str.match (“Str”));
    输出结果:null
    输出结果:Str
  • 取得字串长度(length)
    var str = “test String”;
    alert(str.length);
    输出结果:11
  • 寻找某字串在字串内的位置(indexOf)
    stringObject.indexOf(searchvalue,fromindex)
    stringObject.indexOf (寻找的字串,开始寻找的位置)var str = “test String”;
    alert(str.indexOf(” Str”));
    alert(str.indexOf(“Str”,6));
    输出结果:5
    输出结果:-1
  • 从后往前找某字串在字串内的位置(lastIndexOf)
    stringObject.lastIndexOf(searchvalue,fromindex)
    stringObject.lastIndexOf (寻找的字串,开始寻找的位置)var str = “test String”;
    alert(str .lastIndexOf(“Str”));
    alert(str.lastIndexOf(“Str”,2));
    alert(str.lastIndexOf(“Str”,10));
    输出结果:5
    输出结果:-1
    输出结果:5
  • 变更英文字大小写
    toUpperCase()大写
    toLowerCase()小写var str = “test String”;
    alert(str.toUpperCase());
    alert(str.toLowerCase());
    输出结果:TEST STRING
    输出结果:test string
  • 传回字串内某位置的字元(charAt)
    stringObject.charAt(index)
    位置由0开始var str = “test String”;
    alert(str.charAt(3));
    alert(str.charAt(7)) ;
    输出结果:t
    输出结果:r
  • 传回参数中以Unicode值所代表的字元组成的字串(fromCharCode)
    String.fromCharCode(numX,numX,…,numX)
    alert(String.fromCharCode(65,66,67));
    输出结果: ABC
  • 传回字串内某位置区间的字串(substring & substr)
    stringObject.substring(start,stop)
    stringObject.substring(开始位置,结束位置)
    stringObject.substr(start,length)
    stringObject.substr(开始位置,字串长度)
    (开始位置)若为负数则由后往前数,但不适用于IEalert(“0123456789”.substring(4,6))
    alert(“0123456789”.substr(4,6))
    输出结果:45
    输出结果:456789
  • 字串取代(replace)
    stringObject.replace(findstring,newstring)
    stringObject.replace(要找寻的字串,换成新的字串)
    大小写必须相符var str = “test String”;
    alert(str.replace(” string”, “Text”));
    alert(str.replace(“String”, “Text”));
    输出结果:test String
    输出结果:test Text
  • 组合字串(concat)
    stringObject.concat(stringA,stringB,…,stringX)
    stringObject.concat(字串A,字串B,…,字串X)var strA = “test “;
    var strB = “String”;
    alert(str1.concat(str2));
    输出结果:test String
  • 取得切割字串(slice)
    stringObject.slice(start,end)
    stringObject.slice(开始位置,结束位置)var str = “test String”;
    alert(str.slice(5));
    alert(str.slice(0 ,4));
    输出结果:String
    输出结果:test
  • 分割字串(split)
    stringObject.split(separator, howmany)
    stringObject.split(分割字串,分割后各字串的字元数)var str = “test String”;
    alert(str.split(“”)) ;
    alert(str.split(” “,4));
    输出结果:t,e,s,t, ,S,t,r,i,n,g
    输出结果:test,String

6. 用于在现金明细表中自动求余额。这是用于Google Drive里电子表格(Spreadsheet)的脚本,使用Google Apps Script编写,类似于JavaScript

function sumBalance() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  
  for (var i = 2; i <= numRows; i++) {
    var rowSum = 0;
    for (var j = 2; j <= i; j++) {
      rowSum = rowSum + sheet.getRange(j, 8, 1, 1).getValue();
    }
    
    sheet.getRange(i, 9, 1, 1).setValue(rowSum);
  }
}

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the readRows() function specified above.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Sum Balance",
    functionName : "sumBalance"
  }];
  sheet.addMenu("Script Center Menu", entries);
};

7. Script manager

Script manager可以帮你确认目前文件中所有使用上的Script,在这边可以进行所有的Script新增、编辑,或者执行其中一个function,列表中右边的script就是script的project name,而function就是project内可执行的function 如果不想直接修改从Script Gallery找来的Script,也可透过New来新增自己的project & function 其中有几个内定的function:

  • onInstall:建立文件时执行
  • onOpen:开启文件时执行
  • onEdit:编辑文件时执行(编辑完每一个Cell)

更多内容:https://developers.google.com/apps-script/guides/triggers/

 

Getting started

To use a simple trigger, simply create a function that uses one of these reserved function names:

  • onOpen(e) runs when a user opens a spreadsheet, document, or form that he or she has permission to edit.
  • onEdit(e) runs when a user changes a value in a spreadsheet.
  • onInstall(e) runs when a user installs an add-on.
  • doGet(e) runs when a user visits a web app or a program sends an HTTP GET request to a web app.
  • doPost(e) runs when a program sends an HTTP POST request to a web app.

The e parameter in the function names above is an event object that is passed to the function. The object contains information about the context that caused the trigger to fire, but using it is optional.

onOpen()

The onOpen() trigger runs automatically when a user opens a spreadsheet, document, or form that he or she has permission to edit. (The trigger does not run when responding to a form, only when opening the form to edit it.)onOpen() is most commonly used to add custom menu items to Google Sheets, Docs, or Forms.

function onOpen() {
  // Add a custom menu to the spreadsheet.
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .createMenu('Custom Menu')
      .addItem('First item', 'menuItem1')
      .addToUi();
}

onEdit()

The onEdit() trigger runs automatically when a user changes the value of any cell in a spreadsheet. Most onEdit()triggers use the information in the event object to respond appropriately. For example, the onEdit(e) function below sets a comment on the cell that records the last time it was edited.

function onEdit(e){
  // Set a comment on the edited cell to indicate when it was changed.
  var range = e.range;
  range.setNote('Last modified: ' + new Date());
}

onInstall()

The onInstall() trigger runs automatically when a user installs an add-on. The most common use of onInstall() is simply to call onOpen() to add custom menus. After all, when an add-on is installed, the file is already open, and thus onOpen() will not run on its own unless the file is reopened.

function onInstall(e) {
  onOpen(e);
}

doGet() and doPost()

The doGet() trigger runs automatically when a user visits a web app or a program sends an HTTP GET request to a web app. doPost(e) runs when a program sends an HTTP POST request to a web app. These triggers are demonstrated more in the guides to web apps, HTML service, and Content service. Note that doGet() and doPost()are not subject to the restrictions listed above.

8. How to trim all cells in Google Spreadsheets 去空白

I’ve been using Google Spreadsheets to clean data and I recently discovered this little script to trim (remove leading and trailing spaces) from all cells. This is really great. In an open Google Spreadsheet:

  1. Go to Tools, then Script Editor.
  2. Copy and paste the code below into the Script Editor.
  3. Save the script.
  4. In the Script Editor go to Run, then onOpen.
  5. Return to the open Spreadsheet and you’ll see a new menu item, Trim Cells (you can close the Script Editor).
  6. Highlight the cells you’d like to trim and run your new script!
Google App Scripts 内置常用函数 custom functions
Google App Scripts 内置常用函数 custom functions

The Trim Cell Script

var ss = SpreadsheetApp.getActiveSpreadsheet();
 
function trimSpacesInSelectedCells() {
  var sheet = ss.getActiveSheet();
  var activeRange = sheet.getActiveRange();
  for (var cellRow = 1; cellRow <= activeRange.getHeight(); cellRow++) {
    for (var cellColumn = 1; cellColumn <= activeRange.getWidth(); cellColumn++) {
      cell = activeRange.getCell(cellRow, cellColumn);
      cellFormula = cell.getFormula();
      if (cellFormula[0] != "=") {
        cellValue = cell.getValue();
        cell.setValue(String(cellValue).trim());
      }
    }
  }
}
 
function onOpen() {
  var entries = [{
    name : "Remove leading and trailing spaces",
    functionName : "trimSpacesInSelectedCells"
  }];
  ss.addMenu("Trim Cells", entries);
}

9. google translate 谷歌翻译

translate(text, sourceLanguage, targetLanguage)

 // The code below will write "Esta es una prueba" to the log.
 var spanish = LanguageApp.translate('This is a test', 'en', 'es');
 Logger.log(spanish);

translate(text, sourceLanguage, targetLanguage, advancedArgs)

 // The code below will write "Esta es una <strong>prueba</strong>" to the log.
   var spanish = LanguageApp.translate('This is a &lt;strong&gt;test&lt;/strong&gt;',
                                       'en', 'es', {contentType: 'html'});
   Logger.log(spanish);

更多:https://developers.google.com/apps-script/reference/language/language-app#translate(String,String,String)

Code.gs

/**
 * Creates a menu entry in the Google Docs UI when the document is opened.
 *
 * @param {object} e The event parameter for a simple onOpen trigger. To
 *     determine which authorization mode (ScriptApp.AuthMode) the trigger is
 *     running in, inspect e.authMode.
 */
function onOpen(e) {
  DocumentApp.getUi().createAddonMenu()
      .addItem('Start', 'showSidebar')
      .addToUi();
}

/**
 * Runs when the add-on is installed.
 *
 * @param {object} e The event parameter for a simple onInstall trigger. To
 *     determine which authorization mode (ScriptApp.AuthMode) the trigger is
 *     running in, inspect e.authMode. (In practice, onInstall triggers always
 *     run in AuthMode.FULL, but onOpen triggers may be AuthMode.LIMITED or
 *     AuthMode.NONE.)
 */
function onInstall(e) {
  onOpen(e);
}

/**
 * Opens a sidebar in the document containing the add-on's user interface.
 */
function showSidebar() {
  var ui = HtmlService.createTemplateFromFile('Sidebar').evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle('Translate');
  DocumentApp.getUi().showSidebar(ui);
}

/**
 * Gets the text the user has selected. If there is no selection,
 * this function displays an error message.
 *
 * @return {Array.<string>} The selected text.
 */
function getSelectedText() {
  var selection = DocumentApp.getActiveDocument().getSelection();
  if (selection) {
    var text = [];
    var elements = selection.getSelectedElements();
    for (var i = 0; i < elements.length; i++) {
      if (elements[i].isPartial()) {
        var element = elements[i].getElement().asText();
        var startIndex = elements[i].getStartOffset();
        var endIndex = elements[i].getEndOffsetInclusive();

        text.push(element.getText().substring(startIndex, endIndex + 1));
      } else {
        var element = elements[i].getElement();
        // Only translate elements that can be edited as text; skip images and
        // other non-text elements.
        if (element.editAsText) {
          var elementText = element.asText().getText();
          // This check is necessary to exclude images, which return a blank
          // text element.
          if (elementText != '') {
            text.push(elementText);
          }
        }
      }
    }
    if (text.length == 0) {
      throw 'Please select some text.';
    }
    return text;
  } else {
    throw 'Please select some text.';
  }
}

/**
 * Gets the stored user preferences for the origin and destination languages,
 * if they exist.
 *
 * @return {Object} The user's origin and destination language preferences, if
 *     they exist.
 */
function getPreferences() {
  var userProperties = PropertiesService.getUserProperties();
  var languagePrefs = {
    originLang: userProperties.getProperty('originLang'),
    destLang: userProperties.getProperty('destLang')
  };
  return languagePrefs;
}

/**
 * Gets the user-selected text and translates it from the origin language to the
 * destination language. The languages are notated by their two-letter short
 * form. For example, English is "en", and Spanish is "es". The origin language
 * may be specified as "auto" to indicate that Google Translate should
 * auto-detect the language.
 *
 * @param {string} origin The two-letter short form for the origin language.
 * @param {string} dest The two-letter short form for the destination language.
 * @param {boolean} savePrefs Whether to save the origin and destination
 *     language preferences.
 * @return {string} The result of the translation.
 */
function runTranslation(origin, dest, savePrefs) {
  var text = getSelectedText();
  if (savePrefs == true) {
    var userProperties = PropertiesService.getUserProperties();
    userProperties.setProperty('originLang', origin);
    userProperties.setProperty('destLang', dest);
  }

  // The Translate service expects an empty string to signify auto-detect.
  if (origin == "auto") {
    origin = "";
  }

  var translated = [];
  for (var i = 0; i < text.length; i++) {
    translated.push(LanguageApp.translate(text[i], origin, dest));
  }

  return translated.join('\n');
}

/**
 * Replaces the text of the current selection with the provided text, or
 * inserts text at the current cursor location. (There will always be either
 * a selection or a cursor.) If multiple elements are selected, only inserts the
 * translated text in the first element that can contain text and removes the
 * other elements.
 *
 * @param {string} newText The text with which to replace the current selection.
 */
function insertText(newText) {
  var selection = DocumentApp.getActiveDocument().getSelection();
  if (selection) {
    var replaced = false;
    var elements = selection.getSelectedElements();
    if (elements.length == 1 &&
        elements[0].getElement().getType() ==
        DocumentApp.ElementType.INLINE_IMAGE) {
      throw "Can't insert text into an image.";
    }
    for (var i = 0; i < elements.length; i++) {
      if (elements[i].isPartial()) {
        var element = elements[i].getElement().asText();
        var startIndex = elements[i].getStartOffset();
        var endIndex = elements[i].getEndOffsetInclusive();

        var remainingText = element.getText().substring(endIndex + 1);
        element.deleteText(startIndex, endIndex);
        if (!replaced) {
          element.insertText(startIndex, newText);
          replaced = true;
        } else {
          // This block handles a selection that ends with a partial element. We
          // want to copy this partial text to the previous element so we don't
          // have a line-break before the last partial.
          var parent = element.getParent();
          parent.getPreviousSibling().asText().appendText(remainingText);
          // We cannot remove the last paragraph of a doc. If this is the case,
          // just remove the text within the last paragraph instead.
          if (parent.getNextSibling()) {
            parent.removeFromParent();
          } else {
            element.removeFromParent();
          }
        }
      } else {
        var element = elements[i].getElement();
        if (!replaced && element.editAsText) {
          // Only translate elements that can be edited as text, removing other
          // elements.
          element.clear();
          element.asText().setText(newText);
          replaced = true;
        } else {
          // We cannot remove the last paragraph of a doc. If this is the case,
          // just clear the element.
          if (element.getNextSibling()) {
            element.removeFromParent();
          } else {
            element.clear();
          }
        }
      }
    }
  } else {
    var cursor = DocumentApp.getActiveDocument().getCursor();
    var surroundingText = cursor.getSurroundingText().getText();
    var surroundingTextOffset = cursor.getSurroundingTextOffset();

    // If the cursor follows or preceds a non-space character, insert a space
    // between the character and the translation. Otherwise, just insert the
    // translation.
    if (surroundingTextOffset > 0) {
      if (surroundingText.charAt(surroundingTextOffset - 1) != ' ') {
        newText = ' ' + newText;
      }
    }
    if (surroundingTextOffset < surroundingText.length) {
      if (surroundingText.charAt(surroundingTextOffset) != ' ') {
        newText += ' ';
      }
    }
    cursor.insertText(newText);
  }
}

// Helper function that puts external JS / CSS into the HTML file.
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

下载全部:google-apps-script-samples-master

10. 获取当前sheet元素 getA1Notation,getName,getValue, setValue …

function onEdit(event)
{
  // all sheets
  var sheets = SpreadsheetApp.getActiveSpreadsheet();
  
  // one tab of all sheets
  var tab    = event.source.getActiveSheet();
  
  // current single cell of one tab
  var cell   = event.source.getActiveRange();
  
  
  /*
  * 
  * cell.getA1Notation(): current cell position, like: D2, F4, A1
  * cell.getValue(): current cell's value you input
  * tab.getName(): current sheet tab name, like: sheet1, sheet2, sheet3
  * toast: tip function on right-bottom corner
  * setValue: function put value
  *
  */
  // if(tab.getName()!='main sheet'){return};
  sheets.toast('hi = '+cell.getA1Notation()+'/'+tab.getName()+'   value='+cell.getValue());
  
  var sheet = sheets.getSheetByName('Memo');
  sheet.getRange(sheet.getLastRow()+1,1).setValue(cell.getA1Notation());

  //For range
  var activerange = sheet.getActiveRange().getValues();
  //for cell
  var activecell = sheet.getActiveCell().getValue();

  // popup
  Browser.msgBox('new:'+activerange+'==='+activecell);
}

或者:

function getValuesFromEditor(editor){
      var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell();
      var rowData = editor.myTextarea;
}

function getData1() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 // Returns the active cell
 var range = sheet.getActiveCell();
 var vals = range.getValue();
  return vals;
};

11. Class Sheet getSheetValues方法

取出储存格范围内的值
语法:
getSheetValues(startRow, startColumn, numRows, numColumns)
getSheetValues(起始列号,起始行号,列展延量,行展延量 )

准备工作:
请在A2:H8内输入任意内容,接下来再进入工具/指令码编辑器内将以下的代码COPY贴上

//****取出储存格范围内的值
//getSheetValues(startRow, startColumn, numRows, numColumns) 
//适用于取出有范围的储存格
//这个方法取出的值Values,可以先下用setValue方法(记住没有s喔)看一下格式全貌或用Logger.log(values);来检视
//样貌如后[14-01-23 00:02:52:601 HKT] [[*车种*, *车次*, *经由*, *发车站->终点站*, *台北开车时间*, *花莲到达时间*, *行驶时间*, *备注*], 
//[自强, 202.0, -,树林- >花莲, -693958.7430555555, -693958.65625, 02小时05分,逢周一、五至日行驶。新自强号(普悠玛)列车,不发售无座票。], 
function testrun(){
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //指定工作簿
  var sheet = ss.getSheets()[1]; //指定工作表
  //var cell=sheet.getRange("A21:H28"); //这个表示法与var cell=sheet.getRange(21, 1, 7, 8);相同
  var  values= sheet.getSheetValues(2, 1, 7, 8); //取得来源储存格范围内的值
  var cell=sheet.getRange(21, 1, 7, 8); //指定目地储存格范围
  cell.setValues(values);//要讓資料自動展開,不可用cell.setValue(values),不然不會分解值,會全部放在第一個儲存格
  //Logger.log(values);
}

这段代码同时演示了[取值]与[赋值]的方法,
var cell=sheet.getRange(21, 1, 7, 8);这行代码中的7、8因为必须与values二维阵列变数大小一致,所以可以改写成

var cell=sheet.getRange(21, 1, values.length, values[0].length);

其余的在代码内已有说明语法请自行演练,以下是我跑出来的结果

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

12. 从google sheet中搜索字符串,onSearch

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Search", functionName: "onSearch"} ];
  ss.addMenu("Commands", menuEntries);    
}

function onSearch()
{
    var searchString = "Test11";
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetName"); 
    var column =4; //column Index   
    var columnValues = sheet.getRange(2, column, sheet.getLastRow()).getValues(); //1st is header row
    var searchResult = columnValues.findIndex(searchString); //Row Index - 2

    if(searchResult != -1)
    {
        //searchResult + 2 is row index.
        SpreadsheetApp.getActiveSpreadsheet().setActiveRange(sheet.getRange(searchResult + 2, 1))
    }
}

Array.prototype.findIndex = function(search){
  if(search == "") return false;
  for (var i=0; i<this.length; i++)
    if (this[i] == search) return i;

  return -1;
}

源自:http://stackoverflow.com/a/18482717

13.复制一个sheet

function copyTemplate(templateName)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    ss.insertSheet(Utilities.formatDate(new Date(), 'America/Los_Angeles', 'MM/dd'),1,{template: ss.getSheetByName(templateName?templateName:"Template")});
}

 

更多参考:

https://developers.google.com/apps-script/reference/document/text

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet

google spreadsheet programming:PDF

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

 

本文:Google App Scripts 内置常用函数 custom functions (字符串,翻译,日期,搜索,复制模板,getRang,getSheetValues等)

One Comment

Leave a Reply