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 a demo spreadsheet to your question; this will greatly increase the chance that you get a satisfactory answer.
7,965
questions
1
vote
1
answer
32
views
Organizing hierarchies in Google Sheets
I am currently using Google Sheets to organize and update the keywords I use in Adobe Lightroom (Photo organizing software) I'm trying to organize my keywords into hierarchies to make it easier to ...
0
votes
1
answer
13
views
How to get a notification when a column in google spreedsheets changes
I need help with a code.
I need to get a notification on my email or Slack when for example, column D changes. But I would love to received what column changed.
Here is what I got right now.
function ...
0
votes
1
answer
39
views
Google sheets - search a cell for multiple partial words and return different value for each partial word
I am trying to put a formula together that will look at a cell and return a value based on a partial word, but to do it with multiple criteria. For example:
If A1 has the following in it:
Type of ...
0
votes
0
answers
22
views
IMPORT RANGE PARSE ERROR
I have this arrayformula where I need to match cells in a column from one worksheet to another and I used Importrange to link these two but it's giving me errors and I'm not sure what i'm missing
here ...
- 1
-2
votes
1
answer
17
views
Google Sheets Import XML
I'm trying to import the price from this products website: https://www.mobilesentrix.com/lcd-assembly-with-steel-plate-pre-installed-replacement-for-iphone-xr-aftermarket-aq7
But struggling to use the ...
- 1
0
votes
0
answers
24
views
Google Sheets - Autosort Script issues
I am trying to sort data descending based upon column B (2) which has different numbers in each cell. EG: B8 = 99, B14 = 70, etc. I'm intending for it to autosort and do it onEdit. I'm not good at ...
- 1
0
votes
1
answer
14
views
Make ARRAYFORMULA cover the entire column?
This causes he headaches every time I use ArrayFormulas in Sheets. It seems that you have to specify the exact length of the range, and can't just use for example X2:X like you can in any other range ...
- 999
0
votes
1
answer
32
views
Is there a way to create a list of values from a certain column based on referenced criteria in two rows?
I'm trying to find a formula that will fill in two columns with data from another sheet based on values from a dropdown that correspond to header rows from the original dataset.
I have Sheet1 that ...
- 5
0
votes
1
answer
35
views
Hiding columns based on date
Pretty new to scripts.
I have a sheet with name information in columns A-C and dates from column D on.
I found a script that will hide all columns to the left of the column with the current date.
I ...
- 3
0
votes
1
answer
21
views
Custom phone number format with accepting 8 or 9 digits Google Sheets
I want to format a cell to properly display phone numbers, but the number of accepted digits are 8 or 9. It is ok displaying 8 digits with this number format "387" ## ### ### and looks like &...
1
vote
0
answers
21
views
Google Sheets: how to bring drawing to top / bottom
I found workarounds for images by cutting an image and paste it again (so that would be the same to set on top).
And playing with right click, image options, image wrapping may help as well
But with ...
- 345
0
votes
0
answers
26
views
Create a pop up window script for Google Sheets as a password
I have created a sign in/out sheet for students within my building. Students will type their name in column A, click a checkbox as a timestamp when leaving and entering and a dropdown for where they ...
- 1
0
votes
1
answer
25
views
Prevent people from modifying other people's rows with Google Sheets
I want to share a public URL to a Google Sheet with many people (potentially 1000 people or more), with edition permission.
I would like that anyone with the share link can add their own rows and ...
- 407
0
votes
0
answers
30
views
How to get a column value repeated to all related rows of a data table?
The table has EXPIRY date column, and it does not repeat on all rows:
This is a Option price table for MSFT(Microsoft), the data table is sorted by Expiry date on sheet 'BACKEND'.So, when I fetch via ...
- 11
0
votes
0
answers
14
views
VLookup multiple items into one cell
In Google Sheets, I have a Vlookup working for a column with the thing I'm looking for, then two columns in a different sheet with sheetname! where it looks for the thing I'm looking for and gives me ...
0
votes
1
answer
36
views
Need a Unique ID to be populated in Google sheets on Google Form submission
Looking for a dynamic unique ID for Google Forms, to populate into Google Sheets Response form. I don't want to use external addons.
Below is a code I found online but can't get it to work - I'm a ...
1
vote
0
answers
13
views
Formula to automatically sort range of data into groups in different columns by colour?
I am an educator trying to find a way to automatically group students into broad groups of 'below' 'at' and 'above'.
My data is more specific (i.e. numerical value between 0 and 30) and I would like ...
- 11
0
votes
0
answers
16
views
Carryover without overwriting?
I have the following situation and am not sure how to set up such a structure, maybe someone has an idea on that:
A total of 20 different gsheets
1-5: Tables in which information is entered
6-20: ...
0
votes
0
answers
42
views
Sheets: Collaborators inserting their names as values into selected cells
Previous post marked closed due to being unfocused, so I pared it down. Not sure if this is now good enough, if not please can someone comment to explain so I can correct it?
I'm trying to find a way ...
- 1
0
votes
1
answer
35
views
Splitting hierarchical codes into parent items (Google Sheets)
source cell: CCA, BF
result cell: C, CC, CCA, B, BF
CCA is a complex code relating to a hierarchy. It means, CCA is child of CC, which is child of C. So, the formula should split into all parents (C ...
- 1
2
votes
1
answer
54
views
Google Sheets Query - Drop down text match
I would like a query on the 'query' tab that searches sheet 'sales' and returns all values if it matches the dropdown selection on the 'query' tab
https://docs.google.com/spreadsheets/d/...
- 75
1
vote
1
answer
25
views
How do I "=SUMIF" or "=COUNTIF" functions with multiple conditions from different column's?
Afternoon everyone! Strap in as this might get a bit hectic!
I'm working on organizing sheets that are sent to me, and I don't want to change anything about the original sheets (A23:O). The sheet ...
- 13
0
votes
2
answers
42
views
How to return header value if in the column it contains typed value
I am attempting to make a spreadsheet in Google Sheets that will protect the "database" from those able to access it, so I've got the first sheet as the "front end." On the second ...
0
votes
1
answer
23
views
Problem carrying data validation down through column
I am utilizing dependent drop down lists with an indirect function. The first drop down list is Column I, the second drop down list is Column J, and the indirect formula is running in Column N through ...
0
votes
1
answer
22
views
Delete second line of text in every cell in Google Spreadsheet
I have an issue with Google Spreadsheets. I export bulk address lists from my eBay orders into Google Spreadsheet to be able to export as a CSV file to my label printer software for printing. The ...
0
votes
0
answers
16
views
How to lock/link data across a row when using array formula?
I have a sheet with multiple tabs where one tab serves as a master, containing a list of music tracks. This master tab contains information about each track, like duration and BPM (tempo). In another ...
0
votes
0
answers
24
views
Google sheets -How to modify a number in a formula based on user input
I was trying to automate a project where I would need to modify a number in one of the formula every time as per the user input.
For example: in the above formula, the highlighted number (9) should ...
0
votes
0
answers
31
views
Is it possible for spreadsheets to fix a value after calculation even if references change afterwards?
What I want to do is that a cell gets calculated and it never gets calculated again, or a workaround.
My use case is this. I have a Google Spreadsheet where I have two percentages, those percentages ...
- 101
0
votes
0
answers
28
views
Google Sheets - Calculating Difference To The Next Grade Boundary With Multiple Criteria
I need help calculating the difference to the next grade boundary. On sheet 'Year Group', I have a formula that will tell me this difference (col F) but as you can see Apple (row 20) has enough total ...
- 1
0
votes
3
answers
53
views
Weeks since larger drop
Data
Each value in B is the amount that the Federal Reserve balance sheet changed for a given week.
Largest drop since...
This past week, the balance sheet decreased by $31,617.
This is the largest ...
- 117
0
votes
0
answers
22
views
Condensing Data
I have roughly 1000 rows of data, and each day has about 50-60 rows for it. Is there a way I can condense it to where it will just say "May 20" and if you click it, you can get all of the ...
- 1
0
votes
1
answer
21
views
add timestamp to this code
How do I add a timestamp in the format hh:mm:ss in column B (instead of "null")
const values = [[new Date(), null, text]];
const firstBlankCell = sheet.getRange(sheet.getLastRow() + 1, 1)...
-1
votes
2
answers
46
views
Google Sheets – Is there a Full Screen Function or Script?
I was wondering if there is a function that makes google sheets go full screen? For example a script that can be added to picture, that when clicked makes google sheets go full screen? Or upon opening ...
0
votes
1
answer
27
views
How to us Conditional Formatting with color scale based on data in another cell
need some help with conditional formatting. Take a look at the sheet below. I would like to apply conditional formatting to D20 based on the vales of C20. For example, I want it to be on a color scale ...
2
votes
1
answer
77
views
Google Sheets - Group/Aggregate data
I would like a formula that groups/aggregates the data of Column A, B, C and D if column A is the same string
The issue is the end of the string in column A with different sizes at the end of the ...
- 75
0
votes
2
answers
24
views
Can I use a drop down list to color-code a cell with Conditional Formatting, without displaying the text of the dropdown list option?
I know how to create a drop down list of options, and how to use conditional formatting to change the color of a cell based on the selection from the list.
However, I do not want the list item ...
- 3
1
vote
0
answers
17
views
Google Sheet - Sum Column until value is met if another column matches
Column A is trx date
Column B is a stock ticker
Column C is qty of shares purchased
column D is qty of shares sold
Column E is transaction price
Column F is trx total
I would like to have a formula I ...
- 11
0
votes
1
answer
17
views
Correct Google Sheets Autofill - Skipping Cells
I'm trying to have a spreadsheet with a second standings chart that references a lot of different information on the sheet; I'm trying to average a range of a row of cells, but the rows that have the ...
0
votes
2
answers
37
views
TypeError: Sheet.getMaxColumns is not a function. What do I need to do>
I had some code created using the classic editor that did the job of inserting 10 lines in two Google Sheets, which grow down over time with record building. The code speeded things up for the users. ...
- 1
0
votes
1
answer
11
views
I need to "duplicate" a sheet and keep the protected cells
I have a Google Sheet that has protected cells that I need to "duplicate" for each week of the month. However, I can't get the protected cells to "carry over" with the new sheet ...
- 1
0
votes
1
answer
23
views
Import a working formula from a different Google Sheet
I want to import a working formula from doc 1 to doc 2.
I used importrange but then it importet the data from doc 1 to doc 2 and not the formula.
I then used a second spreadsheet in doc 1 to show the ...
- 1
1
vote
1
answer
56
views
Creating new rows using arrayformula if a cell contains info
I have a list of products (bundles of items, eg a number of different books) on single rows in a spreadsheet.
EG:
product
book 1
book 2
book 3
book 4
Product A
ISBN 1
ISBN 2
ISBN 3
Product B
ISBN ...
- 153
0
votes
1
answer
15
views
Counting different types of text and producing specific text outcome
So I'm trying to create a tracker where I can gauge a pass/warning/fail status for each category. Under "Report Status", I wanted a function that can put out the text "Fail" if one ...
0
votes
2
answers
28
views
Converting ARRAYFORMULA to range for SUMIF
I have a sheet that IMPORTRANGE's data from another spreadsheet, which contains a column with numerical values that are of type string. In another sheet I am trying to sum the values in that column ...
- 103
0
votes
1
answer
43
views
How to import only some of csv into Google Sheets
I'm trying to auto-import this huge csv file into Google Sheets, but when I use the importdata function, it tells me "Resource at URL contents exceeded maximum size." I'd like it to auto-...
- 1
0
votes
1
answer
32
views
I'm asking how to sort specific grouped rows
I have a question that seems similar to this one:
Sorting a sheet without losing group order
I want to sort the Rows on the 2nd Tab of the Spreadsheet ("Sorted Sheet"), so that the rows ...
- 3
0
votes
1
answer
29
views
Sorting multiple columns using formula
Give a sequence of columns, I want to sort each column individually using formula because of some reason.
Eg.
A
B
C
D
4
7
2
5
1
8
1
3
9
3
4
7
4
Expected output
A
B
C
D
1
7
1
3
4
7
2
4
8
...
- 3
0
votes
1
answer
16
views
Regexextract numbers from string
I have a regexextract formula which takes the year from Column A (column) - however this only works if the year is yyyy-yy format - however some of the years are only yyyy and it returns an error
Is ...
- 75
1
vote
1
answer
41
views
How to make a formula expand to new rows when new data is added?
I've got a set of data, a column of numbers in this case, that I want to automatically calculate moving averages for when new data is entered. Currently I'm doing this by using =TRIMMEAN($A$2:A2, 0.15)...
- 25
1
vote
2
answers
34
views
Splitting JSON-Formatted Single Column Into Rows and Columns in Google Sheets
PROBLEM
Since Google Sheets doesn't natively support JSON data, which I'm importing, I'm trying to split the data into a matrix of four columns and an unfixed number of rows using only Google Sheets's ...
- 193