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.
0
votes
1answer
9 views
How to use IMPORTRANGE when I have multiple tabs?
I have to export some data from a Sheet with 30 tabs(a tab for each day of the months) and I have to extract data under specific criteria from all the sheets which have to be done every month on a new ...
0
votes
1answer
4 views
Set certain days of the week on repeat
I'm making an attendance sheet for my team, how do I make only certain days like Mon Tue Wed appear in the top column?
0
votes
1answer
5 views
Query Function Not Putting Consolidated Data in the Right Place
So I don't know how to explain this completely, but basically, I'm using the query function to consolidate data from multiple sheets into one Master Sheet. The function itself seems to be working fine ...
1
vote
1answer
6 views
Rotate and duplicate row values in Google Sheets
I'm trying to rotate values in a sheet and then duplicate the first value in the row as a header. For example:
a | b | c | d
would become:
a | b
-----
a | c
-----
a | d
Is there any way to do this ...
0
votes
0answers
11 views
Google Sheets Script - check if value exists before copy-paste
Can someone help please as I am new to Google Scripts.
I have this simple code that copies values from one sheet to another
function copyrevision() {
var ss = SpreadsheetApp.getActiveSpreadsheet ()...
0
votes
0answers
8 views
Change default zoom on a Google Sheet?
I use a shared Google sheet to display apparatus issues for a fire department. This is displayed on a tv using an Asus Chromebit. Every time someone logs into the sheet it resets the zoom to 100% ...
-1
votes
1answer
11 views
Why doesn't google forms recognize responses manually added to the responses sheet
I'm trying to see if I can add responses to a sheet manually through a proprietary application but whatever response I add to the sheets document is pushed down and isn't recorded on the responses ...
0
votes
0answers
10 views
Joining Two Working Formula's
I have the following working formula
Located in K2 =IF(INDEX(Details!B18:B81,MATCH(C2,C2,0))<>C2,S30,"0")
So I want it to only use the number in S30 if the date in C2 is not in a range of dates ...
1
vote
1answer
8 views
How can I track the time it takes for someone to fill out a Google form?
I am running a study for my thesis, and would very much like to track the time it takes to complete a Google form to compare against how long it takes to fill.
0
votes
1answer
7 views
How to pull data from a random cell on page load in Google Sheets?
Basically, I'm just trying to figure out how to make a cell pull a random cell's value within a range at random when the page loads.
For example, have cell A1 pull a cell at random from range B1:B20 ...
2
votes
1answer
11 views
Dot leaders in Google sheets
Dot leaders are repeated dots that visually connect a label to the corresponding information on the same line, commonly used in tables of contents but also used in accounting formats, for example:
...
0
votes
1answer
15 views
How to print selected range of a sheet?
Can someone help me to print a selected range of cells in a current sheet in Google Sheets?
0
votes
0answers
6 views
Auto Increment a Named Range in Google Sheets
I am working on a bidding calculator and would like to be able to automatically increment a Name Range once I finalize the formulas once. For instance, there are 15 cells per item row with formulas ...
0
votes
1answer
6 views
How to paste a comma separated string into one column in Google Sheets?
For example, if I have (bob, china, bing, bong). I want to put bob on top of china on top of bing on top of bong.
1
vote
1answer
20 views
How can I allow results from a Google Sheets 'filter' action to be mutable?
Brief: The results of a filter are not mutable. I cannot directly add/edit rows to the cells in and around a filter result due to a #REF Error: not expanded because it would overwrite data error.
...
0
votes
1answer
10 views
Filter column based in other column (using query prefer)
I am trying to make a filter with a query based in a column, but I cannot make it works.
https://docs.google.com/spreadsheets/d/1zKMW8vuyHRpS8eVEmRgAOg_dBQ0r2OIXnqV3_TFEm9o/edit?usp=sharing
This is ...
1
vote
1answer
18 views
Is there a formula to list all possible 2 card combinations of 4 playing cards in Google Sheets?
Example:
Is there a formula to list all possible 2 card combinations of 4 playing cards?
4 playing cards:
A♥
A♦
A♣
A♠
The result should be 6 combinations only (Not 4 x 4 = 16 Combinations):
1: A♥ ...
0
votes
1answer
20 views
Get the name and link file based on Folder ID
I created a script to get the name and link file based on Folder ID. The file name and link are displayed on sheet 'Get_Link'.
File spreadsheet
The script that I wrote like this:
function ...
0
votes
0answers
15 views
Picking a Total from the Current Month
I have this working formula (below)
=SUM(QUERY(QUERY({{A2:B},{ARRAYFORMULA(IF(LEN(A2:A), WEEKNUM(A2:A, 16), ))}},
"select *", 0),
"select Col2
where Col3 matches '"&WEEKNUM(TODAY(), 16)&...
1
vote
2answers
16 views
Reverse subtraction if less than zero (negative)
I have a simple Google spreadsheet that Takes the estimated cost for a service provided and compares it to the actual cost of the service. the equation I have in the Q column is =K4-P4. When the ...
0
votes
1answer
12 views
How can I use ARRAYFORMULA (or something similar) with GOOGLETRANSLATE?
I'm learning a new language and I recently discovered Google Sheets' GOOGLETRANSLATE function. I thought it'd be neat to have a personal dictionary of translations which I can even annotate. So I ...
0
votes
1answer
12 views
Running script to replace “null” with nothing in Google Sheets
I tried to do this through a macro but I'm unable to have it actually record. I'm not a coder so I've been recording macros and using the code from there to create an add on. I need a script that can ...
1
vote
0answers
16 views
Is there a way to create a calculated field using results of another calculated field?
Is it possible to use the results of a calculated field as part of a new calculated field? I know further calculation could be done outside the pivot table but I was hoping there was a way to somehow ...
0
votes
1answer
16 views
How to trigger a script in Google sheets when a value changes
I try to fire a script when a value in a certain cell or range of cells changes.
(it's not changed by the user, so I can't use onEdit)
I've tried to use onChange, but this doesn't fire my script at ...
0
votes
1answer
12 views
How to import/merge data from from 2 separate sheets from another spreadsheet
I need to import data from "Spreadsheet A" to "Spreadsheet B" but all the data is in two separate sheets "MY" and "NY" in "Spreadsheet A"
I basically need exactly this but with a function to import ...
1
vote
1answer
18 views
How can I have Google Sheets automatically parse and calculate text that follows a certain pattern?
I have the following sheet:
https://docs.google.com/spreadsheets/d/1iEMjWVEwyuqbn_imW1UZ-AF2NlE__IbRZdHQifKJjko/edit?usp=sharing
I would like to calculate "Total Work" by deriving it from my own ...
0
votes
1answer
13 views
In Google Sheets, Enter key moves selection
In Google Sheets, pressing the Enter key moves the cursor down a cell. For example:
position cursor on the cell
type to enter text
press Enter key
Expect: Cursor stays on the cell I was just ...
0
votes
0answers
8 views
Build a value-over-time line chart from monthly values?
I have a spreadsheet that looks like this:
+---------------------------------------+
| |01|02|03|04|05|06|07|08|09|10|11|12|
+---------------------------------------+
|'16| ?| ?| ?| 1| 2| ?| ?| ?| ...
1
vote
1answer
27 views
Trying to Query a list against another list
First time ever using stack exchange so please excuse any formatting problems.
I'm trying to create a sheet where Sheet1 is a dump of data and Sheet2 contains:
A date range
A list of items
True/...
0
votes
2answers
21 views
Add columns based on rows
I'm currently working on setting up a sheet, but I want to add columns in one worksheet based on rows in another worksheet.
I have no idea if this is even possible, I've tried googling it. Might be ...
0
votes
0answers
16 views
Google Sheets, using a script to copy a sheet and create a new sheet from a form submission
The goal is to have the process go -
Form Submission
Data goes in to a "main data" Google sheet (This is done automatically)
(This is where I need help creating the script)
A copy is made of an ...
0
votes
1answer
16 views
Sort Google Spreadsheet by 'alternating' values in multiple columns
How can I sort a random list in Google Sheets so it returns alternating values? Is it possible to do it with multiple columns?
How can I alternate so each Room starting with the lowest has one ...
1
vote
0answers
12 views
Google Sheets Clock Cycle Counter - Increment value only on TRUE value in another cell
I want to increase the numeric value of a cell output, based on the contents of another cell input that will contain a binary value. I'd like to do this without using scripts.
Cell B5 is going to ...
1
vote
1answer
12 views
How to group values by month and sum previous values?
I would like to sum the values of a table grouped by months but adding up the previous months.
Example:
Result that I want:
The closest I've come so far is:
=Query(B2:C4;"select sum(C), B group ...
1
vote
1answer
35 views
COUNT UNIQUE values by group ONLY IF NOT already counted in previous group
I hope to explain clearly what I'm trying to do.
I have some data in column B, grouped as shown in column A.
I'd like to count the unique values for each group present in column A, not taking into ...
0
votes
1answer
15 views
Repeatable questions
I'm trying to create a daily personnel report. Each manager needs to report who their personnel are, whether they are Present, Sick, On Leave, etc, and where they are. I then plan on having a set of ...
0
votes
0answers
14 views
Google Forms Duplicating answers to Google Sheets
I have a google form to capture order information,
When the user captured all order information, it pushes to a google sheet so we can keep an order history of who ordered what, when and how much.
...
0
votes
2answers
11 views
IF Function with too many arguments
I want to apply this formula
=IF(A2= "", "",
IF(REGEXMATCH(A2, "Brand"), "Brand",
if(regexmatch(A2, "Loans"), "Loans" ,
if(regexmatch(A2, "Cards"), "Credit Cards",
if(A2, "Generic"),"Generic",...
0
votes
2answers
15 views
Google Sheets API call creates scientific numbers that I cannot then run calculations on
I am using API in Google Sheets to call prices of cryptocurrencies vrs Bitcoin
from URL: https://api.coinpaprika.com/v1/tickers?quotes=BTC
In some cases I am getting scientific notation instead of ...
-1
votes
2answers
17 views
Disable automatically calculation on a single sheet in Google Sheets [duplicate]
I would like to disable automatic calculation on a single Google sheet and then have a trigger, either by button or through some implementation of scripts, inserting a column, etc that will allow me ...
0
votes
0answers
9 views
How can I create this kind of system in Google Sheets? [on hold]
Alright so, my question regards Google Sheets.
I have created a guild for the game DRPG on Discord, but I need to manage the server and deposits of gold. Therefore, I need to log everyone's deposits ...
0
votes
0answers
9 views
Is there recent data comparing web-based Excel to Sheets performance? [closed]
Is there a recent experiment that provides quantifiable performance data comparing specifically the web-based versions of Excel and Sheets?
0
votes
1answer
6 views
Locking information in rows together
I have a Google sheet with contact information that is accessed by many people. These people need to have the ability to sort information and edit it.
Some users, when sorting, accidentally only ...
1
vote
2answers
27 views
How can we make a field (Cell) value mandatory if another field (cell) have specific value, while submitting the data?
I am developing a small attendance app in Google Sheets. Each day after choosing their name or team, the supervisor will see a list of their employees to update their attendance status. The list will ...
1
vote
1answer
17 views
Analyse multiple columns per row
I need to combine the data of three different columns. Thus, I have this:
-------------------------------------------------------
| ID | ID Data | Markdown 1 | Markdown 2 | Markdown 3 |
--------------...
0
votes
1answer
12 views
Increment number in array formula based on a condition
I have a data set with Sub-Total rows at various intervals. As the data can have rows inserted or deleted, I am trying to use an array formula to give each group of data an incremented integer as a ...
0
votes
2answers
23 views
Is it possible to have my Google Drive documents on my desktop some how?
I have about 20 documents on my Google Drive that I constantly and continuously edit.
Is there a way to have quick access to them from my desktop?
I use to just pin them in my browser but I can not ...
1
vote
1answer
12 views
Google Sheets Query Where Cell value
I'm looking for help editing a Google Sheets formula. I want the formula to Import Range from another worksheet where value equals a specified cell. I can only make the where in the formula equal a ...
0
votes
2answers
14 views
Using a checkbox to auto-populate a row on another sheet
I have this spreadsheet which is used for receiving and recording work. SST responses sheet is where people submit requests, Workload is where we record all work.
I'm wanting it so that when someone ...
0
votes
1answer
9 views
How can I have a 2nd function start in the cell after the output of the 1st function?
So the problem I am trying to solve is where I have Google finance fetching the price of a stock and outputting it, but the length of cells of its price history is increased by 1 each day. In the ...
