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
0
votes
1answer
13 views

Summing groups, Aggregate costs by items in groups

I have a sheet with a grouping by Feature / Subfeature / Task. Here is an example: How can I aggregate the cost from individual tasks to subfeatures (eg. rows 147-150 of Sign-In subfeature group) and ...
5
votes
4answers
21k views

How can I get GoogleFinance to see 1 year and 5 year return of a stock?

You can use GOOGLEFINANCE("name", "return52") for mutual funds, but not for stocks. Is there another way to get the same result?
0
votes
1answer
119 views

Best way to change dates in Google Sheets in all the tabs?

I have a sheet that is for accountability in my business, I have each month with it's own tab with five week sections and the dates are done by a Monday thru Sunday sections with the date that is ...
1
vote
1answer
975 views

Temporarily disable autosave in Google Sheets

I use autohotkey scripts for a lot of things in Google Sheets. It's basically scripted, automated keyboard input. A script may look something like this: Loop, 4 { Loop, 7 { Loop, %...
0
votes
2answers
28 views

Google Sheets IMPORTXML Issue about Currencies

I've used these link and formula on Google Sheets to auto-update currency: =IMPORTXML("https://tr.investing.com/currencies/gau-try";"//*[@id='last_last']") However, it doesn't ...
0
votes
1answer
27 views

Google Script Substract Function

I want to create a script function that I will use with daily triggers: function will subtract "Daily Fees" (a cell value that changes daily from "Open Trades" sheet) / from "...
0
votes
1answer
17 views

Counting a cell if another cell has certain strings

Have a cell which will either have W, L or BB in it. I want to add the cell in O6 if the N6 cell contains any of the strings mentioned below what is the code for this? below is what I currently have ...
0
votes
1answer
11 views

Colour scale based on a changing value (Todays date - Entered Date) in google sheets

I'm trying to make a colour scale based on Today() between another entered date, basically a scale that starts green to yellow to red. I've attempted to try For green: =DAYS(TODAY(),$I2)=0, For Yellow ...
0
votes
0answers
13 views

Is there a way to add up the cost of multiple different items but not all the items in Googlesheets?

Im very new to this so please dont mind if its stupid Im making a spreadsheet to manage your army in a game and I'm trying to add up the cost of different kind of divisions (Infantry, Sniper, Tanks, ...
1
vote
2answers
57 views

Count how many times a certain word comes before and after another in a list

I am trying to evaluate some information from an animal studying exercise using Google Sheets. I'd like to know how many times the animal: forages then allogrooms sleeps then allogrooms etc. Also ...
0
votes
1answer
117 views

The cell is filled with $0,00 but IF(Cell >0;True;False) return True

In Google Sheets, when I compare a Cell filled with $0,00 using: CELL [$0,00] IF(Cell > 0; true; false). It returns true, but Cell is = 0. It should return false. It seems to be related to ...
1
vote
1answer
49 views

Aggregate data to show text in one cell

I need to put together multiple entries into one where the results is text and has to be in one cell. From this to this
0
votes
1answer
49 views

Add fields and update array and scripts

I want to add some questions to my Google form but my response sheet has a script and an array, which do not like that. Here is what it should look like: The URL is the link for the form. Here is ...
0
votes
1answer
16 views

Calculate specific contents of a cell that contains a string of numbers and text

I have a sheet that contains cells that have lists of data within them, for example: One cell contains: 1xExample, 2xAnotherExample, 5xThirdExample The cell below contains: 1xAnotherExample, ...
0
votes
1answer
46 views

Google Sheets Conditional Formatting Cycle

In Google Sheets I would like to color a cell depending on if the cycle is on or off. The cycle is for 10 day the cycle is ON then for 5 days the cycle is OFF then repeat. So if I start the cycle on ...
0
votes
1answer
25 views

Using IMPORTXML in Google Sheets: How to Combine XML Data Sources as a Single Set Before Applying XPATH Query

Using the IMPORTXML function on Google Sheets, I've got two XML data sets at two URLs, respectively in cells A1 and A2. I've got the XPATH query in cell H1. I would like to run a single query on the ...
0
votes
1answer
18 views

Merged cells not preserved when copying and pasting

I wrote a script to copy and paste a block of cells from one sheet to another in Google Sheets, and everything is working fine except the merged cells are no longer merged after the cells are pasted. ...
0
votes
1answer
9 views

Convert Historical Currency in Multiple Rows with Preset Date and Amount

I downloaded some data from PayPal and they report certain transactions in the destination currency (PHP), but I need to get the currency I sent it in (USD), however, because these are historical ...
0
votes
1answer
34 views

How do I chart timeseries data organized by one of the columns?

Here's what seems to be a simple common thing in excel/sheets that I can't seem to be able to google in the right way. three columns: timestamp, email, things_done. I want a chart with time as the X ...
0
votes
1answer
23 views

Is there a way to embed a specific cell from a Google Sheets to Google Docs?

I want to fill in a sentence with blank spaces on Google Docs from information that I can provide from Google Sheets. Is this possible? "For my student number X = [student_number], the required ...
2
votes
1answer
37 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 ...
1
vote
1answer
16 views

Round up duration calculation to the next multiple of five minutes

I currently have this formula: =text(D2 - C2, "hh:mm:ss") I need this formula to round up to the nearest 5 minutes. I have tried mround(), ceiling() and roundup(), but I'm not getting the ...
0
votes
2answers
214 views

Arrange Google Form Checkbox Responses into a List

I am collecting scholarship recipients in a Google form. The recipients names are in a checkbox format, as some donors give to multiple students. I need to separate out the names into additional rows, ...
0
votes
0answers
10 views

Google Sheets - SUMIF Contains

how are you doing? I need to aggregate the total amount of money spent in campaigns that are divided in different sub campaigns. Just like this Column A Campaign - 1 - A Campaign - 1 - B Campaign - 1 -...
2
votes
1answer
31 views

Using multiple IF and OR statements with calculations

I'm trying to calculate prices for items that have various costs. Depending on what that cost is determined its multiplier. I'm hoping I can create a formula in a spreadsheet to streamline my process. ...
0
votes
1answer
25 views

Google sheets sum cells if corresponding date is in range of a month

I have a sheet in google spreadsheets with every business day in one calender week and the working hours of the staff. Now I am supposed to create a sheet displaying the salary of the employees for ...
1
vote
1answer
125 views

Can I record the users within a Google sheet?

I'd like to have an auto-generated list of users with timestamps within a spreadsheet, tabs, or if possible specific cells. Ideally, once a cell has been altered, I'd like another cell to record the ...
0
votes
1answer
2k views

How to organize and add multiple tags to a list?

I'm trying to create a sort of "goal bank" and I'd like to organize it so that you can search for goals based on various categories (i.e. diagnoses [autism, mental health, etc.], domains [social ...
0
votes
0answers
14 views

Google Spreadsheets: Display static text on chart

I have a line chart that I've created in Google Sheets. This line chart is published to the web and embedded in another website as an iframe. I have a cell in the underlying data that updates with a ...
2
votes
1answer
65 views

How to pass multiple comma separated values in a cell to a custom function?

Suppose, there are values such as 1,A, 2,B, 3,D in certain cells. My intention is to add 1,2,3 (LHS) separately and A,B,D (RHS) separately. To achieve that, I have written certain custom function in &...
0
votes
1answer
45 views

In Google Sheets, how do you use their Fill Down (dragging formula thing) without copying cell styles / formatting?

Cannot find this anywhere I look. Lot of SEO spam on Google Sheets. I have a table that has a ton of different styles: Header small font BIG FONT bold font italic font The "small font" ...
0
votes
1answer
13 views

how to remove sum label from query

=QUERY(INDIRECT(C13&"!$C$5:F"), "select Sum(F) where D='"&$E$12&"' and C>=date '"&TEXT($G$4,"yyyy-mm-dd")&"' and C<=date '"&...
0
votes
0answers
12 views

Help formatting data on sheets from google Forms

I am trying to format the sheet so that all the "items and quantities" list vertically for each time stamp. I have manually edited how i want it to look under the raw data https://docs....
0
votes
1answer
13 views

Query function with variable in the Col1=

Here' the formula: =query({{$P$3:$P$34;$R$3:$R$34;$T$3:$T$34;$V$3:$V$34;$X$3:$X$34;$Z$3:$Z$34;$AB$3:$AB$34;$AD$3:$AD$34},{$Q$3:$Q$34;$S$3:$S$34;$U$3:$U$34;$W$3:$W$34;$Y$3:$Y$34;$AA$3:$AA$34;$AC$3:$AC$...
0
votes
0answers
9 views

Reorganize csv data

I work in a school that has multiple data entry forms and formats and I am trying to find a way to automate one example of data duplication. We put student report card grades in on one online system, ...
0
votes
1answer
2k views

How to use calculated fields to pull up "Grand Total" in Google Sheets?

I have a grand total value that I want to use in my calculated field. Instead of using a specific number like =sum(impr./919), I want something like =sum(impr./"grand total") and that the grand total ...
1
vote
1answer
3k views

Is it possible to paste local images in Google Spreadsheet?

I am wondering if this is possible in Google Sheets. I am trying to create a visual database of products in the form of a shared spreadsheet. The images cannot be online for privacy and trade secret ...
0
votes
0answers
13 views

Using a vlookup to pull from a table of a team min and max values for specific stats

So I am working on creating a stat sheet and learning a lot as I go along and using a lot of different tutorials and videos to learn how to write my own formulas, but I seem to be stuck on this one. ...
0
votes
1answer
10 views

Export Google Sheet

I have a Google Sheet that includes custom App Script. I need to: Download a copy of the sheet. Zip up the sheet. Send it to another user. Is there a way to do this in Google Sheets? For privacy ...
0
votes
1answer
12 views

Insert data from other column into each row's respective number

Really new to sheets and what I'm trying to do this: I have this formula =CONCATENATE("<option value=")&("""")&A15&("""")&("&...
0
votes
0answers
8 views

Using Google Sheets to solve client scheduling problems

I am a small business owner who works by appointment with a fixed number of clientele. Clients come in at different recurring intervals (some once per week, every 2 weeks, every 3, etc) and for ...
1
vote
1answer
136 views

How do I copy formatted formula results in Google Sheets?

I have formula cells outputting numerical results that are then formatted with a " km" (kilometres) post-fix. For example, the formula returns 21.4, which is then formatted as 21,4 km. I ...
0
votes
1answer
21 views

Google Sheets Formula in Series

How to Formula A2=1 B2=2 C2=3 if all are in series (123) it must be TRUE If A2=2 B2=3 C2=2 not in series it must be False
0
votes
1answer
35 views

Where it goes the paper size information in a script

I'm using this scrip to save to pdf but I need to make it Size A5 and Landscape Orientated I have found some parameters but I don't know how to combine function checkSheet() { var sheetName = "OS&...
0
votes
0answers
12 views

Google sheets, scale Axis to Date, not to item

So i have a simple line chart, it plots a value for each entry into the table it is made from. Each only has 2 columns: DateTime, and Value The value is cumulative, so you expect and upward trending ...
2
votes
1answer
34 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:
0
votes
1answer
20 views

Protect identical ranges across multiple Sheets

I currently have a multitude of docs each containing 30-60+ sheets. Each sheet is formatted identically with the same headers/column labels. In doing research I found its not possible to mass protect ...
0
votes
0answers
13 views

GSheets : How to find all GOOGLETRANSLATE formulas executed or not from external means? [closed]

I am developing a data processing app using Google Sheets and its API as backend . In that I will upload all datas as arrays with GOOGLETRANSLATE via batchUpdate API like below [hello_word,"Hello ...
0
votes
1answer
1k views

Auto-populate cells based on previous cell values in Google Sheets

I have two columns with information. Values in Column B have been manually added to match the values in Column A. Is it possible to have a function that auto-populates column B with the right values ...
0
votes
0answers
13 views

Google Sheets Pie Chart - Display Actual Value Below Label, not in pie chart?

I know I can get the actual value to show up in the pie chart, i.e. on the slice. However I want the percentage in the pie chart (which I can get to show up) since it is more likely to fit in a slice ...

1
2 3 4 5
152