Hot answers tagged google-apps-script
13
Yep its called Google Apps Script.
They just had a Apps Script Hackathon! They have some good examples and tutorials on the resource page. Someone also coded "Game Of Life" in a script :)
They also had a talk at Google I/O about it.
10
There's currently no way for a formula to do this and no default Google feature.
However, there are a few third party scripts that will do this for you:
Installing the script
Open your spreadsheet
Click on Tools menu
Click on Script Gallery
Search for Zebra Stripe
Install
Read the warning (and if you agree go on)
Authorize
Executing the script
Click ...
5
Leverage Google Apps Scripting to extend the UI
Update: This script is available in the Script Gallery. Just search for 'Text to Columns', install the script, and refresh the spreadsheet. Note: it may be slow to load the first time.
Text to Columns, is a very handy feature and one of the reasons that a lot of Google Spreadsheet users go back to using ...
4
You can try adding a Google Apps Script to capture when a cell is edited and add a timestamp to a different cell. Here's a previous answer that is similar:
Google Spreadsheet Timestamp?
function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
var r = s.getActiveCell();
if( ...
4
The following code retrieves the users, present within the Google Apps Domain:
function doGet() {
var app = UiApp.createApplication();
var users = UserManager.getAllUsers();
var flexTable = app.createFlexTable().setBorderWidth(1);
for (var i=0, len=users.length; i<len; i++) {
var user = users[i];
flexTable.setWidget(parseInt(i), 0, ...
3
Starting with the following line of code:
var range = sheet.getRange("A1:B10");
The sort can be performed in different ways:
range.sort([1,2]);
range.sort([{column: 1, ascending: true}, {column: 2, ascending: true}]);
Both ways are identical as the sort type is by default ascending, but the second option allows for different sort order when TRUE is ...
2
After some research, I figured it myself. Here is my solution if someone faces the same problem and needs a script for that:
var BASE_FORM_ID = "0AlDgVsiNXDhjdFlRTFVBeG5rNmprb3kHNxd1ItSFE";
var aNames = [
{name: "Prof. Fernando", course: "Course name"},
{name: "Prof. Rafael", course: "Course name"}
];
function makeForms() {
var aSpreadsheet;
...
2
When passing a Range to a Google spreadsheet function, the framework executes paramRange.getValues() implicitly and your function receives the values in the Range as a 2-dimensional array of strings, numbers or objects (like Date).
The Range object is not passed to your custom spreadsheet function.
The TYPEOF() function below will tell you what kind of data ...
2
You will need to write a Google Apps Script for that. You could let the first row of the spreadsheet be field names, and create a template document where the fields are referenced like [FIELD].
So if your spreadsheet looks like:
NAME | STREET | ZIP | TOWN
---------------------------------------------
Vidar | Karl Johans gate 15 | 0200 | ...
2
The key approach is to minimize the API calls. Performing those in for loops will slow down the process considerably, especially with 900 rows. I've re-worked your code in such a way, that it calls for the Spreadsheet API only twice:
function open_interventions() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// start logger time ...
2
Here's an Apps Script that will build a second sheet based on the first (javascript-only approach).
I only tested it on a simple 3x5 version of your first set of data, with row 1 (array index 0) being the headers.
It fails if the second sheet already exists - it's not pretty. But it, and the Apps-script tutorials, should get you started.
function ...
2
The follow formula will do just that; text-to-column:
A1=5,233,6,2,6,7,2,2,6,6
A2=SPLIT(A1;",")
And the next; text-to-row:
A1=5,233,6,2,6,7,2,2,6,6
A2=TRANSPOSE(SPLIT(A1;","))
UPDATE 03-02-2013
If you split the result of A1 and paste the values, it will give the same result as all the lines of code used in the OP's answer. I gave it a shot at it as ...
2
Not to point out the obvious but your function is never returning anything
function getNamedRange(n){
SpreadsheetApp.getActiveSpreadsheet().getRangeByName(n);
}
you need
function getNamedRange(n){
return SpreadsheetApp.getActiveSpreadsheet().getRangeByName(n);
}
now
var items = getNamedRange("budgetItems");
items.getValues();
should work just ...
2
This little script will a retrieve named range and make a summation:
function namedRange() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
var nRange = ss.getRangeByName("budgetItems");
var data = nRange.getValues();
var sum=0;
for(var i=0; i<5; i++) {
sum += parseInt(data[i]);
}
...
2
There are some inaccuracies in your code. I will try to guide you through the code.
Your code alone will not work, but you probably know that
first declare the active sheet (or spreadsheet)
var sh = SpreadsheetApp.getActiveSheet();
determine the last row in the sheet
var row = sh.getLastRow();
get the range and get the values
var aData = sh.getRange(1, 2, ...
1
Still don't have a solution for how to directly get values of unchecked checkboxes, but I was at least able to write a function that compares the correct answer to the submission.
This splits the correct answer into an array then compares each item to the submission:
function checkAnswer(answer, key) {
var kArray = key.split(', ');
var nArray = [];
for ...
1
I'm Ryan from the Gmail Meter team. Sorry to hear the reports aren't coming through. This is actually happening with a few of our users now, so you're not the only one.
Soon we hope to revamp the program with better features and of course better functionality. Please feel free to shoot me an email and I can personally keep you updated as Gmail Meter gets ...
1
There are a couple of ways to do that. First, you need to declare the spreadsheet:
var ss = SpreadsheetApp.getActiveSpreadsheet();
Secondly, declare the first sheet like this:
var sh0 = ss.getSheets()[0];
or like this:
var sh0 = ss.getSheetByName("Robin");
Then set the active cell and retrieve the column index:
var editedCell = ...
1
This piece of code works:
function onEdit(){
var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var editedCell = sh.getActiveRange().getColumnIndex();
if(editedCell == 2) {
var range = sh.getRange("A2:B10");
range.sort({column: 2});
}
}
One key difference is the .getColumnIndex. Without it you don't receive an integer !!
...
1
There's no way to do that in Google Spreadsheets, only the font sizes you already mentioned. There is however the possibility to do that via Google Apps Script. The following piece of code will create a menu option (upon opening):
function onOpen() {
// get active spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
// create menu
var menu = ...
1
I've created this little script to format the input:
function SCHANGE(array) {
if(typeof(array) == 'number') {
var number = array.toString();
} else {
var number = array.replace(/\D/g,'');
}
if(number.length != 12) {
return "<> 12"
} else {
var first = number.substring(0,4);
var mid = number.substring(4,8);
var last ...
1
This little script will:
create an extra menu item, thus making it accessible throughout the workbook. T
the menu item will set the active range to column E, last row + 1.
See the example I've prepared: Creating a function...Google spreadsheet is opened (edit rights)
function onOpen() {
// get active spreadsheet
var ss = ...
1
Put this under Tools >> Script editor, then Save.
You'll have to change the sheet names and ranges to match what you have.
// Every time you make an edit to the ss, onEdit runs
function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('Sheet1'); // input
var s2 = ss.getSheetByName('Sheet2'); // output
var events ...
1
This cannot be done without scripting, but the script itself is quite simple:
function onEdit() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var dataRange = sheet.getDataRange();
var backgroundColorRows = new Array();
var foregroundColorRows = new Array();
var maxRow = dataRange.getLastRow();
var maxCol = dataRange.getLastColumn();
for ...
1
You can also use the paint format tool. Highlight the row after you've changed the color, click the icon that looks like a paint roller, then click the next row you want to change color too (as long as it's okay if the rest of the format of both those rows are the same.) It's annoying to have to do it repetitively, but it's slightly faster than selecting the ...
1
I have posted a script to the Script Gallery called "Zebra Stripe Menu". If you add that to your spreadsheet, and make sure to open and close the script editor (there is a bug with recognizing the script). You will have a menu called "Zebra Stripes" and there you will find a couple of options for striping sheets and groups of cells. Some of the other options ...
1
You may want to consider using the service DropBox instead.
You are really set on Google Sites you create a sub-site with your files set to non public (requires login). yYou have to manually enter who has permissions to view the page however. You can also do page level permissions and select which emails can either view or edit certain pages. You would ...
1
In column C you will need to put into each cell the folowing formula:
=if(countif(B:B,B1)=countif(B$1:B1,B1),"Last_"&B1,"Not Last")
Explanation:
The first countif() tells you how many cells have that letter in Column B
The second countif() tells you how many are between the top of the column and that row.
If the two countif()'s give you the same ...
1
In addition to the remark made by Barry, here's a link that explains the usage of XML in combination with Google Apps Script (GAS): XML with GAS
You can use the importXML formula in GAS, but you have to address it as a formula:
function myXML() {
var cell = SpreadsheetApp.getActiveSpreadsheet()
.getActiveSheet().getActiveCell();
...
1
This isn't possible because ImportXML is a spreadsheet function and is not available in the Script Editor.
You would have to write a script to get the XML from the URL and then parse it manually.
What exactly are you trying achieve? (I am presuming that the Google Url and href elements are an example?)
Only top voted, non community-wiki answers of a minimum length are eligible
