Web Applications

Questions tagged [google-sheets]

Ask Question

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.

5,729 questions
Newest
Active
Bountied
Unanswered
Filter by
Sorted by
Tagged with
0
votes
0answers
17 views

Dynamically retrieve entire row in Google Sheets for computation

Objective:I want to retrieve the content of the last row of a Google Form and do a simple computation with that and later return that value to the user. Example: The form contains two questions: "How ...
0
votes
0answers
25 views

How do I convert a number on one column to another number on the next column in Google Sheets?

Here is the range of hours I am trying to convert to days. 0-2 days equal 5 hours 3-5 days equal 10 hours 6-10 days equal 15 hours 11-15 days equals 20 hours In the picture, the highlighted column (...
0
votes
1answer
9 views

Google Sheets - conditionally format a cell based on whether the content of the cell appears in a column from a different sheet?

I have a sheet called Master that contains a column of names in cells A1-A60 (e.g. A1: Ann,A2: Bob,A3: Chris, A4: Donna, etc.). I have another sheet called Subsheet that contains names that may or ...
0
votes
0answers
9 views

Select certian coluumns from Table A when a certian cell combination is not present in Table B

I am currently using the QUERY function in Google Spreadseets to select columns from different table. I have two tables (TableA and TableB). I want to SELECT * when the combination of Col1 and Col2 ...
0
votes
0answers
12 views

How can I pick an item given it's range, from a number within that range

https://docs.google.com/spreadsheets/d/1GoaRd0xJZLPPaXJhmcfEQgW_GNsQyyy_Xa51G-nixqc/edit?usp=sharing Please view the spreadsheet. Essentially I have a list of albums, with a rating. What I want to do ...
0
votes
1answer
18 views

ImportXML error: 'imported content is empty' when selecting for text within a list item

I want to scrape a text within this website in google sheets with the IMPORTXML function. Through dev tools by inspecting the text in question, I get the corresponding HTML: <li _ngcontent-udl-c4="...
0
votes
0answers
23 views

Conditional Formatting on Google Sheets using regex

I am now going in circles, I tried to look for answers here but I am still stuck. I want to highlight a cell using conditional formatting if the value of that cell is present anywhere in a specific ...
0
votes
1answer
11 views

How to copy range from one sheet to another column with offset

I have a range of data on sheet5 which needs to be copied and inserted 27 columns in from the end of sheet1. I can get it to insert the column in the right place but it doesn't copy the data. I am new ...
0
votes
0answers
21 views

If cell contains certain text then another cell calculates a number

I have a budget spreadsheet with two sheets. First sheet is titled "Summary" and the second "Transactions," where I log financial transactions. I want a cell (Q18) from my first sheet to return the ...
0
votes
0answers
13 views

Google Sheets: auto-fill with increment to filled cells

So basically, what I'm trying to do in Google Sheets, is: I have a column called 'Name' and a column called 'Badge number'. Badge number consists of 2 parts: category and increment number. For ...
0
votes
0answers
10 views

stock optimization using covariance matrix using Markowitz theory

I'm trying to use a template to see what it says about my stock portfolio. It uses a covariance matrix with the data from excess returns. The template instructions say to use an add-on to calculate ...
0
votes
1answer
18 views

Sheets - Determine Cell Number with Equation

I'm trying to reference a cell by the following equation: =A(51-(48-(COUNTIF(Info!A5:A52,"*")))) A51 is the last possible cell that information could be in (hence the 51). 48 is the total number of ...
0
votes
0answers
13 views

How to setup Google Sheet to import data to a new row when requirements are met?

So I have a Google Sheet I've made at work to simplify our needs for receiving product, the old sheet was a template and people handwrote everything which leads to confusion for people putting the ...
0
votes
1answer
28 views

If then with a checkbox

I have a Google Sheet. I am trying to do an if then statment. If cell C8=True then I want it to perform a function. I know this is not right but wanted to give you guys example of what I am writing ...
1
vote
1answer
38 views

How to query on a Google Sheets to sort by month

I'm using this Sheetgo Attendance template on Google Sheets, but it's designed for US Schools so it's needed to sort by January to December. I will provide below the assets that I've have about it. ...
0
votes
0answers
3 views

Sorting by week in google sheets Analytics add on

I just want to run a simple weekly traffic report with the Google Analytics Sheets add on. It does work fine, but I can't seem to figure out how to sort the weeks in chronological order with the jump ...
0
votes
1answer
19 views

Fix my script? How can I automatically create events in google calendar using dates from google sheets?

I read that it is possible to link a google sheet to google calendar, so dates can be used to create events in the calendar. I used script I found around about, but absolutely nothing happens. Can ...
0
votes
0answers
20 views

Print First Modified TimeStamp of edited Cell in a Column range [duplicate]

This code by @Ruben prints out the timestamp for the first modification for A1 into B1. How to do the same but for the whole column so A2:A printing results into B2:B? // Global variables var origin =...
0
votes
0answers
13 views

My function links all required cells between google sheets except one!

I have this query =query(IMPORTRANGE("<https://docs.google.com/spreadsheets/d/1uxIkcjOTxFAd25Ukzji3VJKoFbAKybuFXfHlBzaGDWc/edit#gid=1385200762>","Master!A3:y"),"select Col2, Col3, Col12, Col13"...
0
votes
1answer
15 views

Variables in Google getRange

I have a simple need to copy a range (E4:G4) and paste it X rows below, determined by the value of a cell, C2. I think I may be misunderstanding the offset function since when I run the routine, I get ...
0
votes
0answers
16 views

Output word within a string of words, if it matches to a value within a named range

I have a list of search queries for cars like: hyundai ioniq hybrid rav4 hybrid hyundai ioniq electric toyota yaris petrol vauxhall automatic car I also have a list of models of the cars (ioniq, ...
1
vote
1answer
16 views

Sum depending on value of Column B and Date in Column C

Column A: Name Column B: Date (mm/dd/yy) Column C: Value I want to sum column C every time column A is a specific name & the corresponding date is before 2020 Small sample/Example of what I'm ...
0
votes
1answer
8 views

How to convert datetime string to datetime value in Google Sheets?

Let's say cells A1 and A2 have these values: 13 Jan, 02:21 am 12 Jan, 10:15 pm And many more values like those below in that column. What formula could I use so that the B column would look ...
0
votes
0answers
29 views

Need some help inserting break to prevent “Exceeded maximum execution time” Google Sheets script

I'm using a Google Sheet to populate drop downs in a Google Form. There's not a lot of info to be transferred as such, but it's repeated 76 times... I'll paint the picture first, so you have an idea ...
0
votes
1answer
16 views

Google Sheets Formula - Trying to Sum, based on multiple cell criteria

I am looking for help with Google Sheets formula. The formula I need is - If A:A=LG and F:F=Indexed then sum the total of P:P for all of those instances. If A:A=PG and F:F=Indexed then sum the ...
0
votes
0answers
7 views

Can you lock formatting rules? [duplicate]

I have set up a sheet as a rota. It uses a few conditional formatting rules to highlight things that might need attention, like if a cell is empty and if a date is within the next 2 weeks. However as ...
1
vote
1answer
20 views

How to see all custom formatting rules in a sheet, not just those applying to current selected cells?

As far as I can tell, in Google Sheets you only see a list of formatting rules applying to your current selection. Is there a way to see a list of all rules that exist in a sheet? When people copy-...
0
votes
1answer
16 views

Is accessing multiple Google Sheets Files(not multiple google sheets within the same Google Sheet File) in one script possible?

I was wondering if I can copy a certain range of cells in Google Sheet File A and paste it to another Google Sheet File B. More specifically, I'm referring not to sheets in the same Google Sheet(eg: ...
1
vote
1answer
25 views

How do I create a pie chart in google sheets without a category or two from my data?

I have a list of data, showing 4 items. Cats, dogs, giraffes, eggplant. I want to create a pie chart in google sheets that shows the pie with just cats, dogs, and giraffes. I don't want to show the ...
0
votes
0answers
8 views

Google Sheets: Histogram of time data (axis numbering)

I've tried making a histogram of my data, which looks like so: 12:00:02 PM 3:00:00 PM 12:30:00 PM 12:30:00 PM 2:00:00 PM 3:00:00 AM … I highlighted the data and selected insert > chart > ...
0
votes
1answer
14 views

Google sheets - tying a numerical value to different texts

I am incredibly new to google sheets and have a basic question, that I can't seem to find an answer for on the internet. Is it possible to count up multiple different texts into one cell, with ...
0
votes
1answer
17 views

Making a chart in Google Sheets

First time poster and currently getting familiar with Google Spreadsheet with using some books and information on the net. However, I couldn't put into words what I was trying to do so I found this ...
0
votes
1answer
17 views

Mass-remove empty sheets in Google Spreadsheets

I have created an interactive search spreadsheet that filters columns based on the user input and the data from another sheet. Since the user needs to be able to enter values in the search cell, I had ...
0
votes
0answers
21 views

INDIRECT cell reference - Can't copy cell as reference cell pattern remains same

I have an Overview Sheet where I want to display a sum of values from another sheet. I'm using the indirect function to grab the sheet name from a cell in the overview sheet. The formula looks like ...
0
votes
1answer
44 views

applying a formula to the data from google forms

I have a google spreadsheet which is connected to a google forms I wanted one formula to be automatically copied to the new lines(Column R) when google form response is submitted the spread sheet: ...
0
votes
0answers
28 views

Google Sheets - Multiple outcomes using If And statements?

I'm working on categorizing tires and rims that I own. I want the sheet to look into the "Width", "Ratio" and "Rim Size" columns to provide me with data to determine which car it would fit on. For ...
1
vote
1answer
95 views

Google Sheets calculate average on variable number of non-adjacent columns?

A Google Sheet is being used to insert markings for students tests. When a single test is done by students, teacher assigns multiple grades for it. For instance, one marking for writing, one for ...
0
votes
1answer
14 views

How to extract digits only from formatted phone number string?

I'm getting user submitted phone numbers in a variety of forms, for example (fictional): (423)700-4000 9002570000 703-203-3000 I want to extract just the numbers so that I can upload them to an app ...
1
vote
1answer
29 views

Create an error message if certain cells are empty

I've been messing around with a spreadsheet for a while now and got it mostly working besides one part. I have two columns, if a cell in the first column is "1", but the cell in the same row but ...
1
vote
1answer
39 views

Friendly Team vs. Enemy attack math and complicated functions in Google Sheets

How would I go about writing a function in Google Sheets? If Friendly Team Hero Power P2,P4,P6,p8 etc. is greater then Enemy Team Power "T2:T15" Else Don't match it and instead look for titan, Same ...
0
votes
1answer
25 views

Google Script Fails to Return Boolean Value

I want to create a simple function that returns a boolean value when the word "website" appears in a specific cell. When I run the debugger, matches returns undefined. function myFunction() { var ...
0
votes
0answers
29 views

How to calculate this Weighted Average in Google Sheets

I am having troubling figuring out how to reproduce the values highlighted here representing weighted averages up until the years 2019 & 2020. Those values are just a weighted average, where the ...
0
votes
0answers
12 views

Google Sheets row automatically moving

I have a google sheet in which each row is labeled with a date (all dates, in order). I have an undated row that I want to appear immediately under today's date, so I want it to move down one row ...
0
votes
0answers
8 views

Google Sheets - Autocomplete cell but continue typing

If I'm working with a lot of long text and want to autocomplete the phrase "The United States Constitution" I can hit enter, tab, or an arrow key. But they all navigate me away from the cell. If I ...
0
votes
0answers
11 views

Google Sheets - Recognising sequences

I have a spreadsheet with a list of files and the majority of it is an image sequence, for example: ImageTest_0001 ImageTest_0002 ImageTest_0003 ... ImageTest_0385 ImageTest_0386 ImageTest_0387 Is ...
0
votes
1answer
13 views

How can I avoid INDEX MATCH on google sheets?

I find the syntax of index match convoluted, and difficult to read. (And in general I dislike nested functions. What workarounds are there when the lookup column is on the right of the answer column?...
2
votes
0answers
29 views

How to use COUNTIF and UNIQUE

I have a Google Sheet with two columns. One containing a list with order numbers - the order numbers can be listed several times/duplicates. The other column consists of the a free text with the name ...
1
vote
1answer
33 views

google sheets checkbox conditional formatting

I have a schedule where I use inspector name, job number and then checkboxes to book the inspector on the visit date What I'm looking to do is if an inspector is booked in a job on lets say jan 3rd ...
1
vote
2answers
58 views

COUNTIF with relative cell range in condition

I want a Google sheets formula along the lines of =countif(a4:a150;and(sum(e4:j4)<132;sum(e4:k4)>=132)) I.e., for each of the rows 4-150 (column A is not relevant), if the sum of columns E-J ...
0
votes
1answer
14 views

Move existing SUM downward when IMPORTRANGE expands

I'm building a complex Google Sheets document and for one sheet I use the function IMPORTRANGE. It's very handy and imports new rows from another sheet when they are added. The problem is this: After ...

1 2 3 4 5 … 115