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
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.