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

learn more… | top users | synonyms (2)

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
23 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
18 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
33 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
26 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
31 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
20 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
116 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
26 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
56 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
27 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
35 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
23 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
12 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
39 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
33 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
27 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
42 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
32 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
29 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
15 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
15 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
18 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
47 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
69 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
25 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
34 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
23 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
209 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 = ...
2
votes
0answers
14 views

Is it possible to reference a cell above with an ArrayFormula? [duplicate]

Here is an example situation which I would like to achieve using ArrayFormula: Column A is the data, column B is the calculation. Here is an implementation without ArrayFormula: Here is how I ...
1
vote
1answer
29 views

Cross referencing 2 sheets and pulling in values in Google Sheets

I have a Google sheet with multiple sheets in a single document. A generalized version of what I have is something like this: Sheet 1: A B C | AA | | | | BB | | | ...
6
votes
1answer
62 views

Why does Google Spreadsheets says Zero is not equals Zero?

After spending hours trying to figure out an error in my personal accounting spreadsheet, I could pin it up to one formula. While this results in Zero =((0,6-0,3-0,4)+(-0,3+0,8-0,4)) comparing it ...
3
votes
1answer
38 views

Is there a way to stripe rows based on change of date in Google Spreadsheets

I would like to not zebra stripe a spreadsheet in Google but to have the sections change color and alternate between dates. So 7/1/2015, even if there are 5 rows dated this would be color A, 7/2/2015 ...
2
votes
2answers
40 views

Prevent “invoked too many times per second” error in Google Apps script

I need a number to be converted to a name. In B1:B1000 I have numbers. In C1:C1000 I has function name. On writing C30 got an error: invoked too many times per second ... how do I do function on range ...
-1
votes
1answer
21 views

Restrict the valid values in one cell, based on the value in another cells [closed]

I am trying to prevent a date being entered into a cell if the date precedes the date in another cell.