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

Using a cell to fill out/finish a formula

So I am currently making a calculator, but need to make it as simple as possible. Currently I have it like this: =IFERROR(query('Calculator Data'!B:H,"Select F where C='105 Kira Tusami'", &...
0
votes
1answer
59 views

Get URL parameter from a Google Sheets URL and pass into a cell

I am trying to pass the value of a URL parameter into a cell in Google Sheets - e.g: https://docs.google.com/spreadsheets/d/3dfedsfc--sdfwefwefewe/edit#gid=29034359&team=frogs I would like to get ...
1
vote
0answers
37 views

Use ArrayFormula to automatically update the column with a formula already containing an ArrayFormula

I am using the below formula to concatenate some data into one string: =TEXTJOIN("",FALSE,ARRAYFORMULA(IF((Data!$F$3:$F=$C2)*(Data!$G$3:$G=$D2),Data!$L$3:$L,""))) I however have ...
0
votes
0answers
7 views

How to update user security policies in GSuite for Google Sheet purpose?

I'm trying to have an "Updated by" cell for each row edited on a Google Sheet. Supposedly, I can do SpreadsheetApp.getActiveSpreadsheet().getName("Sheet 1").getRange(r.getRow(), 16)...
0
votes
1answer
24 views

Calculating the modulo of large numbers in Google Spreadsheets

For an IBAN Validation, I need to calculate the modulo of a very large number. What I am doing is simply Example: with A1 = 210501700012345678131468 =mod(A1;97) The result should be 1. Instead it ...
0
votes
1answer
13 views

Google Sheet to auto fill weekday dates

I would like to create a Google Sheet whereby the dates of the data will change weekly, and showing only the Weekdays if possible. For example: This week the columns will show 11th Oct, 12th Oct, 13th ...
0
votes
1answer
29 views

Google Sheets getRangeByName isn't working after duplicating sheet

I use the following function to duplicate a sheet, and it works great. function duplicateDataSheet() { const date = getTodayDateAsString(); const newDataName = `${date}_data`; const ...
0
votes
2answers
23 views

HELP Needed for Syntax to use IF Statement Included in Query (Google Sheets)

I am trying to create a query to pull ALL reports available in the referenced table but then limit down the reports when options are selected. The formula I have so far is: =QUERY('Common Standard ...
-1
votes
1answer
32 views

How can I conditionally format this Google Sheet with multiple IFTHEN

I'm collaborating on a Google Sheet with approximately 500 rows. I think potentially could use IF, ISBLANK and conditional formatting but I can't conceptualize a formula for this and I've tried ...
1
vote
1answer
20 views

How do I write a Google Sheets Query where I can use the user's input (cell reference) to determine the

The goal of this sheet I am working on is for someone to select the items they want on a report (I created a dropdown with data validation), and based on the options the user select, THIS will be the ...
0
votes
1answer
12 views

Display Google Sheet Default Date Value as N/A

I have a cell (D1) in which a user can enter a date. I have a second cell (B1) that takes the date from cell D1 and adds 10 days to it and displays that date. The user may not need to enter a date in ...
0
votes
1answer
18 views

Sum of rows in one column based on values in adjacent column

I need to sum a range (D20:D22) but exclude any values in an adjacent column (G20:G22) where the Column G values is <-2. The resulting value is the divisor in a second calculation. For example: ...
0
votes
1answer
39 views

How to get list of values in one column for distinct values in another column

I need to do some analyses on a set of data for work and cannot figure out how to do this. I am familiar with Google Sheets QUERY, but could not do what I want and could not find anything like it ...
0
votes
0answers
11 views

Adding the contents of another cell then clearing it [duplicate]

I'm trying to get a cell from any row in column J to update the value of the E cell in the same row but cant quite figure it out. How i want it to work is someone can come along and add a quantity i....
0
votes
1answer
26 views

Keep copy of all data when the data updates periodically

My sheet gets updated automatically every five minutes. How can I save the previous data, i.e., data just before each update, so that I can keep all data. I also want to make a line graph which ...
0
votes
1answer
42 views

Query Divide by Sum Column getting Error in Google Sheets

Looking to work around with error message "Add_col_to_group_by_or_agg:Col1" from formula below: Query(Query({data range},"Select etc."), "Select (Col1*Col3)/Sum(Col1)",0) ...
0
votes
0answers
19 views

Change aggregation type for Grand Total in pivot table (ie, to SUM)

I have a pivot table based on the MIN value. That is fine. But now, the so-called "Grand Total" is also just showing the MIN of the columns. It seems it just sticks with the aggregation type ...
0
votes
1answer
23 views

HELP Sneaky Notifications in Google Drive (Sheets or Docs) document - not made/changed by me

So, is there a way to be notified when you make a change? Can you please enlighten me and help? Thank you Because people share password, it is possible for someone with your password to make changes ...
0
votes
1answer
34 views

Google Sheets: One Conditional Formatting Color Scale Rule, for multiple independent rows at once?

Is there a way to use one single color scale conditional formatting rule for the whole sheet, that will separately compare only the values from each independent individual row? It seems that this can ...
0
votes
1answer
25 views

How can I link/stick a range from Sheet1 and a column from a Sheet2?

I have a spreadsheet test here. My project is a bit long (1000+ rows) but I created something that works in a similar way. My hypothetical situation: I receive grades from my students via Google Forms ...
0
votes
1answer
12 views

Google Sheets - Preserving Data after Importing Range

I work in an organization where we give people jobs across 3 of our programs. I am trying to create a spreadsheet that can have a master list of all of our participants and which program they are in (...
0
votes
0answers
22 views

Short number format in Google Sheets in Indian format (Lacs, Crore etc); 1,53,642 → 1.54 Lacs, 28,18,76,724 → 28.19 Crore

How to truncate large numbers in Indian format? This question answers it perfectly for the US system. The custom number format for Million and Thousand is: [>999999]0.0,,\M;[>999]0.0,\K;0 Need ...
0
votes
1answer
23 views

Google Sheets Query - Divide by Sum(Col), Error

I'm doing a simple query but keep getting the error "Add_col_to_group_by_or_agg:Col1". The formula is =QUERY({L42:N63}, "Select (Col1*Col3)/Sum(Col1)",0) What I'm looking for is ...
0
votes
1answer
26 views

How to return the next month from current month?

I'm trying to return the nextMonth(), but currently both of the following functions I've made don't work. //this one returns a string of "October1" instead of incrementing by 1 function ...
0
votes
1answer
36 views

How to append a cell value to a certain column ("D2:D")?

Currently the function only allows me to get a range and append it to the "Data" sheet. But I have no way of appending that value from "Sheet3" to another column for instance "...
0
votes
1answer
7 views

INDIRECT for dynamic inputs for IMPORTRANGE in Google Sheets?

I'm trying to aggregate data across several different Google sheets using IMPORTRANGE (or a similar function). The challenge I'm having is I need to reference a dynamic sheet name. I'm importing an ...
0
votes
1answer
11 views

Adding Text before a Date in a Cell

How can I achieve "Current Date: <Today's Date>" in a cell in Google Sheets? The "Today's Date" part must update dynamically every day.
0
votes
1answer
28 views

How can I insert multiple empty cells, if a certain criteria is met?

I'm currently stuck trying to figure this out. Basically, I'm trying to insert an amount of empty cells only when cells from A2:B2 return a value. I got it to work where if both cells are empty one ...
0
votes
2answers
25 views

Two scripts running simultaneously in the same spreadsheet disturbs each other?

Script 1: Scraping data from a website and puts the new collected data into Column A and Column B. function scraping() { var sheet = SpreadsheetApp.getActive().getSheetByName('PageA'); var url = '...
0
votes
1answer
18 views

How do I use a drop down of sheet names to change the data pulled using the FILTER function?

I am creating a performance tracker for my employees, and I'd like to have a sheet named for each person's first name, with all the metrics by date. I'd then like to create a performance page that ...
0
votes
1answer
25 views

How to count the number of times a certain label appears within a date range using Google Sheets?

I often have this problem and haven't found a good solution. It drives me nuts! I have a set of data with 1000s of rows and each has a date and some label -- might be salesperson, category, region, ...
0
votes
1answer
12 views

How do I copy and paste a pattern of a few cells in a column down the whole column?

I have a column with the first top-six cells as: column D1: Total days worked column D2: (formula for total days worked) column D3: empty cell column d4: Total Hours Worked column d5: (formula for ...
0
votes
1answer
45 views

Get list of Google Drive folders into Google Sheet

I am trying to be a little organized, and want a way to get a list of all folders in my Google Drive in Google Sheet. It should not have the Files, only folders. I was able to find following code, but ...
0
votes
1answer
10 views

Sort out values not in range

I have a range in my spreadsheet in which each of the cells may contain a number between 1 and 12. In a different area of the spreadsheet I want to list all the numbers between 1 and 12 which does not ...
-1
votes
1answer
26 views

How to add multiple pre-created values to the same cell in google sheets

With google sheets if you add data validation, you can select a single value from a predefined list of values (or from another specified range of values). This makes data entry a lot faster and more ...
0
votes
1answer
20 views

How can I get working formulas to convert quiz data into text fields without errors?

I’m trying to work out a simple way to generate some text-based responses from sheets that are derived from some basic field entries that come in from Gravity Forms. I feel it should be quite simple, ...
0
votes
0answers
16 views

Sorting cells into past and future

I have a google sheet with the first column giving the dates of particular events. Ideally, I'd like to have two side by side versions of the spreadsheet, one with cells before todays date and one ...
0
votes
0answers
19 views

Colour scale for many rows independently once

I found a solution to colour scale many rows independently using the below script that I found in an answer from Oren Pinsky (thank you!), but can't figure out how to add the selected ranges in the ...
0
votes
1answer
17 views

How can I get a list of people that don't have a number in their column?

I have a Google Spreadsheet with a list of jobs of a group I'm part of. In it I have the information of when was the last time a person did a job and how many times he/she has made it. I want to be ...
0
votes
0answers
26 views

Referencing three or more ranges and combining the dates that result

I have this formula that I made that takes a range from a cell (in this case, 10/5 - 10/11), subtracts the dates from that range from a larger list of all remaining days in the year, giving me a list ...
1
vote
1answer
23 views

Sorting consecutive dates into groups

I have a list of dates. The list has chronological gaps in it, but I'm looking to bring it all into a single cell of date ranges separated by commas, organized by consecutive dates. Example of the ...
0
votes
0answers
18 views

change sharing permissions in google web app code

So I have a bot on google drive that among other things sorts files in folders. In the partial code below they are looped and placed in PASS or FAIL folders. The problem I have is that the files get ...
0
votes
0answers
13 views

Using Google Sheet on Samsung Tablet S7

I have no physical keyboard Just S pen The toolbar at the top of my sheets never looks like the toolbars I see when I watch videos about Google sheets !!! I am missing some buttons. Like the View ...
0
votes
1answer
19 views

With Google Sheets, how to have entered data automatically sent to a specific range group?

I need to fill these price ranges with 100 products each: $10-$20 range, $15-$25 range, and $20-$30 range. You can see the ranges overlap. When I have a $17 product, I've been manually copying-and-...
0
votes
1answer
23 views

G Sheets - Can't seem to make not contains and is not null work together

I want to return results for col A & B where B is not blank and where F doesn't contain any city in California so, ', CA'. I can get each operator to work separately, =QUERY('805'!A1:F1105, "...
0
votes
0answers
28 views

Fill in one cell if another has had a date selected and move the row when the date is selected and subjected to an onEdit script

I have built a Google Sheet with a lot of data. I have also put in a script to sort data automatically, by date, when new information is input. The new information can relate to past, present or ...
0
votes
0answers
14 views

Conditional content in column A based on value in column B, with content determined by column C

I am trying to automate a task where the contents of one column is informed by the (text) value in another column. The content should be pulled from a list in the spreadsheet. For example: In this ...
0
votes
1answer
57 views

Script in Google Sheet to automatically run after importing CSV files to replace current sheet

I'm going to admit I'm a newbie at scripting. I've managed to write the script that I want but I want it to automatically run after importing the CSV file onto Google Sheets. I have tried a simple ...
0
votes
1answer
50 views

google sheet how to import cells based on value in different cells

So I have two sheets. Sheet A tab X contains two columns, first one with an url for a google doc and in the other column text IDs. Sheet B tab Y contains a column with text ID of several more files ...
0
votes
1answer
59 views

Percentile Conditional Formatting in Google Sheets - Copying the formatting but isolating the range

I have setup conditional formatting using a green-red percentile color scale. This works fine until I copy this section down the sheet. When I do this, the numbers in the second section affect the ...