New answers tagged

0

Use this script, adjusting the sheet name and date format if necessary. It should be entered in Script Editor, found in Tools menu. After saving the script file, go to Resources > Current project's triggers, and create a trigger to run the function newColumn daily, at your preferred time. function newColumn() { var ss = SpreadsheetApp.getActiveSpreadsheet(...


0

There is no way to get the list of sheet names with a built-in spreadsheet function, not even for the current spreadsheet. You can use a script to fetch sheet names and create a formula automatically. The script given below adds a menu item Import > all sheets to the menu, when the spreadsheet is opened. When invoked, it will get the sheet names from the ...


1

A column of values is represented in Apps Script as [['a'], ['b'], ['c']]. A row is represented as [['a', 'b', 'c']]. The following functions transform one to the other: function col2row(column) { return [column.map(function(row) {return row[0];})]; } function row2col(row) { return row[0].map(function(elem) {return [elem];}); }


0

In order to limit the range of a script, get the row and column of the active cell, and check that it falls within some bounds: function onEdit(e) { var row = e.range.getRow(); var col = e.range.getColumn(); if (col >= 7 && col <= 9 && row >= 25 && row <= 47) { if (e.value == 1) { e.range.setFormula('=CHAR(...


0

There is no setting in the Gmail UI for a time based auto response. The Email Settings API does allow you to enable, disable, change, and poll the current state of the autoresponder.


2

Is there a way to track the indexing status? To know if a file is indexed look at the contentHints.indexableText property by using the get method from Google Drive REST API. Is there a way to force a specific folder on Drive to be indexed with priority? AFAIK Google Drive doesn't include any way for end users to force a specific folder to be indexed ...


0

I have expanded the functionality of the script from the linked question accordingly. First it checks whether the sheet with given name already exists. If it doesn't, the sheet is created and the headers of the form responses sheet are copied there. Otherwise, the existing sheet it used. Either way, the newly submitted data is then copied to the sheet. ...


1

This can be done with a script. Enter the following in Script Editor, found under Tools. Change the 'Name' to whatever is the name of the form field that should be used. function formSheet(e) { var sheetName = e.namedValues['Name'][0]; var sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName); sheet.getRange(1, 1, 1, e.values.length)....


0

Adding additional cell (which can be hidden after) to copy changing cell's data (... .setValue(time)) and manipulate the data in other sheets can be a quick solution in some variants.


-1

I created a sheet that uses filters that allow all kinds of exports. Works great - hope it helps others Shared here: https://docs.google.com/spreadsheets/d/14nOGCyu2nOLDQ_cJNQfVsDiBxy-Te3W8eh9du3C4sVQ/copy


1

In case if you need to add a new row on top (first row) and copy formula from the first top row then you'll need to copy formulas across using getFormulas() and setFormulas() functions. You can change the value of firstRow to 2 if your spreadsheet has headers for example. function addFirstRow() { var firstRow = 1; var sh = ss.getActiveSheet(); ...


1

I was working on this a few months ago and came up with a very simple kludge: create a new sheet with the name of each cell as its contents: Cell A1 could look like: = arrayformula(cell("address",a1:z500)) Name the sheet "Ref". Then when you need a reference to a cell as a string instead of the contents, you use: = some_new_function('Ref'!C45) Of ...


1

Here is my answer to my own question. function autoReply() { var interval = 5; // if the script runs every 5 minutes; change otherwise var wkend = [6,0]; // 1=Mo, 2=Tu, 3=We, 4=Th, 5=Fr, 6=Sa, 0=Su var wkendMessage = "Hi, Thank you for contacting us. The office is now closed. All emails will be attended to when the office re-opens at 9:...


2

Checking the name of the sheet would be like this: function onEdit() { var s = SpreadsheetApp.getActiveSheet(); if (s.getName() == 'Inventory') { // the rest of the script } } The line you were trying, with event.source.getActiveSheet() would work if you actually took the event object as a parameter: onEdit(event). function onEdit(event) { ...


3

You need to use the google.script.run.myServerFunctionName() client side API. You will need to use that anyway, in order to send the form data, unless you are using a HTTPS GET or POST request to send the data somewhere. Apps Script documentation - google.script.run


1

If you're trying to grab data that's stored in a list or table, you should use =ImportHTML(URL,Type,Index). Where Type is "List" or "Table" and index is which # table/list you want to import. Documentation: https://support.google.com/docs/answer/3093339



Top 50 recent answers are included