Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

1
vote
1answer
37 views

Dynamic protection based on cell value

In my Google spreadsheet, I want to protect all cells below a checkbox if it's value is true and unprotect if it's value is false. Every cell in row 3 is a checkbox that this has to work for. Example ...
2
votes
1answer
24 views

Splitting numbers of a cell and adding them together in an ArrayFormula on Google Sheets

My question is how can I have digits of a cell added together in an array formula format, in that 12 | -> 3 //(result: 3 = 1 + 2) 14 | -> 5 46 | -> 10 11 | -> 2 53 | -> 8 And let’...
0
votes
1answer
105 views

Making select columns UPPERCASE

In relation to the post linked below, is there a similar script out there that will apply this same functionality to only certain columns in a Google Sheet? Convert all text to UPPERCASE in a Google ...
2
votes
1answer
45 views

Change tab color based on cell value that is a formula OR cell background color

1st time question asker, so I'm apologizing in advance if my question is worded wrong or doesn't make sense or is, frankly, to wordy (I tend to over explain). I have a sheet with, currently, 10 tabs. ...
1
vote
1answer
19 views

Is there a way to pull or search for data from other sheets?

We use Google Sheets for tech commission sheets (track jobs and hours). Right now we have 2 years (2017&2018) in 1 set of sheets, 1 sheet for each month (jan 17, feb 17, etc). So instead of adding ...
2
votes
1answer
20 views

What's wrong with this arrayformula?

=arrayformula(if(LEN(A:A),if(ROW(A:A)=1,"Outstanding amount Payment", sum(QUERY(daily_fee_collection!A:D,"select D where B="&A:A&"",-1))))) I am trying to cross-check for payments made in ...
1
vote
1answer
90 views

Is there a way to emulate Vlookup in Google Script?

I did some looking around and I'm struggling quite a bit in finding the answer. I'm working on a Google Script for Google Sheets that works like a Vlookup, where you define a cell for it to look at, ...
2
votes
1answer
28 views

Aggregate values based on categories and subcategories

I am trying to create a document which automatically tracks spending based on two categories. For example, I have a sheet formatted similar to the table below: Type | Amount | Category ...
1
vote
1answer
27 views

Cell format protection + forced capitalisation [duplicate]

I've created a Google Sheets document intended for daily use by more than a dozen people. Text inputted in two of the columns is set up, via conditional formatting, to change colour based on the name ...
1
vote
1answer
22 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 ...
1
vote
2answers
21 views

How can you find or highlight all cells having validation in Google Sheets?

My spreadsheet has several cells that have had validation applied to them, but it would be nice to recheck them or switch the validation to a complimentary cell. After searching on Google and looking ...
2
votes
2answers
29 views

How to calculate a monetary value for a given duration in minutes and seconds?

I have a monetary value for 1 minute like 0,10 € (10 euro cents per minute) In Google Sheets, I have a column to enter a length of time in minutes and seconds like "xx,xx", ex. 1,30 The calculation ...
0
votes
0answers
25 views

Have script code itself dynamically update based on value of cell in sheet?

I'd like to have the script coding itself change as I change the data in a sheet. For example, I have a script set to list the all the files in a particular folder as well as their ids. It spits them ...
0
votes
2answers
40 views

Safe SUM() in Google Sheets

When I use sum in Google Sheets, it: sums all numeric cells from the range (which is exactly what I want silently ignores all empty non-numeric cells from the range (which is usually what I want, ...
1
vote
2answers
96 views

Using QUERY and IMPORTRANGE to pull records from a specific date range

I have created two spreadsheets. This one is my source data: https://docs.google.com/spreadsheet_A And I would like this one to just be the people who are currently here (so Column D < today and ...
2
votes
1answer
15 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. ...
1
vote
1answer
19 views

How do I prevent or omit a negative value when calculating between times?

I've researched through the site to see if this has been answered but with no luck after reading: How do I calculate a time delta in my Google Spreadsheets? Calculating time difference Calculating ...
0
votes
0answers
20 views

How do I automatically import one CSV cell to a Google Sheet?

I am a bit of a novice as this will probably be the most complicated thing I have set up so please assume I know nothing. I have an app (App Usage https://play.google.com/store/apps/details?id=com....
1
vote
1answer
28 views

How to change many formulas at once in Google Sheets

I have many formulas, all that look similar to this: =Countif('2019'!$C2:$Z8,"2")/7 I would like to change each one, adding the if() command, so that they look like this: =IF(Countif('2019'!$C2:$Z8,...
1
vote
1answer
9 views

Max number in a column

I haven't been able to find an answer to this because I don't know how to word the search terms. How do I prevent a cell from going above the number 300,000? It is simply the sum of one column ...
0
votes
2answers
48 views

Can I auto populate cells in Google Sheets?

I have a bunch of categories; Bar Foo Ghost Misc Other All have variable amounts - except for category Ghost, the amount is always 15.00. I have Data Validation set up (which is how I pick the ...
1
vote
1answer
37 views

Query Importrange with multiple values

This formula is working fine, but I need to add in other teams into Column A. I would need to return ATT SLC, ATT Orem, and ATT Scottsdale within this same range, but I'm not sure the language to use ...
3
votes
1answer
30 views

What spreadsheets import from my spreadsheet?

Is it possible to get a list of the spreadsheets that are importing data from my sheet? I maintain a spreadsheet that has raw data, and many other spreadsheets use IMPORTRANGE to import that data. I ...
0
votes
0answers
9 views

sheet date copy

When you run a macro from the "A" sheet, you need a script that pastes the data from "A1:C1" to the "B" sheet "A:C". We want data to be stacked in a blank space every time we run a macro, whether it's ...
0
votes
0answers
7 views

master row with same style in all tabs

Is there a way to set a tab with a master row in it, and then I can reference that row in the other tabs and it will pull in both the content AND the style. The style has been the issue so far.
0
votes
1answer
21 views

How to make only one or a few cells publicly editable in Google Sheets?

I want to publish a tool using Google Sheets that should be as easy to use as possible, so I would not like the user to have to create a copy (requiring a Google account), then editing cells in that, ...
2
votes
1answer
16 views

Remove the false operator in Google Sheets

Here I am trying to add a random hexadecimal value every time a name is entered. It’s working but the other empty cells get filled with false. I want to completely delete the false part as it’s ...
0
votes
1answer
77 views

Setting minimum row height

In the past, I used to open a new spreadsheet, resize all lines (the default feels way too cramped for me), and then enter data. If at some point I entered data longer than the row height, it would ...
1
vote
2answers
51 views

Google Scripts (IF Statement + Vlookup Syntax Translation?)

Not sure if this is the right place to ask (I've been trolling around the web looking for the right place and after a few months decided to just throw my question out there), but here goes: I've been ...
2
votes
2answers
42 views

How to plot dates on a timeline in Google Sheets

I have a column of dates, eg. 2017-01-20 2017-01-25 2017-01-27 2017-01-30 2017-02-03 ... 2018-12-20 I would like to create a plot where the x-axis is a contiguous range of dates from start to end (...
1
vote
0answers
9 views

When importing a Google form submission to a spreadsheet, how do I automatically change the time format from “d-m-yyy” to “dd-mmm-yyy”

I am importing information from a Google form into a spreadsheet. It imports live. It creates a time stamp. There are also several other dates in the form (when you type it into the form there is only ...
3
votes
2answers
27 views

Any suggestions for conditional formatting for columns in Google Sheets, date & time specific?

I have uploaded 4.000+ Venmo donation data sets into Google Sheets for sorting, but the date and time is preset and looks like this: 2018-11-01T20:16:52 I have tried multiple approaches to sorting ...
2
votes
2answers
38 views

Google Sheets - How to extract words from a cell using regexextract

I have a spreadsheet with many rows like this: Arkonor 16m³Crimson +5%Prime +10%Flawless +15% Bistot 16m³Triclinic +5%Monoclinic +10%Cubic +15% Crokite 16m³Sharp +5%Crystalline +10%Pellucid +15% ...
0
votes
0answers
11 views

Continue from last modified cell

I have a large Google spreadsheet. The cell in which I have to work is in the last row of the sheet. Also, in this spreadsheet I have several sheets such as sheet1, sheet2, sheet3. I tried to find a ...
1
vote
1answer
22 views

Query Not Showing Certain Positive Values [duplicate]

I'm attempting to import some data to my Google Sheet. Using QUERY to reshape my data, an example code of what I am trying to import is as follows: =query(importhtml("http://statfox.com/cbb/cbbteam....
-3
votes
1answer
36 views

Possible scam using Google Drive

Is there any possibility of someone harvesting email address and/or Google profile info by sharing a link to a file on Google drive and collecting info on the people who view the file? I suspect a ...
0
votes
1answer
15 views

How do I create a “copy only” link in Google Sheets or Docs?

Oftentimes people will create templates in Google Sheets or Google Docs and have the first part of the document say something like this: BEFORE YOU MAKE ANY CHANGES, go to File > Make a copy... ...
0
votes
1answer
12 views

How to use vlookup or indirect to copy data from another sheet, without changing the value on update of database

So I've set up automatic MySQL sheet that gets the data from it every day at 8pm. I've set up everything with vlookup, and tried indirect. But I can't figure out how to make sure the values don't ...
0
votes
2answers
116 views

Having Google Sheets not recalculate a formula on page load

I have a Google Sheet with a list of addresses, and a column that geocodes the addresses with mapquest via Google App Script. This works great, except it "recalculates" the addresses on page load. ...
0
votes
1answer
22 views

How to define the cell to use with a math formula, as a function of current cell?

I want to create a formula which, once copied to the cell bellow, references three other cells from a column. So, for instance, look at this image: You can see that what I need is to define a dynamic ...
1
vote
1answer
32 views

Compare multiple cells for minimum value with criteria

I have a Google Spreadsheet for which I need to solve the following (I have tried using the MINIFS function, but it did not end well): A1: "0" B1: "100" C1: "1" D1: "150" E1: "1" F1: "101" A1, ...
0
votes
1answer
9 views

How do I change the year if I want to use the same spread sheet we used last year?

We have a spreadsheet we use every day. One for each month. In creating new ones for 2019, I can not get the date to stay correct. For instance it says Mon Jan 1 which is 2018, When I type ...
0
votes
1answer
20 views

Can I import data from web store directly to Sheet?

I have a spreadsheet that analyzes data uploaded into a sheet from a .csv file that is downloaded from my store for various operations and reports that I extract from it. I would like to know if ...
0
votes
1answer
25 views

How to perform certain calculations in Google Sheets?

Just trying to create a simple sheet to track my weight loss. Here is a link to my sheet so far so you can see what I'm talking about. Weight Loss Sheet So far I have gotten the C column working ...
0
votes
1answer
19 views

Enter Tab Name In Cell To Calculate Sum of Column or Range

I have seen one other similar question on this topic posted a few years ago, but I have been unable to have any success with the suggestions given there. I have sales sheets (a new one added each day)...
0
votes
1answer
10 views

Is there a way I can search some info in Google from Google Sheets?

I have a Google Sheet that contains information of companies and names, and I would like to have their job position. Is there a way I can look for it in Google without doing it manually?
1
vote
1answer
13 views

How to find out the total number, based on particular conditions

Let's say, I have a sheet with these 2 columns, where column A is for 'Remarks' and B is for 'Date'. 2nd and 4th rows are intentionally left blank to simulate that no Remarks and Date are provided. A ...
0
votes
0answers
6 views

Open a Google Sheet to the current week of the year

I am a teacher at a high school and we have a Google sheet calendar and each tab is a new week i.e. (12/17/18 - 12/21/18), so we have a tab for each of the weeks of the school year to keep our IEP ...
0
votes
1answer
19 views

Google spreadsheet mail as Excel attachment

I want to mail Google Spreadsheet as excel attachment, and also I want to write 4 to 5 lines in the body of a mail, and also I want to send this mail to many peoples some in "To", and some in "Cc" can ...
2
votes
2answers
21 views

Negate(NOT) used in IFS, but I get error

I try to use negate (NOT) in IFS, but it is not working, I get error. =IFS( NOT(TRUE); "aaa" )) How to fix it?