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

learn more… | top users | synonyms (2)

1
vote
1answer
83 views

How to delete a row in a Google Sheet based on date

I have a Google Sheet I'm using to geocode addresses, which shows where current road closures are located. In my table, I have a date field "StartDate" and a number field "Duration". "Duration" ...
-1
votes
1answer
71 views

How to calculate with cells that contain two values separated by linebreak?

I've got a Google spreadsheet that looks like this: Start time | End Time | Time difference -------------+---------------+----------------- 11:30:00 | 12:45:00 | 1,25 ...
2
votes
1answer
333 views

Conditional format rule “Text Contains” with a “+” misinterpreted as a formula

I'm trying to conditioan format a bunch of cells that are strings by having cells with a - red and cells with a + green. However when just putting a + o - in the conditional formatting rules it says ...
3
votes
1answer
61 views

How to auto-update function based on background color

I've been referencing @w0lf's answer to help me calculate the sum of cells based on their background color in Google Sheets. From the comments and some other research it appears that changing the ...
1
vote
0answers
7 views

Combining several workbooks into one

I have several workbooks need to merge into one. Each workbook has several tabs, it varies, some of the tabs are same name . I formats are same. In my master, I want to move these tabs. If tab name ...
3
votes
1answer
236 views

Google Sheets Query(ImportRange) function to skip columns

I'm currently using the following function to import data from one spreadsheet to another based on a value in Column K of the source spreadsheet: =QUERY( IMPORTRANGE("URL","Sheet Name!A2:P1000"), ...
1
vote
0answers
96 views

Google Spreadsheets - Snap Chart To Grid or Place A Chart In A Cell?

Is there a way to snap charts to the grid? In my spreadsheet, my chart hangs a little below my table, but I would like the chart to expand/contract with the cells on the left. If this is an easy ...
3
votes
1answer
53 views

Google Spreadsheets - How To Have 2 Functions Output Into 1 Box?

I'm using Google Spreadsheets and I'm currently trying to get multiple functions to output into 1 square. In my example: ={IF((B2-D2)>=0,"+"), SUM(B2-D2)} I get the output I want +7, but it's in ...
1
vote
0answers
27 views

Google Spreadsheet Filter() function randomly stops returning results

I've been having a reoccurring issue with Google Sheets. My FILTER() functions will randomly stop returning results instead saying "No Matches Are Found in FILTER Evaluation". I have to copy the ...
1
vote
1answer
19 views

How can I sum a column with a boolean secondary column

With this kind of Google Docs Spreadsheet: How can I calculate SUM(C1:C8), but without values where the corresponding D column has a 0 (so, without C1 and C3)?
3
votes
1answer
60 views

Google Sheets: can't sort table that use INDEX and MATCH?

I have this scheme on a Google Spreadsheet: A B C D E 1 text text text INDEX/MATCH INDEX/MATCH (expansion) 2 text text text ...
3
votes
2answers
72 views

Sum based on month and name columns

14.08.2015 John Doe 100 14.08.2015 Bruce Willis 100 09.08.2015 Keanu Reaves 100 15.08.2015 John Doe 250 18.08.2015 John Doe 150 18.08.2015 Keanu Reaves 200 ...
4
votes
2answers
52 views

How to hide unused columns in Google Sheets

I would like to make a Google Spreadsheet that has a limited column numbers but I cannot find where is this configured. Looked for this setting with all that I could think would make a good keyword : ...
3
votes
1answer
29 views

Autopopulate Tuesdays, Thursdays, Saturdays starting from a fixed date

I am trying to autopopulate dates from a fixed date in Google Sheets. Essentially, I want it to autopopulate the dates for every Tuesday, Thursday and Saturday. I have found stuff on skipping ...
1
vote
2answers
25 views

Google Forms submits times that are not numerically consistent with those in a spreadsheet

When I submit a Google Form, and I fill in a time the number that is entered into the spreadsheet is not the same number the spreadsheet will use for that time. ie. If I enter 3:30 PM in a form and ...
1
vote
1answer
25 views

FILTER() Is not returning consistent results

I'm having some issues with FILTER() not returning consistent results, below is an image detailing the inconsistencies. When filtering for 3:30 PM I only get 2 results instead of 3. When filtering ...
3
votes
1answer
46 views

Query is returning a single concatenated string of values THEN the rows it found

I'm getting some really weird results from my QUERY() Here is an image with an example: I'm querying for the name where No Show = "No Show". It find the correct name Name 5 but also returns a ...
1
vote
1answer
18 views

Use the order of sheets in which they appear in a Google spreadsheet

Goal What I would like to accomplish is to have my aggregation-master-schedule-sheet (see context) put schedules in the same order that the sheets are. I.e. the left most sheet is 1st, the 2nd ...
1
vote
1answer
30 views

Send the argument of a custom function as an email

I am trying to send email via script in Google Spreadsheet. I know that the argument in this case cell B4 will be integer. In my cell is function =sendEmails(B4) and my script looks like this. ...
4
votes
1answer
38 views

DGET is not successfully matching criteria in Time format

I'm trying to use DGET() to match values across several sheets. If I try and match criteria that are times, it always fails. If I replace the criteria with words, it is successful. Here is an image ...
1
vote
1answer
15 views

Select a sheet to print from, using a dropdown

I have a "workbook" with multiple sheets. Each sheet is a schedule for the week for employees with a bunch of math stuff to help a general manager see their labor. I'm trying to create a single sheet ...
0
votes
1answer
48 views

Conditional formatting of an entire row based on multiple cells

I'm trying to make a conditional formatting rule in Google Spreadsheet that would highlight a row green based on text in multiple cells. If cells L2, M2, and N2 = APPROVED; and cell O2 = X; then row ...
1
vote
1answer
42 views

Filter/Query a column of names by an array of names [duplicate]

I have two columns of names in my spreadsheet. In column 1 I have a long list of names, in column 2 I only have 10 names. I want to filter column 1 in such a way that it will only display the names ...
1
vote
1answer
26 views

Averaging the column F3:F breaks as script adds new rows above row 3

I have Google Sheet connected via Zapier to automatically add a new row to my spreadsheet everyone time someone fills out a satisfaction survey on my website. I have =AVERAGE(F3:F) as the top row to ...
1
vote
1answer
16 views

Running one of two queries based on the content of a cell

I have a query which works on certain condition query1 =query('KEY WORK'!A1:I200,"select A,B,C,D where E = '"&B8&"' and A = '"&B4&"' and I = FALSE",) query2 =query('KEY ...
1
vote
1answer
29 views

Highlight a cell if the date in one column is the latest date

I'm trying to figure out the proper conditional formatting formula to hilight a cell based on the date in that cell (and/or an adjascent one) representing the most recent date in the column. Given ...
3
votes
1answer
55 views

See if a cell contains a string not from a list of strings in another column

In Google Sheets I have two columns, A and B, and want to know when a cell in column A contains a word that is not in column B. Column A contains cells with multiple words per cell, comma separated: ...
2
votes
1answer
127 views

Can I use conditional formatting to color a column's cells' text based on the conditionally-formatted color of text in another column's cells?

I have been working on a spreadsheet in Google Sheets that serves as a database of the hundreds of characters who appear in a novel I am reading (to help me keep track of them all). Column A contains ...
1
vote
0answers
65 views

How do I post a value from Google Spreadsheets to Slack twice a day at preset times?

I have a Google spreadsheet that I constantly update and I'd like to set certain times of the day, say 8am and 4pm, to automatically read the value in certain cells and post them to a channel on ...
1
vote
1answer
33 views

Conditional formatting based on multiple cells

I'm trying to figure out how to write a formula for Google Spreadsheet where: If F3 Contains Data and G3 is more than 0 then cell J3 is Highlighted in Green Can anyone help me out with this?
5
votes
1answer
294 views

Automatically add borders around nonempty cells

I'm looking to have cell borders automatically applied when a cell is not empty. I thought I could use conditional formatting for this, but it doesn't seem to be an option.
1
vote
1answer
149 views

Filter based on Data Validation drop down list

So I have a two part question that involves filtering a column based off of both data validation drop down lists, and dates. The problem for me is to create a filter that will give me a subtotal ...
1
vote
0answers
32 views

How do I set my Google Sheet up to create a running list of data from another Google Sheet?

I am attempting to create a master calendar in Google Sheets that my department can use to add appointments to. I know I can grant them access to my calendar to add these appointment directly, ...
0
votes
1answer
40 views

Google Sheet Cell Link to Google Form test score

I have a pre-made Google Sheet which has cells to be marked as "pass" or "fail" depending on the person's score. Is it possible to link a Form response to record a pass or fail mark depending on the ...
3
votes
1answer
53 views

Format a cell in bold if its row has other nonempty cells

I'm looking to format a Google Spreadsheet with something beyond the "basic" Conditional Formatting they offer. From what I understand, this must be done using a Google Apps Script. I've found one ...
2
votes
1answer
261 views

E-Mail from Google Spreadsheets: including image for signature takes out formatting of message

Following situation: Sending emails from google spreadsheets. I have a compiled text (with line breaks), which is stored in var message. I have been trying to include a picture for the signature. To ...
4
votes
1answer
246 views

Filter and subtotal by month

So I'm trying to do three things that are related to each other that I'm struggling to figure out: I'd like to filter an entire column (B) based on a the month (let's say April), while maintaining ...
2
votes
1answer
47 views

“getAs()” error - send an Email with Attachment using GoogleAppScript [duplicate]

I am very new to GoogleAppsScript and struggling. Here is my problem: Trying to send multiple E-Mails via Spreadsheet. Want to attach a PDF to those mails. Getting the following error: TypeError: ...
1
vote
1answer
61 views

Google Sheet not being populated and responses summary not getting incremented after Google Form submission

My wife has a paint & wine party business and I've been using an embedded Google Form for registration on her site for nearly a year without issue. Last week I updated the photos in the ...
3
votes
1answer
94 views

Using Filter formula to display cells starting with a specific number

In my Google spreadsheet I have data which updates automatically. I need to see only cells which start with 1. I have used filter but it didn't workout as after each update filter doesn't update so I ...
2
votes
2answers
50 views

Add the numbers in rows where a string contains a given word

I have two columns a and b a contains a set of text and b contains a set of numerical that relates to each row in a I want to create a function that will search column a for substring value and ...
3
votes
1answer
22 views

How do I have a cell contain a link in Google Sheets that is shown as plain text?

I've tried prepending with ' and formatting the cell as plain text. I've even tried stuff like ="part of url" + "next part of url" It just insists on representing it as a navigable link.
0
votes
0answers
37 views

Spreadsheet: How to calculate a percentage with a circular reference

I want to calculate a percentage of profit where the percentage is an expense and therefore affects the profit. Let's assume: Revenue 100,000 Expenses 50,000 Profit Percentage 50% Normally the ...
3
votes
1answer
214 views

Is it possible to link a Google Form to a desktop Excel sheet so that when someone fills in the form the data goes into the Excel sheet?

I work for a sporting organisation and track all sorts of monitoring variables using Excel spreadsheets. Up until now I have used macros in Excel to do this, but this requires players taking it in ...
2
votes
1answer
112 views

In Google Sheets' filter views, can I filter columns non-cumulatively?

I have been putting together a spreadsheet in Google Sheets that includes various data/information relating to the hundreds of characters in a novel I am reading. Two of the columns relate to the ...
0
votes
3answers
8 views

How to download Google Sheets?

What are ways to download a Google sheet that you were given access to but don't own? On https://docs.google.com — is there a Chrome extension or anything like that?
1
vote
0answers
194 views

How do I add error bars based on a series?

I've got a Google Spreadsheet. First sheet is a set of raw data for ratings of beers. Then I have a pivot table on that data, showing beers grouped by brewery, with values for the countunique() of ...
1
vote
2answers
52 views

Formula result depending on the week of the day

I've got a bunch of cells that have formulas to display the day of the week according to the date above it. (I'm using Google Sheets by the way) I want the cell underneath the day of the week that ...
1
vote
1answer
33 views

Iterative sums in google sheets

Continuation of my previous question. I would like to calculate an average item cost for a trading log, using only the most recent number of items equal to the number in stock. Currently, I'm only ...
1
vote
1answer
25 views

RegexReplace produces an error

I have two sheets of data as per below: ╔═══════════╦══════╦══════════════╗ ║ SheetName ║ Cell ║ CellValue ║ ╠═══════════╬══════╬══════════════╣ ║ Sheet1 ║ B1 ║ ='sheet2'!A2 ║ ║ Sheet2 ║ ...