google-apps-script interview questions
Top google-apps-script frequently asked interview questions
I fear this may have an obvious answer, but I've been knocking my head over this and can't find it!
When creating a new spreadsheet, we can easily create a script within it. I like to call it a macro.
But when I create an even newer spreadsheet, I can't find a good way to get my macro!
I just wish to save a file and re-use it at will, without needing to copy & paste & rename. My goal here is sharing the script and whoever is going to use it isn't a programmer. This got to be really simple!
Any insights?
Source: (StackOverflow)
I have an export for a group that composed of student/mentors. Everyone in the group is both a student and a mentor. I need to transform the data to have mentors with assigned students rather than students with assigned mentors.
The software gives me an export such as five columns in a sheet that has student names and mentors that have been assigned to that student:
ID Numbers of Students | Students | Mentor 1 | Mentor 2 | Mentor 3
I need a sheet to reference the data with mentors in one column and corresponding students such as:
ID Numbers of Mentors | Mentors | Student 1 | Student 2 | Student 3 | Student 4
Students are mentors and mentors are students - it is the same set of names. Mentors have a max of four assigned students. Only one row per Mentor.
I am thinking QUERY() or perhaps ARRAYFORMULA() but I have not been able to assigned use VLOOKUP() or FILTER() with ARRAYFORMULA(). I am not getting anywhere with it.
What Google Spreadsheet formula do I use to transpose the data as described? Can you provide an example? I will accept a custom function, but surly this can be done with existing tools.
Source: (StackOverflow)
I'd like to colorize a cell based on it's contents. 0 = red, 100 = green, and linearly interpolated between.
function LinInt(x){
var ss = SpreadsheetApp.getActiveSheet();
var cell = ss.getActiveRange();
var hue;
hue = (x/100)*120;
var color = HSVtoRGB(hue, 40, 100);
cell.setBackground(color);
return x;
}
function HSVtoRGB(h, s, v) {
var r, g, b, i, f, p, q, t;
if (h && s === undefined && v === undefined) {
s = h.s, v = h.v, h = h.h;
}
i = Math.floor(h * 6);
f = h * 6 - i;
p = v * (1 - s);
q = v * (1 - f * s);
t = v * (1 - (1 - f) * s);
switch (i % 6) {
case 0: r = v, g = t, b = p; break;
case 1: r = q, g = v, b = p; break;
case 2: r = p, g = v, b = t; break;
case 3: r = p, g = q, b = v; break;
case 4: r = t, g = p, b = v; break;
case 5: r = v, g = p, b = q; break;
}
return '#'+((b | g << 8 | r << 16) / 0x1000000).toString(16).substring(2);
}
When I do this I get an error saying that I am not allowed to call setBackground. I assume this is because I'm not allowed to change the color of one cell from another because I'm screwing up getting the current cell that the function is running in.
My question is: How do I get the cell that the function is running in so that I can call setBackground() on it? For example, I'd like to type =LinInt(50) into cell A1 and have A1 be yellow and have the number 50 in it. Then when I type =LinInt(100) into cell A2 it is green and has the number 100 in it.
Source: (StackOverflow)
I have a Google Spreadsheet that lists various events. Students have entered their name next to events that they would like to sign up for. It looks like this:
Each student may sign up for more than one event.
I would like to automatically generate a second sheet in the spreadsheet that lists the students alphabetically and lists next to them the events they have signed up for. Something like this:
I can generate the ordered list of names using SORT(UNIQUE()) but I'm having a lot of trouble getting the associated events to display. I thought HLOOKUP or QUERY or a combination of IFs and ORs or maybe some sort of scripting would work, but I haven't had any success.
Source: (StackOverflow)
I made a Google Apps Script, but often I get this error: Script invoked too many times per second in this Google user account.
This is the script:
function papersControl(tipo, col, prev_cell, quant) {
var cell = 0;
var gender = tipo.split(" ");
if (tipo == col) {
cell = parseInt(prev_cell,10) + parseInt(quant,10);
return cell;
}
if ( (col == "Special Paper") && (gender[0] == "Certificate") && (gender[2] != "Voucher") ) {
cell = parseInt(prev_cell,10) + parseInt(quant,10);
return cell;
}
if ( (col == "Special Paper 2") && (gender[0] == "Certificate") && (gender[2] == "Vouncher") ) {
cell = parseInt(prev_cell,10) + parseInt(quant,10);
return cell;
}
else
return prev_cell;
}
I'd like to freeze the past recorded values in a cell in order to reduce the number of "times per second" that the script invokes Google servers, since I don't need to compute anymore. How can I do this?
Source: (StackOverflow)
I'm trying to write an extension for Google Sheets to add the CHIDIST() function as in Microsoft Excel and LibreOffice Calc. My current attempt does not produce the same results as the LibreOffice function. Any help would be greatly appreciated.
function CHIDIST(value, degrees_of_freedom) {
x = value;
v = degrees_of_freedom;
if(x>0) {
return (Math.pow(x,(v-2)/2)*Math.pow(Math.E,-x/2))/(Math.pow(2,v/2)*gamma(v/2));
}
else {
return 0;
}
}
function gamma(x){
var p = [0.99999999999980993, 676.5203681218851, -1259.1392167224028,
771.32342877765313, -176.61502916214059, 12.507343278686905,
-0.13857109526572012, 9.9843695780195716e-6, 1.5056327351493116e-7];
var g = 7;
if(x < 0.5){
return Math.PI / (Math.sin(Math.PI * x)*gamma(1-x));
}
x -= 1;
var a = p[0];
var t = x+g+0.5;
for(var i = 1; i < p.length; i++){
a += p[i]/(x+i);
}
return Math.sqrt(2*Math.PI)*Math.pow(t, x+0.5)*Math.exp(-t)*a;
}
This is the equation that I modeled the program after:
Source: (StackOverflow)
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)
I have a column in Google Spreadsheet that calls an outside webservice using URLFetch in a Google Script. The cell uses two parameters in that cell's row. After a change occurs where those two parameters will return a different value if sent to the webservice again the cell retains a cached value. I know it is cached because if I copy and paste the row to another row, the webservice calculated values change to the correct ones.
Is there a way to force a row or a cell in a Google Spreadsheet to recalculate its value, especially if it is using an outside webservice in a custom fucntion?
Source: (StackOverflow)
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)
How do you do a text-to-columns conversion in Google Spreadsheets?
For example, I have the following data string in one cell:
5,233,6,2,6,7,2,2,6,6
I want to break it apart by the comma separator into columns.
Edit: I changed the accepted answer to one that doesn't use Google Apps Scripting because Google seems hell-bent on nerfing it's ability.
Source: (StackOverflow)
I would like to find out which cells have formula dependencies in a large spreadsheet. I am looking for a way to do something like OpenOffice
Tools>Detective>Trace dependents
and
Edit>Find & Replace>Search in formulas
or a way to create a trigger in GAS that gets called when a given cell value is referenced and can identify the source of the reference.
Source: (StackOverflow)
I have a shared Google spreadsheet where several users have already filled out parts.
It would be useful to have alternately colored lines to facilitate correct placement of additional data.
Is there a painless method option to alternately color (say, the cells given that already many of them contain text?
In an empty document, there would always be the "color 2 lines, copy-paste 2 lines, copy-paste 4 lines, etc." option, but in the current document, there is already text and some lines are still liable to be deleted which necessitates a recoloring later.
Source: (StackOverflow)
In a Google Spreadsheet called Attendance there is a sheet called Template. The user duplicates this sheet, renames the sheet with current date and uses this sheet to mark attendance for students.
The Template sheet contains protected cells and the attendance is marked by entering Student's ID number in the space given (unprotected cells).
I use the following script to duplicate multiple sheets and rename them everyday:
function createDailyAttendance() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var refss = ss.getSheetByName("DataPointers");
// Get the range Row and Column information.
var dataRangeRow = refss.getRange("K2").getValue();
//var dataRangeCol = ss.getRangeByName(ColName).getValue();
// Get the range of cells that store Duplicate sheet name.
var AttendanceDataRange = refss.getRange(dataRangeRow);
var AttendanceObjects = AttendanceDataRange.getValues();
var template = ss.getSheetByName('Template');
for (var i=0; i < AttendanceObjects.length; i++) {
// Put the sheet you want to create in a variable
var sheet = ss.getSheetByName(AttendanceObjects[i]);
// Check if the sheet you want to create already exists. If so,
// log this and loop back. If not, create the new sheet.
if (sheet) {
Logger.log("Sheet " + AttendanceObjects[i] + "already exists");
} else {
template.copyTo(ss).setName(AttendanceObjects[i]);
}
}
return;
}
This script helps me create multiple copies of sheets from Template but the duplicate copies do not retain the Cell/Range permissions.
Is there a way to add a loop function which extracts permission from Template and applies it every time the loop template.copyTo creates a sheet?
Source: (StackOverflow)
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)