One of productivity apps in Google Drive, that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

1
vote
1answer
23 views

Display most recent comments from column connected to date and username in Google Sheets

In Google Sheets I have three columns: Timestamp, Username and Comment. How can I search a column for the most recent comment of a username and overwrite the most recent comment, in another cell? ...
0
votes
0answers
22 views

Set color if cell value is element of another sheets row [duplicate]

I am trying to figure this out since a while now. I have got it working if everything is in the same sheet! It goes like this: Select row K -> conditional formatting -> Custom formula is ...
0
votes
0answers
14 views

Google Sheet Cell to have Clock to Pick the Time

How do I make a cell show a clock to pick / click on a time from a clock face to then add that time to a cell without having to type the time value into the cell.
3
votes
1answer
30 views

How does one remove a new line in a cell in a Google Spreadsheet?

I frequently have a problem when entering a row of data that one or more cells will get a spurious newline at the end of their data, and that row will become double-tall. Sometimes I can guess the ...
6
votes
2answers
237 views

How can I find duplicates in a column, using formulas?

Has anybody come up with a formula to find duplicates in a column? For example: a b c d b d =somefunction(A1:A6) Output: b d I know there is a =unique() function, it's basically a ...
0
votes
0answers
8 views

How to detect previously deleted Google Calendar events

Per this article, Google Calendar events are not really immediately deleted. Therefore, previously deleted events are successfully returned by the methodcalendar.getEventSeriesById(eventId) I need ...
3
votes
1answer
41 views

Course allocation with array formula in Google spreadsheet using constraints

I try to assign students to courses based on the student's preference for each course and course capacity. Students are ranked based previous performance. In the current implementation, the data is ...
5
votes
1answer
46 views

Enlarge label area in Google Spreadsheets

Is there any way to enlarge the label area in Google Spreadsheets? E.g. see red rectangle: I would like to increase the label area so that the label text don't get cropped.
1
vote
1answer
27 views

Add date stamp when new row added by Zapier

I am trying to add a submission date to column 1 whenever a new row is automatically added through Zapier. Is this possible? E.g., C1 C2 C3 (DATE STAMP ...
2
votes
2answers
24 views

onOpen doesn't execute

I've got a google spreadsheet I created yesterday and used Tools -> Script Editor to create a function in a single file called Code.gs function onOpen(e) { var email = ...
1
vote
1answer
19 views

Get unique rows and their count in Spreadsheet

I have a sheet as below, | A | B | C | ------------- |abc|123|Yes| |jkl|123|No | |abc|123|Yes| |abc|567|Yes| I want to get the output like, | A | B | C |Count| ------------------- |abc|123|Yes| 2 ...
1
vote
2answers
31 views

Make a summary admin document

I trying to make an admin document where all rows from other documents are added automatically. Example: Person A is typing data into this document ...
1
vote
1answer
41 views

Filter Google Spreadsheet Array Formula

I have a spreadsheet with 2 columns. B1 contains a custom function that returns an array that overflows to fill the column. I want to create a few filter views that each show all entries with 1 given ...
1
vote
0answers
27 views

Google Spreadsheets Loading Bug

A few days ago, I created a Google Spreadsheet and from what I remember, it was working fine. When I came back to it today, I saw that a few cells said Loading... despite all cells in the column have ...
0
votes
1answer
26 views

Daily spend vs projection spend pacing (%)

I have a google doc sheet which currently has all our accounts spends.There are two tabs.The first tab details our actual daily spend for each account for each day of the month.The other tab is a ...
2
votes
1answer
32 views

Changing colour of row if a certain aspect is met

I have a list of clients that send in certain information to me and I have setup columns to enter a tick using (=char(10004)) on R,S,T,U,V,W,X & Y columns and I need to colour them to go green ...
0
votes
0answers
21 views

How can I plot date vs time in Google sheets?

I have a Google spreadsheets with two columns. Column A contains dates and is formatted as date. Column B contains times and is formatted as time. I want to make a chart showing the dates on the ...
0
votes
1answer
29 views

How to Run a Query in GoogleSheets for New 26-Week Highs Using GOOGLEFINANCE Function

I'm looking to run a query in GoogleSheets using the GOOGLEFINANCE function to pull down a list of stocks reaching new 26-week highs (limited to US companies or perhaps more simply US stock markets). ...
2
votes
1answer
120 views

How to write this Google Spreadsheet percentage calculation function?

I track my diet (macros) in Google Spreadsheets. For 1 day my protein is 114.63 carbs 194.75 and fat 69.15, the 70.54 is sugar which I'm not sure I should include. The total of all 3 is 378.53 ...
2
votes
1answer
27 views

How to Create a Google Sheets Script for Reporting 26-Week New Highs using GOOGLEFINANCE Function?

I'm looking to use GoogleSheets to display ticker symbols and prices in real-time of stocks that have created new 26-week highs, i.e. today's highest trading price is greater than any trading price ...
1
vote
1answer
17 views

How to select all cells between two cells, where more may be inserted later?

I am trying to count all the cells that have information between two cells that are in the same column. This is easy enough to do using =countif(cell1:cell2, "<>") the problem is that I may ...
2
votes
1answer
62 views

How to automatically refresh a filter view in Google Sheets when new data comes in via Google Forms?

I have a filter view in Google Sheets that does not refresh when new data comes in via Google Forms. To refresh the filter view, I can remove the filter and apply it again, or even just click "OK" in ...
2
votes
1answer
28 views

“Clean” join() without excess delimiters?

I'd like a function in Google Sheets to concatenate elements with a chosen delimiter like join(), but without excess delimiters at beginning, end, and between null elements. I accomplished this with ...
2
votes
1answer
36 views

Clamp Value in Google Sheets

I'm trying to clamp a value between a minimum and maximum value. If this were a formula, I'd expect it's usage to be =CLAMP(value, min, max). From what I know, there's two approaches, nether of ...
1
vote
1answer
24 views

If a name matches a name in another sheet, then print the cell next to it

If sheet 1 has company names (and duplicates of those names) company A company B company A company A the other sheet has all those company names and their URLs (no duplicates) company A - url ...
1
vote
1answer
13 views

How do I change the year in Google spreadsheets

I'm copying and pasting things over from an old spreadsheet and the original dates where marked like '25/4' so dd/mm but I never included the year, well the problem is these where dated in 2014 but ...
3
votes
1answer
40 views

Automatically add rows daily and append previous day's data

I currently have a small database that gets data dumped into it manually every day that looks like this. Each day the data below will be dumped (just with updated date in column A) On another ...
1
vote
0answers
34 views

Dropdown menus drop-off

I have a Google Sheet application (url below) that uses dropdown menus to allow selection of data to display (one line selected from 5000). It works quite well BUT once the file is shared, for some ...
3
votes
1answer
29 views

Populate a Google slide automatically via Google spreadsheet

Is there any way to populate a Google slide automatically with some other workbook like Google spreadsheet. (e.g. like by giving range reference in a Google slide). If someone knows the other way to ...
3
votes
1answer
43 views

How to delete a group of cells and shift the rest up?

In MS Excel, it is possible to select any rectangular range of cells and delete it in such a way that the cells below the deleted range move up to fill up its place. This feature is helpful, e.g., ...
1
vote
2answers
36 views

Batch Google search or search inside Google sheet

I have a list of cities that I want coordinates for. I've been typing in the city in Google Search (in the format: city name coordinates—for example Recife coordinates) and then pasting the result ...
1
vote
1answer
34 views

Send email If one cell is greater than or equal to another

For some reason this script is not working and I was wondering if someone may guide me on fixing this? In my Google Spreadsheets, I just want the value in cell A2 to send an email if it's greater ...
2
votes
1answer
45 views

Formula to show text based on numbers

I am looking for a short way to type this: =if(a1=1,"sometext",if(a1=2,"othertext",if(a1=3,"thirdtext"))) In JavaScript you would do something similar like this: switch(a1) case 1: "sometext" ...
1
vote
0answers
40 views

Google sheets query returns “loading…”

I've put together a web application that relies on a series of Google components. Google Forms collects data from users Google sheets saves the data (automatically) Additional "calculated" columns ...
2
votes
1answer
16 views

Formula counting based on two conditions

I am struggling with a formula in Google Spreadsheet. Here is what I want to achieve: If a column in a book is not null and if another column in another book is having a specific value, then counting ...
1
vote
1answer
17 views

Google sheets average between cells

I want to fill in the blanks between these cells with the average difference: i.e., because there are 11 blank cells between 0.234 and 0.334 the first blank should be 0.234 + ((0.334 - 0.234) / 11). ...
1
vote
1answer
12 views

Chart usage per interval from periodic readings

I have a series of monotonically increasing values associated with arbitrary dates. In this case, they're meter readings, but could be other values like mileage, fuel usage, etc. 29/05/2015 844.79 ...
2
votes
0answers
12 views

Toggle named range window with a button?

I would like to know if it would be possible to toggle the different features in sheets with a script. What I am trying to do is to basically create a button that will show the Named Ranges side ...
2
votes
1answer
22 views

How do I use a negative DURATION formatted cell from Google Spreadsheets in Google Scripts?

I'm using Google Spreadsheets and I have a sheet in the following format: Start | End | Partial result | Expected | Total 08:00 | 12:00 | 04:00 | 05:00 | -01:00 14:00 | 18:00 | ...
1
vote
1answer
19 views

Google spreadsheet count words

I have spreadsheet: https://docs.google.com/spreadsheets/d/19GrXAFLpv6jz9OzDum-fyfP_m-RZFCNrQ0rr1vd5bYI/edit?usp=sharing I want to count percent of wins(W) in C column and write it to C1 cell. I plan ...
2
votes
1answer
36 views

Extracting GICS codes from Fidelity website

This is the website: https://eresearch.fidelity.com/eresearch/goto/evaluate/snapshot.jhtml?symbols=aapl&type=o-NavBar I'm trying to pull out the following two pieces of data into a Google Sheet: ...
2
votes
1answer
51 views

Convert one column of multicolumn output REGEXREPLACE from string to number

In an expression of the form (one column in, two out): =ARRAYFORMULA(REGEXREPLACE(A1:A100,...,{"$1","$2"})) How can I convert just one of the output columns from text to a number (like VALUE)? The ...
0
votes
0answers
14 views

Formula for SUM from multiple sheets and tabs within each sheet [duplicate]

I have a Google Sheet named titanstatsheet2015 and one named titanstatsheet2016. in each of those Google Sheets I have tabs (sheets) named "Cumulative". I want to sum Cell C4 from each of those tabs ...
3
votes
2answers
70 views

Can you do a UNION SELECT in a Google Sheet?

I've got a Google Sheet linked to a form which collects replies from three different people on the same subject using the same form and editing three times. This creates a sheet which contains three ...
2
votes
2answers
36 views

Format of spreadsheet chart not kept when exporting inside my GoogleApp

I built a Table Chart in Google Sheets, and all numerical values except for the last column are displayed as percentages, which is a behaviour I need and want. View of my sheet (sample of the Table ...
1
vote
0answers
9 views

Drag to change referenced cell in Google Sheets as Libre Office Calc does?

In LOC when double clicking cell A1 that has the formula '=B1*C1', both B1 and C1 have a coloured border that is drag-able. Dragging B1's border to D1 now makes the formula in A1 '=D1*C1'. In Sheets ...
2
votes
2answers
26 views

How to output data from Column A and Column B to Column C automatically in Google Sheet

When I insert data in column A and Column B, It should be automatically merged and added in column C. A | B | C =================== ABC CDE ABCCDE
1
vote
0answers
35 views

Counta Function Counting One Extra Filled In Line

I am creating a register for some workshops to introduce people's personal information, organized by when these workshops are taking place. I have six different spreadsheets (Monday, Tuesday, ...
1
vote
0answers
24 views

Using drop down menus in Google Spreadsheets to display additional information. In this case scheduling

Quick Overview: I am trying to create a simple easy way to look at a master schedule for a complete year. Each Event has a time, place and coordinator. Sheet 2 would be the master schedule for the ...
3
votes
2answers
251 views

Create a popup in Google Spreadsheet

How can I create a popup in Google Sheets using script editor? I'd like to show a popup with a personalised notification. It should start like this: function onOpen() { var ss = ...