Tag Info

Hot answers tagged

4

Please try: =query(Data!A1:D7,"select * where B ='NO' ")


3

What you're looking for is conditional number formatting. You can do this in the Google doc by highlighting the column you wish to format and clicking: Format → Number → More Formats → Custom number format... Thereafter, specify this as your formula: [<10]_(###0.00_);_(#0_) What it means is, if the cell value is less than 10 then format the cell in ...


2

assuming you have the names in A2:A5 and the dates in B2:B5, try this formula: =ArrayFormula({transpose(split(join(char(9), rept(B2:B5&char(9),rows(B2:B5))),char(9))), transpose(split(rept(join(char(9),A2:A5)&char(9),rows(B2:B5)),char(9)))}) Example sheet with formula in D2 explanation: This formula consists of two parts, forming a literal array ...


2

Maybe this is what you need ? =IF(A4=2;1;IF(A4=4;7;A4)) or, depending on your locale: =IF(A4=2, 1, IF(A4=4, 7, A4))


2

You can use this formula. Formula =ARRAYFORMULA(IF(ISBLANK(A2:A),"",A2:A + 7)) Screenshot Example I've created an example file for you: new date from entered date


2

Please try: =Query(Data!A6:L1397,"Select A,B,C,D,E,F,G,H,I,J,K,L where A>=date '"&TEXT(A1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(B1,"yyyy-mm-dd")&"'") Note Column references for the source data set and that the likes of date '"&TEXT(A1,"yyyy-mm-dd")&"' are to accommodate the possible use of =today(). May be worth mention ...


2

This is what you need to do. Formula =QUERY(A1:C2, "SELECT * OFFSET 1", 0) Explained The SELECT statement contains an extra OFFSET argument. It tells the query to skip one row. The third parameter of the QUERY formula controls the headers. Setting it to zero will ignore the headers.


2

This is your key: 11jp7-1POyw_cunE1imQ80Bqxj8U93a4hvHmKpWLY5K8 As @Punchlinern mentioned in his comments, with the new Google Sheets you can use the entire URL in combination with the IMPORTRANGE() formula. Using the above-mentioned key, the old style, works as well. Reference: IMPORTRANGE


1

I think mostly so that they can act like bookmarks, only better. I have a Google Docs icon on my desktop that will launch Chrome and go straight to my documents. Sure, I could drop a bookmark there just as easily, but by default it'll have the logo of my default web browser. Sure, I can change it, but it's a hassle. Also, since it's a "Chrome app", it can do ...


1

See if this helps ? function onEdit(e) { var s = e.source.getActiveSheet(), watchCols = [2, 5], offsetCol = [8, 10], ind = watchCols.indexOf(e.range.columnStart) if (s.getName() !== "Sheet1" || ind === -1) return; e.range.offset(0, offsetCol[ind]) .setValue(!e.value ? null : Utilities.formatDate(new Date(), "GMT", "hh:mm")) }


1

If your 0:01:00 is a date/time index (numeric rather than a string) the underlying number is an integer for days and a decimal for time. as there are 24*60*60 seconds in a day the number of seconds represented by 0:01:00 is effectively the number of seconds between midnight and one minute past midnight, which for A1 can be calculated as: =86400*A1


1

First, the error: shs is an array, since you populate it with ss.getSheets(), so it doesn't have a method getActiveCell(). However, the elements of the array has that method. You are also referencing a variable sheet, which you haven't defined anywhere. Currently, your code is more suitable for going through a single sheet, you need to iterate through the ...


1

Use: =ARRAYFORMULA(IFERROR(D:D-C:C, "error"))


1

The IMPORTRANGE function does not seem to cache its results, so it will refresh whenever the source spreadsheet changes. But we can circumvent this by writing our own function: function customImportRange(spreadsheetUrl, rangeStr, timestamp) { var sheet = SpreadsheetApp.openByUrl(spreadsheetUrl); var range = sheet.getRange(rangeStr); return ...


1

Try: =mod(C2-B2,1) and drag down as far as needed. Make sure the format the cells as duration. For the 'total time' a simple sum should suffice.


1

Have you tried the .toDateString() method ? See what this gives you: var start = row[5]; var dateStr = start.toDateString(); Logger.log(dateStr)


1

You might try: =CHOOSE(RANDBETWEEN(1,2),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122)))&CHOOSE(RANDBETWEEN(1,2),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(97,122))) strung together with & three more times.


1

You can set up an onEdit trigger that will log the edit date and other details in another sheet inside the same spreadsheet. function onEdit(e){ var range = e.range; range.setNote('Last modified: ' + new Date()); }


1

It seemed simpler to put the conditions in a range (named TestR) and then use: =if(or(iserror(match(C3,TestR,0)),A3<>"X"),"No","Yes") I have opted for a shorter formula, mainly by hiving off the four ‘list’ items into a named range. Rather than mix AND and OR I opted for just OR by testing for all the possibilities that should give rise to a ...


1

Assuming you meant 'selecting from a certain cell from all sheets' (in one spreadsheet), custom function written in GAS that does that could look like this: function avgCellAllSheets(cell) { var arr = []; SpreadsheetApp.getActive() .getSheets() .forEach(function (s) { arr.push(s.getRange(cell) .getValue()); }); return ...


1

You would need a google apps script for that. Something like this should work: function onOpen() { SpreadsheetApp.getUi() .createMenu('Custom Menu') .addItem('Select Range', 'selectRange') .addToUi(); } function selectRange() { var ui = SpreadsheetApp.getUi(); var sheet = SpreadsheetApp.getActiveSheet(); var response = ui.prompt('Enter the ...


1

I know this is an old topic, but for the number of unique articles, this might work: =ArrayFormula(counta(unique(filter(A2:A&B2:B, len(A2:A&B2:B))))) I also tried to work out some formulas for the other questions: Hope this helps ?


1

Here is a Custom Function code snippet to get a spreadsheet name by it's Identifier. function GetSpreadsheetNameById(id) { var ss = SpreadsheetApp.openById(id); return ss.getName(); } In a cell, pass a sheet Identifier (look in the spreadsheet URL for the identfier). =GetSpreadsheetNameById('49nvkwOE-203nXDLGGJtg0htFKK020dsd939AAPZU')


1

This is a derivative of the other answers but it might be useful for future users. function onEdit(e) { var sheet = SpreadsheetApp.getActiveSheet(); var row = SpreadsheetApp.getActiveSheet().getActiveCell().getRow(); var bugCount = sheet.getRange("M2").getValue(); bugCount++; if (sheet.getRange(row, 1).getValue() == "") { ...



Only top voted, non community-wiki answers of a minimum length are eligible