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

learn more… | top users | synonyms (2)

1
vote
0answers
57 views

How to save a Google Sheets chart as an SVG or EPS?

By default, Google Sheets only lets you save charts as PNGs. This isn't good enough when I need high-quality images, or images that are editable after export. This quick tutorial provides guidance on ...
2
votes
1answer
51 views

Can I set a trigger to run every second?

While searching for answer to How can I trigger a function when switching sheets within a spreadsheet? I noticed that the Triggers for current project dialog has an option for specifying a trigger ...
3
votes
2answers
94 views

How can I trigger a function when switching sheets within a spreadsheet?

How can I trigger a function when switching sheets? Let's say I'm viewing a multi-sheet spreadsheet. Now I want a function to run whenever I switch from one sheet (tab) to another. Is that possible? ...
2
votes
2answers
28 views

Formula to use to add up all rows that returned a value of 1 for several cells that meet a specific crtieria

This is the spreadsheet I need help with: spreadsheet I need cell B21 in the 'Reports' sheet to add up all rows that meet this criteria: return a value of 1 if columns M, O, T, U, W, X, Z, AA, AB, AC ...
0
votes
0answers
28 views

Set a send email account in Google spreadsheet

I have 3 email accounts, two gmail (a "corporate account" and a work account) and one personal account not via gmail. We collect requests in a google spreadsheet, where I want to add a hot mailto ...
3
votes
2answers
36 views

How to make Google Sheets autowrite value into table

I would like to place some values in a table, given their row and column coordinates. Do I have to use a script or can I use a function? On the image I swapped the x and y axis but I think you get ...
1
vote
1answer
56 views

arrayformula (or other to copy down formula in adjacent cell to last row with data?

I have a spreadsheet in Google Sheets in which the formulas in E2 and F2 need to be copied down to the last row that has data. Here are rows 1 and 2: Name of Teacher Date Absent During Start ...
0
votes
1answer
39 views

Updating a column of cells with a maximum value from Google Finance

function getHigh() { //to update the highest value of a stock price at market close, used to calculate a trailing stop price var sh = SpreadsheetApp.getActiveSheet(); //column E is stock price ...
1
vote
1answer
63 views

Filter View, Text Starts With in Custom Formula

In Google docs (spreadsheet) under Filter View, I am trying to write a custom formula for multiple conditions. For example, in column O, I may have "Jury Room" and also "Jury Rooms" and so I am ...
2
votes
1answer
22 views

Combine rows to a column in Google Spreadsheets

I have a question about combining rows to a column in Google Spreadsheets. There are a lot of threads already about this topic, but I couldn't find one about this particular problem. This is the data ...
1
vote
1answer
20 views

How do I pull information from google sheets based on a date range?

I am using google sheets to collect information about patients in one of our programs. I want to be able to pull a list every week of the patients I need to follow up on. The new spread sheet needs to ...
1
vote
1answer
36 views

Formula to use to return a value of 1 for several cells that meet a certain crtieria

Here's the spreadsheet I need help with: https://docs.google.com/spreadsheets/d/1DGYgprXe7V7WH2HII0hvQDWXl1SaG_a4MUZ5HDGhSq8/edit#gid=258610801 I basically need cell B21 in the "Report" worksheet to ...
2
votes
1answer
24 views

Google Sheets formula for weekly column totals

I'm a bit of a newbie to Google Sheets formulas and such. I have a set of data entered daily with the dates converted by WEEKNUM() to display and allow me to filter by week no. I want to add the ...
0
votes
1answer
27 views

Grabbing every data from every sixth cell in a row for a sparkline

I am creating a spreadsheet in Google Sheets where every 6th column is a "total" column. I need to create a sparkline that will grab the value of every 6th cell in that row.
1
vote
2answers
21 views

How to concatenate date value with a string?

I need to create the following value: // "ABC" + current date + some random number ABC20160405123 I do as follows: =CONCATENATE("ABC", NOW(), 123); But instead of getting expected value I get ...
1
vote
1answer
27 views

Timestamp code for Google Sheets

I am using this script in order to put a time stamp in column B when someone edits in a row. However, I need it to place the time stamp if someone edits a row in column A. I don't need it to place a ...
3
votes
1answer
23 views

Normal Text in Google Sheets is absent. How to enable?

Where has Google hidden Normal Text in Google Sheets? It is present in Google Docs but is absent in Google Sheets: G Docs: G Sheets. There is anything under the menu except Normal Text: When I ...
1
vote
1answer
55 views

Group events by day, count them and aggregate the amounts

I have a long list of date times (timestamps) in column A, attached to some data in column B. I want to group this data by day, obtaining the count of events and the totals for each day. For example, ...
1
vote
2answers
56 views

Row by row comparison with FILTER returning error

I am trying do a row by row comparison of two separate sheets, and display the unequal rows on a third sheet. As an example, rows three and five on Sheet1 are different from Sheet2 and I want to ...
0
votes
1answer
39 views

Sort column from another sheet alphabetically

I have a sheet that has a column of names. I want to have another sheet that copies all of the entries from this column in sheet 1 and alphabetizes them automatically as I add new entries into sheet 1....
0
votes
0answers
23 views

Can I Add Multiple “Locations” to a single row for a Map Chart

I have a Google Spreadsheet that I am testing out the Map Chart with. I wanted to test two Charts, Org and Map. So I thought that a list of gods would give me both. However, I ran into a situation ...
1
vote
1answer
54 views

Alternate colored lines in Google Sheets that respect other formattings

I accidently read this question about alternate coloring for filter views wrongly and answered a question I had all the time. The question is: how can I use alternate colored lines but still have ...
2
votes
1answer
33 views

ImportRange as dependent function

I am trying to write a script which set the function importRange in a specific cell, using us KEY the content of another cell. I have the list of the KEYs in Sheet2 and here is what I wrote so far: ...
2
votes
1answer
24 views

Keyboard shortcut to extend Google Sheet formula down to the next cell?

I have a formula in cell A2, say, in a Google Sheet. With cell A2 selected, is there a keyboard shortcut to extend that formula down to cell A3?
1
vote
1answer
21 views

What is a cut and paste approach to converting a chunk of a google spreadsheet to markdown table?

I maintain my web site in a version of markdown, with the common table extension. I maintain my inventory as a google spreadsheet. I'd like a quick way to copy paste a chunk of a spreadsheet, and ...
1
vote
1answer
59 views

How to auto generate the date a URL was last updated?

I'm trying to find out if it's possible to fetch the date a URL was last updated. So column A I'd have the URL and column B the data would auto generate. I'm guessing this would be done using some ...
1
vote
1answer
50 views

Add Additional Lines to Google Sheets Line Chart

I'm having some issues adding simple lines to a line chart on Google Sheets. I have a line chart using Table A, has 10 X and Y values, X values are dates, Y values are a number, simple line chart. I ...
1
vote
0answers
15 views

How can I auto-format US and international phone numbers plus extensions of various lengths in Google Sheets?

I have contact data being submitted from a bajillion sources globally. I would like Google Sheets to format the phone number field specifically to accommodate international phone numbers and phone ...
2
votes
2answers
34 views

Create a grade sheet where it will flag number grades in a certain range by turning that row another color

I'm trying to create a grade sheet where it will flag number grades in a certain range by turning that row another color. The problem I'm having has to do with grades of Zero. I have this formula ...
3
votes
0answers
22 views

Alternate colored lines in Google Sheets that respect filter views [duplicate]

Basically I want the zebra stripes effect for readability but all the methods I've found so far don't work when using filter views. Does anyone know a way or is this not possible? Update: Using a ...
3
votes
2answers
26 views

Don't disturb formulas on Google Sheets when cutting and pasting

I have the formula: =IF(Q5=time(0,0,0),1,0) in cell A3. It works fine, but I need to "cut" and paste the contents on Q5 elsewhere and then Google Sheets "updates" the formula changing Q5 for the new ...
1
vote
0answers
41 views

Convert jagged array to 2D array in Google Sheets

I have two columns in a Google sheet: an ID column with a single value per cell and Color column with an arbitrary number of comma separated values per cell. I'd like to expand the Color column so ...
0
votes
0answers
27 views

How to freeze a row until a similar row shows up on Google sheets?

I would like to make an effect similar to how for example iOS apps persist a table's row until someone has scrolled beside it. example This behavior happens all the time in iOS apps. You can see ...
1
vote
1answer
64 views

Using ImportXML to pull HTML tags along with data

Can ImportXML be used to pull the HTML tags along with the data? For example: importxml(A1,"//div[@id='qlist-wrapper']") on stackoverflow.com will pull all the data in the div with the id qlist-...
2
votes
2answers
75 views

Filtering by case-sensitive string equality

I discovered (when answering this question) that string comparison in filter is case insensitive: the formulas =filter(A:A, B:B = "Yes") and =filter(A:A, B:B = "YES") have the same output. ...
1
vote
0answers
20 views

Dynamic dropdowns - for sales contact management exercise

I am trying to figure out a method in Google spreadsheets where I would be able to display information of contacts after I have selected 2 more criterias before. So imagine it this way: You see a ...
1
vote
2answers
60 views

How to check if a cell contains any of an array of values?

TL;DR: Using SEARCH(search_for, text_to_search), "search_for" is a variable-length array, pls halp On Google Sheets, I have four cells, E1:E4. The values in each cell are as follows: | E ...
1
vote
1answer
107 views

Google Sheets - Keep multiple sheets synced (both ways)

I need to have multiple Google Sheets be synced both ways. For example: I have a "master" sheet that has all of my information. And then I have multiple "filtered" sheets. Each filtered sheet only ...
3
votes
1answer
40 views

Display the contents of a named range elsewhere in the sheet

I have a sheet like so: A B 1 W X 2 Y Z I have the range A1:A2 named as "Column1". I want to display that range elsewhere. Now if I wanted to display it as a row, I know =TRANSPOSE(Column1) works....
0
votes
0answers
21 views

How to lock/protect rows/columns width in “Google Sheets”

Using Tools > protect sheets we can protect certain ranges/sheets from begin "edited". But I want to protect some/all of the rows/columns from being resized (a.k.a. lock width/height).
2
votes
2answers
41 views

Automatically create an array of unique names and their number of wins

I'm trying to make a sheet that I can use to track which heroes I need to practice in a game... Want to manually input the following things: Map name (A2:A), Hero name (B2:B), Result (C2:C) I ...
2
votes
1answer
76 views

Detect whether a cell has strikethrough format

Is there a function that checks if the strikethrough format has been applied to the cell contents? I would like use a formula similar to (=IF(HAS_STRIKETHROUGH(C5), 1,0)
1
vote
1answer
48 views

How to share different tabs in the same Google Sheet with different people/groups

I usually create Google Sheets that have several tabs. Usually the audience of each tab should be completely separate. However it makes sense to keep these tabs in the same sheet since I like to have ...
2
votes
1answer
22 views

Easily switch between seeing only lines affecting a running total and all lines

Background: I have a spreadsheet with one row per date. Each row has a current total A, there are different ways to either add or subtract an value from that total. No other column except G (for A, ...
0
votes
2answers
36 views

Auto entering functions in sheets managed by forms

I want to fill a single column of my sheet with a function, the sheet is being managed by forms. So, whenever a new entry is added it adds a new row and that row will not have the function that I want,...
0
votes
1answer
43 views

How do I change a cell to the color of the hexadecimal value of a cell in Google Spreadsheets?

Based on my data, I calculated values of hexadecimal color codes. I want some cell (I don't care whether it's an empty cell at the end of the row or the same cell) to be the color based on the code. ...
2
votes
2answers
36 views

Conditional Average with a function

I'm trying to average a range but only if one condition is true. A B C 1: A 1 2 2: B 3 4 3: C 5 6 4: D 7 8 5: A 1 6 6: E 8 9 7: E 5 8 What I want is if the char I'm looking for is "A" then I ...
0
votes
0answers
63 views

Which of the functions INDIRECT or IMPORTRANGE slows down my Google sheet that most?

I have a master Google sheet that: Uses the importrange function to fetch data from 8 different columns in 8191 separate google sheets (files, not only sheets/tabs). To be extra clear: in this sheet ...
1
vote
1answer
36 views

What is the difference between creating a menu with .getUI().createMenu() and .addMenu()?

What is the difference between these two ways of getting the same thing (a new menu item)? Version 1 function onOpen(e) { SpreadsheetApp.getUi() .createMenu('My Menu') .addItem('My ...
0
votes
0answers
19 views

How to only use the Google Drive interface (and not the Docs one)

After the switch from Google Docs to Drive I've been trained to do everything through the Drive interface which I now prefer to the Docs one. But for whatever reason the Docs interface is still ...