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

learn more… | top users | synonyms (2)

1
vote
1answer
14 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 ...
1
vote
0answers
19 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
19 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
20 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
votes
0answers
5 views

How do I fix this If/query to be able to sort data from two other cell with in the spreadsheet

=if(len(B1)=0,"Please choose a value in B1",query('Form Responses 1'!A2:F,"Select * Where F = """&B1&""" ")) I need to be able to select names and sort info. from these to cells A2:I, and ...
0
votes
1answer
12 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
11 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
19 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
24 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 ...
2
votes
0answers
9 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 ...
2
votes
1answer
31 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
23 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
16 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" ...
2
votes
0answers
25 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 ...
-1
votes
0answers
10 views

Multiple Timestamps in google spreadsheet [duplicate]

Im having the worst time ever trying to input timestamps in spreadsheet. I have basically a check sheet and each row has a subject to be inspected and stamped with the date and time the task was ...
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
13 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
9 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
10 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
13 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
votes
0answers
12 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 G column and write it to G1 cell. I plan ...
-1
votes
0answers
19 views

Sum 4 rows in every 5th column

The formula works in Excel but does not work in Google Doc. I want to sum columns G, M and S with 4 rows. Column A =sum(G1:G4,M1:M4,S1:S4)
2
votes
1answer
18 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
39 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
62 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
7 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 ...
-1
votes
0answers
12 views

Edit existing google sheet using google form

I have data in a google spreadsheet that I'd like to modify in forms. I'm working on a large scanning project and have a list of all file names and associated utility types in a google sheet. I'd ...
2
votes
2answers
21 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
32 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
18 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
94 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
23 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
58 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
27 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
38 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 ...
-2
votes
1answer
16 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.
2
votes
2answers
19 views

Sum all expenses of a given type given a list of expense types in a separate sheet

I use Google Sheets to keep track of my expenses. The file currently consists of: Sheet 1: "Expense Types" Sheet 2: "2015 Details". The first column of Sheet 1 contains the list of admissible ...
0
votes
1answer
21 views

Using Query with Importrange and comparison of dates

I have no experience with this, but I'm trying to set up a very extensive spreadsheet for work. I've got everything to work so far, but now I'm stuck. I created a Google form that employees complete ...
1
vote
0answers
9 views

How do do Conditional Formatting to compare cell contents between to Worksheet tabs [duplicate]

So, I've got two tabs in a Google Spreadsheet. I want to compare the contents and see which are different. Ideally, I'd like to use Conditional Formatting to format the cells that don't match. I.e., ...
3
votes
1answer
8 views

Set a revision point in Google Spreadsheets; or compare revisions between two arbitrary dates

Google Spreadheets's Revision History is very handy. But, I often do things like: Copy a Tab Edit it When finished, copy it over the original. I want to see only the changes made in step 2. But I ...
1
vote
1answer
11 views

Formatting cells based on upcoming date in Google sheets

I am trying to format a cell to be highlighted red if the following conditions are true: the future date associated with that row (which is in a separate cell) is less than 21 days from today, and ...
2
votes
1answer
36 views

Search Google Drive files directly from the Chrome search bar?

Is there a way (perhaps a Chrome extension) to show Google Drive search results in my Chrome autocomplete menu? For example in this image, if I had a Google Doc or Google Sheet called "orange juice" ...
2
votes
1answer
25 views

Highlighting duplicate data in rows

I have a query and I cant figure out why it's not correct. I have two lists of data and I want to highlight the matching data in the separate lists. Below is the query I have constructed ...
3
votes
1answer
35 views

Google Sheets notification triggered from function result

I have a google form that populates a spreadsheet with a timestamp and two numbers. For technical reasons I can't have the numbers passed directly into a database but they need to be copy-pasted ...
-1
votes
0answers
12 views

Import simple text into google sheets

I'm trying to import data from a website's API, the API outputs text and I want to import that text into a cell using Google Sheets. I've tried importXML and importHTML and neither seem to work. If ...
3
votes
1answer
22 views

Restore a Google Sheet

I was entering information on my Google Sheet and walked away. Then when I went to entered another number in a cell an error message came up and the entire sheet disappeared. There must be a way to ...