Questions tagged [google-sheets]

For questions about the Google's web application to create and edit spreadsheets on a web browser. 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 chances to get a satisfactory answer.

Filter by
Sorted by
Tagged with
0
votes
1answer
14 views

How to do you extract characters from a string in Google Sheets [duplicate]

I have a string in a cell: A|BCD|EF|GHI|JK. I would like to extract it separate in individual cells: A should be in one cell, BCD in the next, EF in the next, and so on. I would appreciate any ...
2
votes
0answers
33 views

*UPDATE* Hide/unhide certain columns on some conditions

UPDATE: I FIGURED IT OUT!!!! YEY!!!! Im trying to have certain columns in my Google Sheet hide or unhide, depending on some conditions. I realized that this is difficult. But I've managed (I think) ...
0
votes
0answers
23 views

Google Sheets script that allows hiding and filtering of rows in a protected range?

Like many others I am trying to allow the average user to hide rows in a spreadsheet that has an entire protected column. Currently, they are denited because hiding is considered editing. From this ...
0
votes
1answer
17 views

Google Sheets: Formula to get data from other tab

I have a Google Sheet to get an overview of all workshops/events we are hosting. There is a tab INPUT. There is a column for Date, Month, Event, Location, Presenter, Language. The column month is ...
0
votes
0answers
8 views

Google Sheets data not loading in pdf

I have a Google Sheets with some tabs, but not yet a massive amount of information. I have one tab where data is put in and then two tabs where this data is being displayed by a query formula. This ...
1
vote
0answers
8 views

I need to create a custom script to show specific columns based on choices from a dropdown in GSheets

I'm setting up a product tool in a google sheet, and the purpose is to only show up to 8 of about 91 columns. Each column is a product, each row is the "entitlements" or features of that product. The ...
1
vote
1answer
22 views

Formatting based on multiple cells and duplicates

I will try my best to explain the situation : I have a sheet where we enter test data and serial numbers. Column A is serial number Column C is if the unit passed or not. I currently have ...
0
votes
0answers
3 views

OR statement in conditional formatting

I am trying to use an OR statement to set off conditional formatting in google sheets, but I don't want to have to keep defining the range. In other words, I want to change the formatting if a:a100 = ...
1
vote
0answers
59 views

Importing data into Google Form response sheet with date, list, grid

I have a normal Google Form that is filling answers into a response spreadsheet but I want to import pre-existing data stored on another Google Sheet (100 entries) that match the Google Form responses ...
1
vote
1answer
12 views

Split Google Sheet into new Google Sheets by row key

I have a sheet that has has a bunch of data in it, all tied to a site URL. It's not sorted data, and each site can have no log entries or many. There are many items in this file, so I'm looking for ...
1
vote
0answers
10 views

How do I get my last played on a steam game from my profile

I am attempting to grab my last played on my steam games. I am currently working with this.. =REGEXEXTRACT(REGEXEXTRACT(CONCATENATE(IMPORTXML("http://steamcommunity.com/id/PerfectPlays/games/?tab=...
1
vote
2answers
50 views

Google Sheets, Reorder filter views list

Ive got a google drive spreadsheet with lots of filter views. Ive been adding them on an adhoc basis, and the list is growing a bit long making it hard to find the filters when i need them. Is it ...
1
vote
0answers
12 views

Sorting a row by emptiness along another row with a specific date

I have made a goal organization spreadsheet so that I can be more organized and productive in reaching my goals. So far I have my goals spread out on a sheet with a lot of white space in between ...
1
vote
0answers
11 views

Using a query on an importrange, not pulling information

I am trying to do a query on importrange but it is only showing me the headers not any of the data. I am really stuck. =QUERY(IMPORTRANGE("19Nx2EtMktFW-xwNMjY4IiQpN33QHrXIwcqDsvKqCiHo", "MASTER!A1:...
2
votes
2answers
47 views

Is there any way to set different values for multiple ranges all at once?

Is there any way via script to put "Apple" in cell A1 and "Banana" in cell B4 simultaneously? The motivation to do so would be that the sheet would only recalculate one time. Currently it re-...
0
votes
1answer
17 views

Creating a custom formula in Google Sheets without scripting, but instead based upon another sheet

Ive made a custom calculator in a google sheet, the calculator accepts 5 inputs (arguments) on row 1, rows 2-50 is where is runs the calculation, and the result comes out in row 51. This calculator ...
1
vote
1answer
25 views

Multiple custom menu order

I have set up multiple custom menus in one of my Google Sheets. While the items in the menu stay in order, the 3 menus that I have created are always switching places. Is there a way for me to order ...
0
votes
1answer
37 views

Why is this Google Sheets formula counting blank cells?

I am new to Google Sheets but really am only using it for simple tasks - yet I have encountered an issue that I cannot seem to resolve. I will start with an image of my test Google Sheet below: If ...
1
vote
0answers
37 views

Google Sheets Conditional Formatting only applying formatting to certain cells

I am currently working on making a spreadsheet that will figure out which class I should be in based on times. That all works and outputs if I should be in the class to a TRUE/FALSE cell. My ...
0
votes
2answers
44 views

gscript “getValue()” returns “#VALUE!” error

Problem My spreadsheet displays a number in the ReturnOnInvestment range, not the #VALUE! error. I checked analysisSheet.getRange("ReturnOnInvestment").getA1Notation() to make sure I'm not confused ...
0
votes
1answer
23 views

Google Sheets script server error on activeSpreadsheet.getNamedRanges()

I have a script to get a list of Named Ranges on a google spreadsheet. I was working fine until yesterday, where the execution is failing with: We're sorry, a server error occurred. Please wait a ...
1
vote
2answers
55 views

How can I flip a column of data vertically in Google Spreadsheet?

How can I flip a column of data vertically in Google Spreadsheet? E.g. if the column is: Bob Alice Calamari I want: Calamari Alice Bob
0
votes
0answers
18 views

Utilize SI units in Google Sheets

Seven years ago this was asked, are there any new developments with regard to formatting with SI units? Normally, I will adjust the format to be all milli or micro simply by division, however my ...
1
vote
1answer
46 views

Getting every combination of 4 items from a unique list

I have a list of items that looks like this : The goal would be to get every unique combination of 4 of those items. The combinations are unordered so ABCD, BADC, DBAC, etc. are considered the same ...
1
vote
1answer
8 views

How can I add/subtract the value of ANY cell within a column by 1 using a button?:

I found the script: function increment() { SpreadsheetApp.getActiveSheet().getRange('A1').setValue(SpreadsheetApp.getActiveSheet().getRange('A1').getValue() + 1); } But this is limited to a ...
1
vote
0answers
29 views

Multiple columns and get unique on preferred column

I want to get 3 columns only if a preferred column is unique. What is the correct syntax in Google Sheets? =Query('Sales'!A:AX,"Select A, B, C where A is unique")
1
vote
1answer
37 views

Filter View With Criteria Ac cross Multiple Columns or some other solution

I am struggling to find a solution either for the Filter view or using the slicer that would give me some sort of an OR function. My spreadsheet looks like this: I need the Filter view it to show ...
1
vote
1answer
14 views

Can I use Google Sheets to Automatically Update Font Styling, Size, Color, etc.?

I have been creating a contact list in Google Sheets to continually add and update names, emails, organizations, etc. for work. I am often copying and pasting this information from other sources as to ...
2
votes
1answer
50 views

Apply conditional formatting to a blank cell if another cell in the row is not blank

I am trying to create an equation for conditional formatting that would highlight a cell that is blank if another cell in the same row is not blank (i.e. that row is being "used"). In the picture ...
1
vote
1answer
24 views

In google sheets, want to copy cell values from 1 sheet to another if a specific cell has any text

I have this working well: =if('Lee''s sheet'!$AP2="x",'Lee''s sheet'!B2,"") which will populate cell values into a cell, from 1 sheet to another if there is an "X" marked in column AP. I want to ...
0
votes
0answers
21 views

Clearing 1 cell based on a changing the contents in another cell

I'm trying to clear a cell based on changing the contents of another cell. I have tried this solution below but it has a few ramifications. it is executing for all tabs. I want to specify one tab. ...
0
votes
0answers
25 views

How can I create a violin plot in Google Spreadsheets?

How can I create a violin plot in Google Spreadsheets? Example of violin plot:
0
votes
0answers
13 views

Multiple IF Statement for Google Sheets [duplicate]

I'm looking to have one cell do calculations for commissions based on revenue. Example: If A1 < 1000, (A1 * 0.35) If A1 < 1200, (A1 * 0.3) If A1 => 1200, (A1 - 350) Thanks in advance
2
votes
1answer
53 views

How to check if the values of two cells exist in two columns of another sheet

I am trying to make a class attendance system where the student checks in by submitting her name into google forms. The final result will be displayed in Google sheets by marking a check box after ...
1
vote
1answer
22 views

google sheets, making a dropdown list based on user input

I am working on a scheduling system for a very specific process. I am going to be sending out some shifts to my employees, and based on the result of their input, I want to create a dropdown for the ...
1
vote
1answer
38 views

Google sheets Xlookup dont work but need results in table the max date on the condition of userlist

Need some help with some data processing. Link of the sheet Example - I need on : Page-2-Seznam članov > Coloumn C : It needs to be the max dates. The function need to search on the liste the ...
1
vote
1answer
20 views

Automated way to copy comments out of comment tool and paste into a cell?

Ive got a spreadsheet with a load of "cell comments" using the comment tool. I need to filter on these comments, which i understand i can not do. My plan is to copy each of the comments out of the ...
0
votes
0answers
9 views

Split, unique and array in Google Sheets [duplicate]

I have the following table: email1 name1 text1,text2,text3,text1 email2 name2 text1,text2,text2 email3 name3 text1,text2,text3,text4 email4 name4 ... I want the table to look like this with ...
2
votes
1answer
31 views

How to generate standard competition ranks (not dense) for my Google Sheet?

What I have is a leaderboard sorted according from high to low scores. I would like to assign standard competition ranks (1224) formulaically. All 7 people who have the highest score (30) should be ...
1
vote
1answer
8 views

Conditionally format a range based on the value of a single cell outside of it [duplicate]

I am using conditional formatting with a custom formula. I want to highlight rows in the range A2:B11 if the values in $B2 (column B starting at row 2) is greater than the value in the cell D5. I can ...
1
vote
1answer
45 views

How can I get the information from a column next to the active cell to be included in the email?

I have this script running which works fine. However as you see, it only copies the information of the active cell in Column 10 toString. How can I get the information next to it in Column 11 to be ...
1
vote
1answer
31 views

How to embed a google map of an address into a google sheet

I have a google sheet and I have cell B1 called Address which contains: 405 E 42nd St, New York, NY 10017 I would like to embed a google map into my google sheet in cell D5 that looks like this: ...
1
vote
1answer
26 views

Google Sheets formula for “if contains” for grading multiple choice quiz

I want to use a Google Sheets formula to correct a multiple choice survey that I built using Squarespace. The results get dumped into Google sheets. For example, if the respondent selects choice: A....
1
vote
1answer
47 views

My formula in Google Sheets doesn’t add up to the same total in Yahoo?

Link: https://docs.google.com/spreadsheets/d/1KcYqRRqxZ3GWNAIUWgg9AGraTAzkrVxkDrk4ZsI5axY/edit?usp=sharing So I do sports spreadsheets for fantasy sports. I’m filling out my baseball spreadsheet ...
1
vote
1answer
23 views

How to pass an entire sheet as a parameter to a Google Sheets function with dependencies

I have a Google Sheets app script that is passed the name of a sheet. Internally it pulls the data from that sheet and returns a value. Something like this: function myFunction1(partNumber) { var ...
1
vote
2answers
57 views

Select All Columns Where Cell In Columns Contain A Word - Google Sheets

I am looking for a query formula that will allow me to select all columns (entire column) that contain a specific word within the column. You can see a test sheet here: https://docs.google.com/...
2
votes
1answer
26 views

Google Sheets Query: Making the selected column depend on formula

I would like to use a normal QUERY with where operating in the usual way where A = 'Speed' but with the selected column being determined based on a formula. Specifically, I want to return only the ...
0
votes
1answer
45 views

Google Apps Script slow applying sheet/range protection

I am building a set of spreadsheets using Google Apps Script. Each spreadsheet is being created with a set of 10 sheets. Now that the all seems working, I have added some code to protect ranges, ...
2
votes
2answers
50 views

How can I filter a list based on whether it contains any of a list of substrings?

I have a list of Japanese words. I want to produce a formula to filter that list, based on a filter list of substrings (kanji characters) which I'm looking for. So for example, say my filter list ...
2
votes
0answers
19 views

IMPORTRANGE, where range shifts often, to different spreadsheet

What I have: a client information spreadsheet that contains company information including their last order date following the status of their account (active/follow-up). What I need: the last order ...