Questions tagged [google-sheets]
For questions about Google's web application to create and edit spreadsheets on a web browser or app. Don't use it for the Google Drive Excel files editor. Please consider adding data as markdown table to your question; this will greatly increase the chance that you get a satisfactory answer.
1,890
questions with no upvoted or accepted answers
6
votes
1
answer
7k
views
Calculate Percentage with QUERY on Google Sheets
I have a Google Sheets database like this:
Name | Value | Answer
A | 100 | Y
B | 200 | N
C | 10 | Y
A | 100 | N
A | 1000 | Y
B |...
6
votes
2
answers
578
views
Unable to operate script in my work computer and home computer, on other computers it works fine
I have a Google Apps Script project that I have used for the past few months (successfully) and everything has worked just fine. A few weeks ago, I started getting the "Unable to load files" error ...
- 226
6
votes
3
answers
4k
views
Copy range from specific values to top of another sheet
I want to copy everything from the range "Start" to "End" to the top row of another sheet (Sheet2).
Sheet1:
The data between "Start" to "End" can be any number of rows.
This is my current code. It ...
- 161
5
votes
1
answer
12k
views
How can I duplicate a row in Google Sheets while retaining all of the formulas and format?
I need help with Google Sheets. We are using a sheet as a working file and new rows will be added by different users. Formatting and formulas are included in various columns.
How do I ensure the new ...
- 51
4
votes
0
answers
1k
views
How to check if there is any Data Validation error in the whole spreadsheet?
Is there a way to check in a Google Sheets spreadsheet, if there is at least one cell violating a Data Validation rule? If such functionality is not built-into the app, how can I write a Google Apps ...
4
votes
0
answers
166
views
How to automatically convert all Microsoft Office file attachments in Gmail to Google Docs?
My Gmail has thousands of emails. Each email will have several attachments as Microsoft Office files (.doc, .docx, .xls, .xlsx, .ppt, .pptx).
Currently, I want to automatically convert all Microsoft ...
- 41
4
votes
2
answers
23k
views
How to insert Mathematical Equations in Google Sheets?
I have been searching for a while now and all searches lead to Google Docs, not Google Sheets. So basically what I'm looking for is similar to the insert equation function as in Excel where you can ...
- 41
4
votes
2
answers
7k
views
Why would I be getting the error: Too many simultaneous invocations: Spreadsheets on a simple once a day trigger?
I've searched around and still can't find anything directly related to the failure message I'm receiving. The script hasn't changed and it used to run just fine, but now in the past week or so it's ...
- 41
4
votes
2
answers
1k
views
How to Modify Matched Cells Across Multiple Sheets with Google Script
My question is a fairly complex one, but I'll do my best to clarify exactly what I'd like to do and this requires some background:
I have a Google Spreadsheet used for user administration in my ...
4
votes
1
answer
1k
views
How can I create a Google Spreadsheets whose name and cells auto-complete with cells completed on another sheet?
I'm using Google Spreadsheets to manage projects, and I'm not familiar with scripts or macros.
What is:
1) I have a Gantt spreadsheet which lists all the ongoing and foreseen project as follows (by ...
- 41
3
votes
1
answer
971
views
Googlefinance function - What ticker symbol to use for the crude oil price?
Which ticker symbol can I use for the crude oil price for the GOOGLEFINANCE function?
Nasdaq Brent oil is "BZ:NMX"
https://www.nasdaq.com/market-activity/commodities/bz:nmx
Nasdaq Crude oil ...
- 271
3
votes
1
answer
47
views
I wish to make my Google sheet slightly easier for me to use
Can anyone teach me how to autofill the "Selling At" column E and "SC Used Per Transaction" when I key in the "Denomination Sold" Column?
So for example, when I key in &...
- 31
3
votes
0
answers
465
views
How do I specify a consistent interval for a date-range axis in a Google Sheet Chart
I have a Google Sheet with a set of deposits and withdrawls and the running balance. I have added a chart to the sheet, so I can visualize what the balance has been over time and when deposits or ...
- 31
3
votes
1
answer
119
views
How do I input a current balance in a cell from a running balance that will be updated frequently?
I know I'm not accurately explaining that but here's a sample spreadsheet of what I mean:
- 31
3
votes
1
answer
51
views
Sums with Same Value - Follow-up Question
This is a follow-up to these questions and information.
Thank you Jacob and Kowser for the formulae you shared many years ago! The information you shared is almost exactly what I need to simplify a ...
- 31
3
votes
1
answer
42
views
In google sheets - how return *1.54M* as $1,540,000 with the same formula that I would return $1.35B* as $1,350,000,000?
I am building a stock model - where I use =importhtml() to give me financial data pulled from a website on certain stocks.
It is returning it correctly, but there are asterisks surrounding all the ...
- 31
3
votes
1
answer
660
views
How do I stop Google Sheets from opening new tabs?
In Google Sheets, I have two completely separate spreadsheet files. In Spreadsheet A, there is a cell with a link to open Spreadsheet B, and vice versa. When I click on the link, the other ...
- 31
3
votes
2
answers
3k
views
Import button greyed out in Google Sheets menu for unknown reason
I'm trying to import a .csv file into my Google Sheet like I've done myriad times. However, occasionally, the Import button in the menu is greyed out (see screenshot).
Reloading the document doesn't ...
- 521
3
votes
1
answer
748
views
Google Sheets - Query Multiple Columns for same Data
We have Employee training data in a Google 'Form Response' spreadsheet which ranges from A1 to JQ1000. This spreadsheet is automatically populated when a Google Form is completed but it is difficult ...
- 131
3
votes
2
answers
133
views
Automatically copy first 3 columns from one sheet to each new sheet in Google Sheets
I'm having columns A, B and C filled with data in Sheet1. Those columns contains data that is static for each new sheet. Currently, I need to manually copy the first three columns to each new sheet I ...
- 31
3
votes
1
answer
309
views
Struggling to Generate Chart From Existing Data
This question is specific to Google Sheets—hopefully I’m in the right place.
https://docs.google.com/spreadsheets/d/161rtpjNK5363-U6y6MZYzqOIx7CQBCHDWInBXFtZYIg/edit?usp=sharing
As you’ll see above, ...
- 131
3
votes
2
answers
5k
views
How do I get Google forms edit url to work
I want to generate an edit url link in the google sheet for a google form which is linked to it.
There are already instructions on how to do it. However they do not work for me.
I have followed the ...
- 31
3
votes
1
answer
6k
views
Repeat formula n number of times in Google Sheets
Apologies, I'm very new to Google Sheets and spreadsheets in general. I'm trying to fill some cells with repeating data, and then analyze it. The data is pulled from another large spreadsheet and has ...
Phinet
3
votes
1
answer
3k
views
Lost responses from Google Forms
I have updated my form with some responses recorded. After deleting one question section, the responses for that question disappeared. Can I still get to them?
- 31
3
votes
2
answers
6k
views
How to pull last value in a Google Sheets column and populate it across another worksheet column?
This is my first post here- I hope this is the right place for it, apologies in advance if it isn't.
Context:
I have a password protected presentation that I need to give users access to, but only ...
Akira
3
votes
3
answers
651
views
How to remove duplicate words in a cell only if longer than a certain number of characters?
I need to remove a duplicate text in a cell but only if it is longer than 4 characters.
I have this formula that only works for any types of duplicate words:
=join(" ",unique(transpose(split(A1,", ")...
- 31
3
votes
1
answer
61
views
IF Cell = True, then retrieve (separate) Cell Value
Cells:
T2 = Price to Pay
U2 = (Tick box)
V2 = =if(U2=True, "Paid", "To Pay")
W2 = empty
What I want to do is be able to tick U2 and have W2 contain the value of T2. V2 works, so it does not ...
3
votes
2
answers
884
views
Formula for PROPER Case with Apostrophe
I'm using Google Sheets glued together (not be me) from multiple sources where some of the names are in all caps. In trying to clean web-ready content, I have good success with the PROPER() function ...
- 67
3
votes
3
answers
510
views
Conditional formatting for columns, date & time specific?
I have uploaded 4.000+ Venmo donation data sets into Google Sheets for sorting, but the date and time is preset and looks like this:
2018-11-01T20:16:52
I have tried multiple approaches to sorting ...
- 31
3
votes
1
answer
235
views
Add values between two dates in Google Sheets
So I've already found this question answered several times around here but for some reason, despite essentially copy and pasting the answers it still doesn't work for me.
I'm trying to add ...
- 31
3
votes
1
answer
6k
views
Query returning results into merged cells in Google Sheets
Short Question:
I have a query function returning an array over merged cells. Instead of displaying all of the values in the array, some of the data is hidden in merged cells where only the first ...
- 364
3
votes
1
answer
144
views
Script to place duplicate Google sheet beside the sheet it copies (instead of the end)
Is there any way to modify my script to have the sheet it's copying insert the new sheet immediately to the right of the one it's copying? I have tons of tabs in the file, and it's starting to get ...
- 31
3
votes
1
answer
3k
views
Format telephone numbers, keeping only digits
I have a spreadsheet that has phone numbers in the cells that are typed out like this:
+1(313)313-3333
and I need these numbers to all be plain and look like this 3133133333 (with no 1 in front).
...
3
votes
1
answer
2k
views
Reference Cells in Sorted Filter View
I have a Google Sheet that looks like this:
That Google Sheet has some Google Script that looks like Google This:
function myFunction() {
var ss = SpreadsheetApp.getActiveSheet();
var rng = ss....
- 353
3
votes
3
answers
2k
views
Summing values from multiple sheets with names specified in cells
I'm attempting to sum unknown quantities of data that is contained in several sheets. The amount of sheets isn't known either. Thus far, I've simply been using the indirect command on a column with an ...
- 147
3
votes
0
answers
2k
views
How to find all Google Docs (or Sheets) with comments?
In Google Docs/Sheets, when making a copy of a file, the comments don't get copied over. There is a plugin (Copy Comments) that makes a copy-with-comments for Docs, but it's one document at a time, ...
- 163
3
votes
1
answer
159
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 ...
- 131
3
votes
0
answers
6k
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 ...
- 229
3
votes
1
answer
2k
views
Simultaneously sort multiple ranges on a single sheet in varying orders
I am trying to find a way using a script if possible (or formula) that will allow me to reduce the number of operations that I have perform to sort data in a particular google spreadsheet. Currently, ...
- 31
3
votes
1
answer
5k
views
Can I precisely position charts in Google Sheets?
I am using Google Sheets to create a "Monthly Report" spreadsheet for a product, and am attempting to make an attractive, compelling experience for the consumers of the report.
In this spreadsheet, I ...
- 69
3
votes
2
answers
64
views
How to find the maximum number of consecutive negative occurrences in GOOGLE SHEET using the specified cells?
I can now find the maximum number of consecutive negative occurrences of B:B.
However, my problem is that I want to find the maximum number of consecutive occurrences of negative B:B that match the C3 ...
- 31
3
votes
2
answers
72
views
Filter function with multiple variables google sheets
I want to be able to use a filter function for people in NAME whose P DATE is within a specified range. However, if they have a Y under VALIDATED, I want the filter to also include names with the T ...
- 31
2
votes
1
answer
37
views
How do I change the number format for the horizontal axis labels in a histogram?
I would like to change the number format of the horizontal axis labels of a histogram. Some tutorials mention that this should work, e.g.
New chart text and number formatting options in Sheets
How To☝...
- 121
2
votes
2
answers
45
views
How can I group/count occurences of a column
I have a Google sheet with 2 columns.
| Jim | Foo |
| John | Bar |
| Jane | X |
| Jim | YZ |
What I usually do is:
download the values as a CSV
write/run a script that processes the CSV
the ...
- 121
2
votes
2
answers
46
views
In Google Sheets, is it possible to collate + auto sort multiple columns of data into a single column?
I used this site to learn how to sort a two-column list, {string, number}.
I can't find a way to combine multiple two-column lists into a single, two-column list.
For example, in Major League Baseball ...
- 21
2
votes
1
answer
28
views
Recursively search cell for each value within an array and return position within array
I have a bunch of transaction descriptions, and I'd like to check against an array for any matches (case insensitive, in any position within the cell) and return the associated position within the ...
- 121
2
votes
0
answers
123
views
Moving data generated by a Google form from the response tab to another tab
I'm trying to copy data from a Google Sheets tab linked to a Google form into a new tab. I used the function ='Form Responses 1'!B2, then copied the formula in the other cells of the column. But when ...
- 21
2
votes
1
answer
143
views
data validation case sensitive string in google sheets
I'm trying to formulate a data validation code in Google Sheets for a simple 6-letter string - any 3 capital letters and any 3 lowercase letters. I believe I should be using the RegexMatch function. ...
- 21
2
votes
1
answer
83
views
How do I receive the data from Google Form onto Google sheets and allow my students to edit it without other students seeing their work
---I am trying to feed data from a Google Form to a Google Sheet. I want to have it separate out each student into their own sheet. Then I want to sent that information back to the student in a ...
- 21
2
votes
2
answers
133
views
Can I avoid copying this formula in new rows by using ARRAYFORMULA
Suppose I have this sheet showing which group (1, 2 or 3) each student joined on each day of class. Column B indicates in which group he/she last showed up, which is obtained by using the following ...
- 41