EzDev.org

google-apps-script interview questions

Top google-apps-script frequently asked interview questions


How to automatically insert a new row and retain functions/formulas from last row?

I have a table with cells that have functions/formulas, like this one:

enter image description here

I need a script that creates a new row, copying with it the functions/formulas of the last used row. I find this script which create a new row but it doesn't copy functions/formulas. How could I implement this formatting copy task in Google Apps Script without having to manually select and copy?


Source: (StackOverflow)

How to re-use Google Apps Scripts in new Google Spreadsheet

I'm having an issue. Whenever I create a new spreadsheet, I'm not able to add a script I've already created. The list for recent projects or scripts is always blank. Anyone have a resolution or do I always have to copy the script code and insert it into the spreadsheet?


Source: (StackOverflow)

Google Sheets - Script to change individual text color within a cell

I have a Google spreadsheet with hundreds of employee names for a schedule. Each cell has multiple names within that cell that look like this:

First Last
First Last
First Last

First Last
First Last

Many of the names are color coded red while the others remain black. I'm using a script to remove the extra newlines to collapse the names to this (many of them are removed using find/replace for things such as days off, resulting in the blank newline):

First Last
First Last
First Last
First Last
First Last

The problem is, is that when this script is ran the red names have their font changed to black. I can't figure out a way to write a for loop to turn these individual lines within the cell back to red. I either end up with all the text in the cell being red, or HTML code indicating it should be red.

Any ideas on how I could make this work?


Source: (StackOverflow)

Google Spreadsheets: how to specify entire sheet as range?

The best workaround I have found so far is:

worksheet_name!$A$1:$YY

but ideally I would like to be able to simply write, e.g.:

worksheet_name!

So, does anyone know: is there a syntax for specifying a whole sheet as a range?


Source: (StackOverflow)

How can I automatically set "last updated" cell in row Google Docs Spreadsheets?

How can I automatically set "last updated" cell in row Google Docs Spreadsheets ?

I want to create a column where the cells' value will be automatically set to when that row was last amended. Is this possible? How do I do this / what option do I use to set this?

I see a workaround for MS Excel but I think Google Docs should have something similar, right? (fingers crossed)

We have multiple people editing a large sheet. We can see revision history, but it would be nicer if we had a column of "last updated" dates as well.


Source: (StackOverflow)

Cell reference with colour formatting

Is it possible to reference a cell in Google Spreadsheets so that cell where it gets displayed also displays it using the same text and cell colour formatting?

=A1

Will only reference dell's value. But if that particular cell has red background and white text I'd like that to be copied as well.

I'm leaning toward existing formula solutions rather than scripts. If applicable of course.


Source: (StackOverflow)

How do I mail merge from Google Spreadsheet to a Google Document?

With Microsoft Excel and Microsoft Word, it is easy to merge rows from a spreadsheet into pages in a Word file. This was traditionally used to make paper mailings. How can I do the same with Google Drive / Google Docs?

There are plenty of templates that offer spreadsheet-to-email mail merge: How do I do a Mail Merge with Gmail? but that's not what I'm after.


Source: (StackOverflow)

Is there any way to script a Google Docs spreadsheet?

I have a really complex spreadsheet in Google Docs, and while it works, having the ability to script it a bit (à la VBA in Microsoft Office) would make it a lot cleaner. I heard that this is doable in Google Apps, but I can't find it in Google Docs. Is this possible? Or is this something only Office can do?


Source: (StackOverflow)

Cumulative Sum without Script

For this question, I've created a Google Apps Script function, that calculates the cumulative sum of a range. Martin Hawksey on Google+ made a comment about a more efficient way of calculating the cumulative sum:

function cumulativeSum(array){
  var output = [];
  for (i in array){
    if(i==0) {
      if (array[i].length > 1) throw ("Only single column of data");
      output.push([array[i][0]]);
    } else {
      output.push([output[i-1][0] + array[i][0]]);
    }
  }
  return output;
}

My question is: can this be achieved with the usage of formula's?


Source: (StackOverflow)

How to pass a range into a custom function in Google Spreadsheets?

I want to create a function which takes in a range... something like:

function myFunction(range) {
  var firstColumn = range.getColumn();
  // loop over the range
}

A cell would reference it using:

=myFunction(A1:A4)

The problem is that when I try doing this, the parameter seems like it is only passing the values to the function. Thus, I cannot use any of the Range methods such as getColumn(). When I attempt to do so, it gives the following error:

error: TypeError: Cannot find function getColumn in object 1,2,3.

How can I send an actual range rather than just the values to one of my custom functions?


Source: (StackOverflow)

error making a copy of google app script

I'm trying to duplicate a Google app script file in Google drive that I own, but it gives me this error Limit Exceeded: Create. This is when I'm in the file and I use File --> Make a copy...

When I try to just right click and Make a copy it says Error creating "xxx".

Other files, such as Google sheets, seem to be duplicating fine.

EDIT: This morning it is copying fine. Not sure what happened


Source: (StackOverflow)

How to limit the length of data in a cell in Google Sheets?

Is it possible to put a limit to the length of cell content while entering data into a cell using Google Apps Script or any other way?


Source: (StackOverflow)

Google Spreadsheet Sort Two Columns

So I've found the following answer in regards to auto-sorting by one column, which works great How can I make some data on a Google Spreadsheet auto-sorting?

However, my use case requires the sorting of two columns. One column is whole numbers, while the second column are dates. I would like to sort the whole numbers column first, then within each number sort the date.

Any suggestions?


Source: (StackOverflow)

Place cell data from a Google Spreadsheet into a Google Document

I'm trying to populate an invoice.

I have a Google Spreadsheet that calculates hours per project, and I want to bring that into a Google Document, but despite much searching, have found no way to do this.

I thought importrange would work as a new equation, but no luck. how do you enter a formula into a Google Document?


Source: (StackOverflow)

Test Google Spreadsheets Add-On with custom functions

I have set up a Google Apps Scripts project with the intent of creating a Google Spreadsheets add-on with custom functions. However, when I use the option "Test as add-on", I can't use the functions that I created. When I paste the script code into the script editor opened from a spreadsheet it works fine, however.

Is this a limitation on how custom functions work in Spreadsheets, or am I simply doing something wrong?


Source: (StackOverflow)