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.
6,261
questions
0
votes
1answer
16 views
Google Sheets - Highlighting Cells if they contain text from another range of cells
I want to highlight a cell in a column with a specific color if it contains any of the elements from a range of cells on another sheet.
I believe I need to use 'custom formula is' with the INDIRECT ...
0
votes
1answer
26 views
Need help with Google Sheets moving rows and setting formatting upon move completion for Google Sheets Script
I have a script which I use to copy rows with checked checkboxes to a "Copmpleted sheet". The script is set to run on a timer and it works without any issues. The script and description can ...
0
votes
1answer
14 views
I want to keep certain values the same but I want others to change automatically in Google Sheets
So I have an if then statement that I am using for certain cell but I want the other cells to change.
if(H2,true=null!C3,null!A3,"")
the problem is I want to keep H2 the same when I copying ...
-1
votes
1answer
38 views
Displaying a “friendly name” for an ID in Google Sheets
I have a spreadsheet in Google Sheets which contains information split across several sheets. To keep things consistent between sheets, I use numbers as IDs, like (very simplified example):
Users: ...
-1
votes
0answers
19 views
help with importxml [duplicate]
Can anyone help me understand why this isn't working?
=importxml("https://www.panamacompra.gob.pa/Inicio/#!/vistaPreviaCP?NumLc=2020-1-0-0-08-LP-000101","/html/body/div[1]/div[2]/div/...
0
votes
0answers
24 views
Is it possible to use a cell containing date as data validation in query statement
Is it possible to use a cell containing a date (as data validation) in a query statement?
0
votes
2answers
23 views
How do I change the date to the coming Monday every Friday at 9:30 pm in Google Sheets?
I would like to write a function that checks what day of the week today is, and whenever it is Friday 9:30 pm it should populate the cell with the date of the coming Monday.
So since today is Friday ...
0
votes
0answers
35 views
Copy from individual cell in Google Sheets Table Chart
I'm using appscript to build and insert a table chart into a sheet when a button has been pushed.
var chart = sheet.newChart()
.setChartType(Charts.ChartType.TABLE)
.addRange(outrange2)
.setPosition(2,...
0
votes
1answer
32 views
Multiple IF functions on a single cell
How do I add multiple IF functions on 1 cel, without blocking it on the first line.
As you can see, column D contains a bunch of text. In column A, I want to categories them.
It works up until the ...
-1
votes
0answers
14 views
Anyone managed to use Google Sheets History?
It's File - Version history - See version history.
Anyone managed to actually use it?
Because once you open it every click or scroll takes minutes to complete so its basically useless. And worse, if ...
1
vote
2answers
45 views
I wrongly set the date of 2000 cells. I want to add 20 years of those cells. How?
My first thought was to
add a duplicate cell and show the formula as general number.
add another cell that adds 20 years worth of days.
change the third cell formula back to date.
But I feel like ...
0
votes
1answer
60 views
Auto Decrease Based on [tags:CheckBox] from Another Tab (or Alternatively Another Cell on Same Sheet)
I have two tabs:
"Sheet1" - keeps track of bought and remaining items by quantity
"Sheet2" - keeps track of opened items
Is there a way to auto decrease the quantity from ...
0
votes
1answer
39 views
Randomly mark the column based on the percentage of the total data from each operator
I have data as in this link.
The data will be checked by several operators. Each operator must check data as much as 50% of the total data of each operator randomly.
If the number of data from the ...
1
vote
1answer
10 views
Use importhtml to import a single cell vs a whole column
As the title would read, I am looking for a method to import data from a retail site to my current spreadsheet to have the prices update in real time. Unfortunately I am not educated in this so I am ...
0
votes
1answer
22 views
how to use MAX in a query and use the result as a number in Google Sheets?
If I write a formula using query and limit it to one result, I can then use it as a number, for instance
=1+QUERY(B13:F18,"SELECT C WHERE (B='Banana') LIMIT 1")
But if I want to use the ...
0
votes
0answers
18 views
Persistent timevalue(now()) [duplicate]
I am using the following formula in a column of cells:
`=if(XXnn="";"";timevalue(now()))`
(nn according to the row XX to the column).
This enters the time correctly as a value is ...
0
votes
0answers
42 views
#Error! in ImportJSON formula in google sheets
I have been successfully using the following ImportJSON formula in google sheets:
=ImportJSON(csgoempire.com/api/v2/metadata/roulette/history?seed=1997, "/", "noInherit,noTruncate,...
1
vote
1answer
23 views
How to create dynamic text which include values from cells for alert in Google Sheets script?
I wrote the script which transfer values from one sheet to another sheet.
I added message of confirmation (alert) which is showing list of values before their transfer:
var response = ui.alert('...
2
votes
1answer
27 views
Keeping a cell formula set to the same cell even when a row is inserted
I have one sheet that needs to pick up singles sells for a lot of other sheets.
On the other sheets I have to change the information each month and I work across the rows so I insert a row at the top ...
1
vote
1answer
39 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
1
vote
2answers
36 views
Transposing varied number of columns per entry into duplicate entries with one column in Google Sheets
I feel almost certain this has been answered, but in a time crunch I am hoping someone can outline the solution for me, as my searches returned variations that were not quite what I wanted.
I have ...
1
vote
1answer
46 views
Need help with Google moving rows on open for Google Sheets Script
I am fairly new to Google script. My knowledge of Java is limited as well (I believe the two are similar). I have a script that moves rows when a checkbox is checked. It also adds some rows and does ...
0
votes
0answers
26 views
Putting multiple arguments/conditions
How can I put multiple arguments in a cell using Count IF in Google Sheet when I am providing separating the arguments/conditions I am getting error message?
1
vote
0answers
21 views
Google Cloud Print
Up to and including yesterday, I was able to select to print a Google Sheet directly on my PC system (Linux Ubuntu 18.04) by selecting File ā Print (or using the print icon).
Since today a new option ...
1
vote
1answer
30 views
Insert query into a specific cell based on a dynamic reference
I have 4 queries. The amount of results from each will vary week-by-week.
I have a formula to calculate the number of rows of each query.
I want the queries set out in the Google Sheet so that the ...
1
vote
1answer
29 views
Android (mobile) Keyboard Shortcuts Google Sheets
In Google Sheets on a PC it is possible to use Ctrl+Shift+: to insert the current time into a (time formatted) cell in Google Sheets.
How can this be done when using a sheet on an android mobile phone ...
2
votes
1answer
26 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:
1
vote
0answers
18 views
Setting Unique Data Validation using For Loop
Right now, I am manually adding data validation one by one to a column of cells. This is because each validation range is unique to the row. The validation list I'm looking to create should only ...
1
vote
1answer
29 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 ...
1
vote
0answers
24 views
How to keep same protections/settings when making a copy of a Google Spreadsheet? [duplicate]
I have a spreadsheet that I want to create about 100 copies of at once, all copies with different titles. I found a script that allows me to do that, all I have to do is fill out information from cell ...
0
votes
0answers
14 views
How to apply CF to rows where there is a duplicate/multiple word in a specific column, AND use a unique color for each different duplicate
I want to have Conditional Formatting apply a color to rows when a word shows up in one particular column, but only when there is more than once instance of that word in the column. So for example if ...
0
votes
0answers
21 views
Query from multiple columns
https://docs.google.com/spreadsheets/d/1g7bKBV5Erd4XI8aMuDI278_g3GMbpy-f8UXqEinxUf0/edit?usp=sharing
Here is a sample sheet that has 4 different tabs with 4 columns per tab that I want to 'count' from....
0
votes
0answers
43 views
How to separate direct sibling elements into different columns?
So basically I'm trying to import a list of values from a certain web-page into my spreadsheet and I'm stuck. I want to make a list of names with their corresponding number stages next to them, all ...
0
votes
0answers
9 views
Trying to fetch data from https://ark.intel.com/ with Google Sheet functions [duplicate]
I'm trying to read the datasheet at this page inside a google sheet.
However, both IMPORTXML (1) and IMPORTHTML (2) return #N/A.
Whats's wrong?
(1) IMPORTXML(A1,"//div[contains(@class, 'specs-...
0
votes
0answers
8 views
Connected Sheets BQ and Multiple String Entry for Parameter
Has anyone used Connected Sheets to query BigQuery while using a parameter? I'm able to select a single cell for the parameter but not a range. I tried to input a string in that cell as a workaround ...
0
votes
1answer
17 views
How to open a specific tab based on today's date (Google Sheets)
I've made a 'Google Sheets' with 31 tabs for the dates of the month (1-31)
How do I write a script that makes the spreadsheet open to that days tab?
e.g. open tab 25 if today is Aug 25th
I imagine (...
0
votes
1answer
29 views
Use Text in cell as formula in Google Sheets
I am working on a way of building out a formula rather than a bunch of if/then statements. Is there a way I can convert the text from one cell and use that text as the formula of another?
Example:
A1 ...
-2
votes
1answer
43 views
Is there a spreadsheet function, than means same formula as a specified cell?
In google sheets. I wish to have the same function in several cells. I could copy the function to all the other cells (this will work). However if I want to change the function, then I need to copy it ...
1
vote
1answer
23 views
How to add a frequency list in Google Sheets?
I'm looking to create a histogram with this data. I don't know how to set the count column as the frequency list of the data. When I try to select both columns and make a histogram it just treats the ...
0
votes
1answer
52 views
How do I automatically match text responses to numbers and sum those numbers in Google Sheets/Forms?
I am using a google form to collect responses from students. For each student I would like to match those text responses to specific numbers. Never = 0, Almost Never = 1, etc. Then sum the results for ...
1
vote
1answer
46 views
Daily Average Calculations in Google Sheets
In the editable spreadsheet below I have a column of dates (column A) alongside a column of data (column C).
I want to calculate the daily average of the data in column C and enter it in columns C and ...
-1
votes
1answer
36 views
Find a column header on another sheet that a value matches on
I want to find a cell value that is in another sheet then return the column header (always minus 2 columns) that the cell value matches on.
In the example below I want a formula in S402 that searches ...
2
votes
1answer
49 views
Compare number in one cell to multiple cells and give out nearest number
https://docs.google.com/spreadsheets/d/1Dew-CLxHvMW2BgI9axdJTEI-2CezTx7k6Vt1R48mwgA/edit?usp=sharing
Above is the sample sheet. I need a formula in K cell
0
votes
1answer
14 views
How to get a cell reference used in a cell for use in a different formula?
Suppose cells in a column (TheSheet!A) contain just references to cells in the column of another sheet, but noncontiguously. For example, give Sheet!A17 the formula:
=OtherSheet!A23
Other cells on ...
1
vote
1answer
25 views
Only run ImportData when Google Sheet is open
I have a number of Google Sheets that make heavy use of ImportData to call a REST service.
I was initially under the impression that ImportData would only call the REST service when the sheet is open ...
0
votes
1answer
16 views
URL to create new Google doc from Template Gallery *and* set folder destination
Right now, we have a workflow where:
someone goes to the Template Gallery
picks a template
saves the new doc/sheet and moves it to a specific shared Drive folder.
We're trying to reduce the number ...
0
votes
2answers
48 views
In Google Sheets, analyze cell content, and if certain values are present, return a response in the next column
In the below example, I want to analyze each cell in column A, and if the cell's contents match certain parameters, return a value in column B accordingly.
So if the value in a col A cell = apple, ...
0
votes
0answers
32 views
Script to add a new row and copy the formula that is in each cell
I am trying to work out a script so that when I press the + image on the attached screenshot it adds a new row and copies the formula that is in each cell. At the moment, I have the following script ...
0
votes
0answers
21 views
Pasting values only instead of copying values using a script
I have a piece of code put together through various posts, it is used to move multiple rows into the "Billed" sheet. Unfortunately it's missing a key feature, namely that I need the script ...
0
votes
1answer
33 views
Generate random value from list if adjacent cell is empty
I have a list of hundreds of values organized as follows:
I'd like to have a cell generate a random Item for which there is no corresponding Order value.
