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.

Filter by
Sorted by
Tagged with
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 &...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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: ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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/...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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)...
user avatar
-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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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. ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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-...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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)...
user avatar
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 ...
user avatar
  • 193