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

learn more… | top users | synonyms (2)

38
votes
17answers
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 ...
0
votes
0answers
14 views

Intermittent Google service connection failures after power outage

This is something of a follow-up to my other question, Gmail chat not working after power outage. This is a short summary of what happened there: Computer turned off without warning because power ...
1
vote
1answer
5 views

Importrange All Sheets

Is there anyway to call an IMPORTRANGE() and have it include all sheets? Here is my current function that combines the current data with the first archive, I'd like to set it up as a failsafe for the ...
0
votes
1answer
14 views

How to set up email notifications to specific accounts when a field is changed?

If a field is is changed in a row I need an email to be sent to the email that corresponds with the row. For instance say in a row the first field is 1234@gmail.com, the second is a description field ...
0
votes
1answer
8 views

Conditional Formatting Referencing another Cell (and using addition)

I am tracking high school credit acquisition. Each trimester a student must earn 2 credits to remain on track. If Cell A1 is "trimester 1 total credits", and cell B1 is "trimester 2 total credits", I ...
1
vote
1answer
18 views

Transposing Column to Row

I am trying to write a script that will transpose a column from one sheet into a row on another sheet. I have already figured out how to do the copy, but I need to figure out how to change the data ...
1
vote
1answer
26 views

Script to delete a dynamic range of rows

I'm looking for a script that allows me to delete a dynamic range of rows. Basically I have a script that adds rows to the bottom of a list but I want another script that will delete all rows in the ...
0
votes
1answer
41 views

Copy a row and add to the bottom of a list

I'm looking for script that will allow me to copy a specific row and paste it to the bottom of a list. The bottom of the list will not be the last row on the sheet. Using VBA I would have used an ...
-1
votes
0answers
9 views

Google Spreadsheet/Excel formula for calculating shipping expenses of my orders [on hold]

I have Website form that is entering data in Google Spreadsheet and I need to calculate automatically by Google Spreadsheet formula Shipping Cost for all orders. Data consists of: Name Email Online ...
3
votes
1answer
125 views

Problem: Script does not trigger on FormSubmit: Removing Duplicates in Google Sheet of Form Responses based on a column

I am trying to remove older duplicate form responses based on a column using the following code. The credit for the code goes to: http://www.jacorre.com/tutorial/remove-duplicate-rows-google-...
2
votes
2answers
16 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
35 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
9 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
71 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
11 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
8 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
27 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
14 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
19 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
17 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
39 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
24 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
10 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
15 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
8 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
12 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
39k 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
16 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
171 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
10 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
88 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 ...