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
}
// 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);
// 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);
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);
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);
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:
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:
Go to Tools, then Script Editor.
Copy and paste the code below into the Script Editor.
Save the script.
In the Script Editor go to Run, then onOpen.
Return to the open Spreadsheet and you’ll see a new menu item, Trim Cells (you can close the Script Editor).
Highlight the cells you’d like to trim and run your new script!
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);
// The code below will write "Esta es una <strong>prueba</strong>" to the log.
var spanish = LanguageApp.translate('This is a <strong>test</strong>',
'en', 'es', {contentType: 'html'});
Logger.log(spanish);
/**
* 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();
}
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;
};
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")});
}