A productivity application from Google that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

2
votes
2answers
14 views

Update the reference in Indirect() when new columns are added

I have a a very large spreadsheet that pulls data from another logsheet through an Indirect depending on user input on cell A1. =INDIRECT($A$1&" Data!$AG$3") I'm trying to add some new columns ...
2
votes
1answer
34 views

Selecting a single column from all rows for chart range

I want to specify a certain column from all rows (omitting the first few) to be the range for my chart. Currently I input this to specify the range: L6:L (note: this automatically changes to L6:L20 ...
0
votes
0answers
2 views

Moving Column in Google Sheets

I am trying to create a script in google sheets that will go through each column and if there is data, copy it to a different sheet and then check the next column for data. If there is no data I want ...
1
vote
2answers
34 views

JavaScript To Dynamically & Daily Select The Next Worksheet In An Open Google Sheets File

At work using Chrome, I have a Google Sheets file saved on my Google Drive folder that's shared with me with edit privileges that I keep open on my MacBook Pro. After I've originally opened the file ...
2
votes
1answer
70 views

Change logs for Google Spreadsheets

We've got a large shared spreadsheet which is a chart showing user permissions. It's writable by several people. Although Google Drive allows you to roll back the changes simply its interface isn't ...
0
votes
0answers
9 views

Google Sheet: How to make 'fixed row' cells a sort button for whole column?

Basically i have fixed column with column headings like Name | Email | Profession | Age | Gender I want to be able to click name, age, etc and have all rows sorted based on that column in alphabetic ...
0
votes
1answer
4 views

How to copy the contents of a single cell from one Google spreadsheet to another Google Speadsheet?

I am trying to copy the content of one cell of a Google speadsheet to another cell in a different Google spreadsheet. I have tried the formula (=Exportrange("spreadsheet-key", "Sheet-name! range")) ...
-1
votes
0answers
17 views
+100

Tally duration, client, client category in each Google sheets Timesheet

I use Google Sheets for my time sheets, using the following folder structure: Timesheets Corporate CorpClient1 Timesheet (Drive Sheet) CorpClient2 Timesheet (Drive Sheet) Freelance ...
2
votes
1answer
12 views

Google sheets recently changed American datetime format, how do I change it back?

Google sheets recently changed American datetime format, how do I change it back? It used to default to using "MM/dd/yyyy" but as of a couple weeks ago now when I enter a date it defaults to "MM/dd" ...
1
vote
1answer
45 views

How can I automatically set “sheets last updated” in a cell on Google Docs Spreadsheets?

Simple task!.. output a date and time every time something in the sheet is edited! function onEdit() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets("Data"); var ...
1
vote
1answer
18 views

If a string is contained in a row return return the value of the first cell of the row

I've this Google Spreadsheet: https://docs.google.com/spreadsheets/d/1-QV49oKCf58HgLBgC8gJ3gc6IXJZAo2xvh6vPTjY05o/edit?usp=sharing check the row in Sheet1 where the domain in Sheet2 is contained ...
1
vote
1answer
16 views

Paste value only without formula for hyperlinks

Consider the following: A | B | C ------------------ | -------- | ------------------- =HYPERLINK(C2, B2) | Google | http://google.com =HYPERLINK(C3, B3) | Facebook | http://...
1
vote
1answer
27 views

How do I copy data if cell contains text?

Basically, I have a book which gets data anytime someone fills a form. There are five groups I need the data divided into. If a cell contains one of the five answers all the answers by user need to ...
5
votes
1answer
36 views

Can I add the URL from a Facebook status update to a Google Spreadsheet with IFTTT

Would it be possible to save the actual URL where you can find the individual post to a new row in a Google Spreadsheet from Facebook to Google Drive Spreadsheets? (You can get to the FB status URL ...
2
votes
1answer
22 views

Create a sparkline for the delta between each column of data

Lets say I have data like so... A B C D E 50 100 125 175 225 I want to create a sparkline that will show the difference between each column SPARKLINE( [B-A, C-B, D-C, E-D])...
1
vote
1answer
14 views

Filter last result before a specific date

I've got the following formula working. However, I need only the last result, but at the moment it is returning more than one result. =IFERROR(FILTER(MAX(FORM!$Q$3:$Q,FORM!$N$3:$N<$A$1,FORM!$O$3:$...
2
votes
1answer
9 views

Getting the highest values for each day

I have a bunch of raw data in a spreadsheet - dozens of temperature readings a day. My ultimate goal is to graph the highs and lows of each day, filtering out all the other readings. So my data ...
5
votes
3answers
1k views

How to move to the last active row in a Google Spreadsheets

I am using a Google Form to insert data into a Google Spreadsheet. Is there a shortcut to move to the last row where data is entered?
2
votes
1answer
13 views

Lookup formula returns an incorrect answer

I've written a Google Sheets formula that looks like this: =lookup(I6, {"First", "Second", "Third", "Forth", "Fifth"}, {((D6-E6)*H6), (0.43*H6), ((D6-0.25-E6)*H6), ((D6-E6)*H6), ((D6-E6)*H6)}) ...
1
vote
1answer
6 views

Conditional formatting for a single cell based on a row being empty or containing any values

I want A1 to go green if any cells in range B1:J1 contain any values. A1 to remain blank if B1:J1 contain no values. In the sheet all values will be text based but will vary from cell to cell.
4
votes
4answers
2k views

ImportRange with preserved formatting in Google Sheets

I am importing a certain range of data from a master sheet and would like to maintain the formatting for those cells. Cell reference with colour formatting This would be the perfect option, except I ...
-2
votes
0answers
15 views

Google Sheets Android App [migrated]

I have been trying to incorporate conditional formatting to change a negative number in a cell to red. I am not seeing the conditional formatting panels as described in several different sites. If I ...
2
votes
2answers
28 views

Search for a value in all Google Spreadsheets at once

I have hundreds of spreadsheets in Google Drive and I am trying to find a value in one of the files. For example, I need to find all sheets that have a $1000 value, but I don't want to open each one ...
-1
votes
0answers
10 views

How to include Split function in Query

I have asked a similar question here: https://productforums.google.com/forum/#!topic/docs/TO80Cpz_agQ;context-place=mydiscussions I managed to work a different solution answered in that post. I ...
0
votes
2answers
46 views

How to sum cells in Google sheets that contain numbers and text

This has already been addressed in Excel but I was wondering how to do the same in Google Sheets. So suppose the B cell has this formula: ="frontend (days) total: "&sum(B2,B10,B14,B20) and the ...
0
votes
0answers
11 views

Capture the time of issue resolution [duplicate]

I need help in understanding how to capture a timestamp of a particular entry in the google spreadsheet. My team is working on customer issues and I would like to capture the time of issue ...
12
votes
3answers
38k views

How do I lookup a value from another sheet in Google Docs?

I have a page which looks like this: ╔═══╦════════╦══════╦═══╗ ║ A ║ B ║ C ║ D ║ ╠═══╬════════╬══════╬═══╣ ║ 1 ║ User A ║ 144 ║ ║ ║ 2 ║ User B ║ 5478 ║ ║ ║ 3 ║ User A ║ 2156 ║ ║ ╚═══╩══...
18
votes
8answers
19k views

How to pass a range into a custom function in Google Spreadsheets?

I want to create a function which takes in a range... something like: function myFunction(range) { var firstColumn = range.getColumn(); // loop over the range } A cell would reference it using: ...
1
vote
1answer
14 views

Get the latest specific date prior to today

I am using the Google Analytics Add-On for Google Spreadsheets, and need to get data for the financial year to date. I have the finish date set as: =TODAY() Which is working fine. Now I need to ...
1
vote
1answer
24 views

Combining columns horizontally with an arrayformula

I need to join columns with a formula (not a script) in Google Sheets in a different way than others I've found--here is my original data, supposing that the Avengers train by playing racquetball: ...
0
votes
1answer
13 views

Restrict the range of a script that replaces input data to a certain range

There is this question regarding adding checkbox functionality to Google Drive: Checkbox function to automatically add to total. And a great answer is: In the Google Sheets spreadsheet go to Tools ...
2
votes
1answer
169 views

Remove header and blank row from the results of a SUM, GROUP BY query

I have a two column table where one column is some sort of key where duplicates are allowed and the other column includes amounts. I then have the following formula somewhere else on the sheet: =...
3
votes
2answers
70 views

Coloring cells according to the value to which its content is closest

I am trying to make a guide for my dragons on a game I play but I'm not quite sure how to code what I am trying to do. Link ex. if has values "Guardian"(C2:C100) and "Dragoness"(I2:I100) in its row ...
3
votes
2answers
40 views

How can I sort in Google Sheets by cells containing section (or version) numbers?

I'm indexing a document with sections and sub-sections. I want to have one column representing the section-number and be able to sort by this column. However when the sub-section gets higher than 10, ...
1
vote
1answer
9 views

How to update row in Spreadsheet based on respective cell value?

I'm about 10 days new to coding in GScripts/Spreadsheets and I've been struggling to write a Spreadsheet function that will change the values of certain cells in certain rows depending on the value of ...
6
votes
1answer
1k views

Convert all text to UPPERCASE in a Google Spreadsheet

I have a spreadsheet I'm sharing with others to collect some data. Each person is just (supposed to be) putting a single character in any of a range of cells. How can I convert those cells to convert ...
3
votes
1answer
53 views

Highlight/reject cells that contain any of the values from another sheet

I have a sheet/tab that contains nothing but a single column where each row is a word, let's call it wordlist. In another sheet/tab, I would like to make it so that for one of the columns, if the ...
2
votes
1answer
85 views

How to show instead of hide repeated values in pivot table?

By standard, repeated values in pivot table are only shown once. I made an example sheet to illustrate: This becomes a problem for people who use filters over pivot tables: Notice how Donald's ...
0
votes
1answer
16 views

Compare date value to current date in Google Sheets

How can I compare the date in a row to a current date, so that if it's greater than today the next steps in my process need to be executed?
17
votes
3answers
13k views

How to reverse the order of selected rows?

Is there an easy way to reverse the order of selected rows? (Or how about all rows, if that's easier.) Specifically, in this case, there is no column that could be used for sorting to get them in ...
3
votes
1answer
25 views

Conditional formatting based on data “age”

I have a sheet of figures, numbers as values. I was wondering if there was a way to have a cell automatically change colors based upon either: Change colors based upon how long it has been since the ...
0
votes
1answer
13 views

Generating multiple random numbers in a single cell

I am attempting to create a sheet that reads a cell and generates a set of random numbers based on that cell's length I have a long list of one to four random numbers, in the format below. 4 2 2 0 4 ...
1
vote
1answer
22 views

Align the rows of one sheet with the rows of an imported sheet

I have a spreadsheet of employee names. I am using =IMPORTRANGE() to import the names into another spreadsheet that tracks expenses in rows next each of the imported employee names. The problem is ...
38
votes
16answers
51k views

In a Google Spreadsheet, how can I force a row to be a certain height?

In a Google Doc spreadsheet I would like to have each row the same height no matter how much text is in each cell. I have tried "resize row" but no matter how many pixels I put in, the row expands ...
1
vote
1answer
14 views

How can I change the value of one cell in Google sheets based on what is entered into another?

Using Google Sheets, I have a dropdown list of different actions, and each action has a different score associated with it. Eg. Action a = 5 points, Action b = 7 points etc How I can make Google ...
6
votes
1answer
70 views

Can I jump to next cell containing invalid data in Google Sheets?

I have a Google sheet containing lots of data, and some of it doesn't respect the data validation rules (and the little red triangle appears). Since I have a lot of data, it's quite a long process to ...
1
vote
1answer
24 views

Update latest spend on Google Sheets based on Date dimension

I have a spreadsheet report -- used to monitor our marketing campaign reports. So my goal here is to be able to update a cell showing the our current spending as of yesterday. How can I go about ...
0
votes
1answer
23 views

How to allow users to enter some data in Google Sheet without actually modifying the content?

I'd like to make a sort of calculator based on a grid, where the user checks different lines/columns and it will calculate some stuff based on what's checked. Perhaps I could do something in HTML/...