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.

2
votes
1answer
21 views

Google Sheets sort question

I used to know this a few years ago and for the life of me can't figure it out and can't find an answer in Google help. I know how to sort columns, but only by the default A-Z format, how can I sort ...
0
votes
2answers
13 views

optimize onEdit managing set of many checkboxes

I have onEdit script that hides or shows rows by checking/unchecking checkboxes (yes/no values). Looks like this if(orpscscchbx.getValue() == 'no') { var rowsHided = 0; for (var i = 0; i ...
1
vote
1answer
16 views

How can I merge drop-down columns in Google Sheets?

Please see Investment Portfolio Tracker - TMOAP Official Version 5.2. If the three columns (in red rectangles) remain the same for rows 5-20, it's obviously foolish and unproductive to click on the ...
2
votes
1answer
20 views

Perform functions (AVERAGE, STDEV) on rows grouped by unique values in Google Sheets

I would like to be able to group rows based on unique values and then output the AVERAGE and STDEV values for those groups. Using the table below as an example, I would like to determine the AVERAGE ...
0
votes
1answer
5 views

Validate that a combination of cells are unique in a row in Google Sheets

I'm looking accomplish the following but in a Google sheet. https://superuser.com/questions/902400/validate-that-a-combination-of-cells-are-unique-in-a-row-in-excel I have a column of first names ...
2
votes
1answer
7 views

Is it possible to edit the column a Google Form response saves to?

I have a client who uses a Google Form to save data to a Google Sheet. They deleted and recreated one question, and the responses to that question are now being stored in a new column. This messes up ...
1
vote
1answer
14 views

How to prevent cut-and-paste data from changing collapsed grouped cells?

I'm completely lost and don't know how to paste any data without affecting those collapsed rows. For example, I have such a list: And I want to paste numbers from 1 to 4 for every capital letter ...
0
votes
0answers
17 views

Google Form response is modifying a cell on another row of linked sheet

I have a sheet that is recording form responses on a linked Google sheet. The issue is that sometimes the form will modify a cell on the row above in addition to the row that is being inserted (see ...
1
vote
1answer
14 views

Date highlighting of a cell, separated by weeks, given only a starting date and a cell range

I am trying to build a "life calendar" similar to this - https://labs.coruscantconsulting.co.uk/life/weeks/ - in Google Sheets. I have the manually designed sheet finished, but want to add the ...
1
vote
1answer
13 views

Full Join 2 lists with 3 key values

I'd like to fully join 2 tables on 3 key values, there doesn't need to be 0 value on blank cells if it makes it easier to understand (Some Array Formulas can be really confusing) I have income: I ...
0
votes
1answer
11 views

Multiplying several numbers within a text string, is this possible?

I have cell A1 which is a text string: 100 blue cars, 100 red cars, 100 yellow cars In cell B1 I have a number which is a multiplier say 2. Is it possible to change the numbers within cell A1 ...
0
votes
2answers
30 views

Is there a way to protect/copy only some formatting in Google Sheets?

I have a spreadsheet that I use to track customer orders. It has the customer details on a row, their order number and a line of check boxes for the items that they want for that particular order. For ...
1
vote
1answer
19 views

Set link in email body

I have script that sends an email from google sheet and want to have link in the email's body but code like var html_link = "google.com"; var body = "<a href=" + html_link + ">Google</a>"...
0
votes
2answers
28 views

Checkbox to activate a column in QUERY

Is there a way to activate/deactivate a column in QUERY function? I have a query to query and selection of several columns Select Col1,Col31,Col32,Col33,Col25,Col26, and want to have a checkbox for ...
1
vote
1answer
14 views

How do I refer to data in a column under this month?

In the below screenshot, I'm trying to get B42 to show the value of whichever cell on row 40 is under the current month's column: As the dates are automatically generated using a transpose(...
0
votes
2answers
11 views

Dropdown list created from a list in one cell

Is it possible to create a dropdown list from only one cell as a source? Each element separated by commas?
0
votes
0answers
10 views

SUMPRODUCT inside ARRAYFORMULA in Google Sheets

I have a SUMPRODUCT equation that works fine on one row: =SUMPRODUCT(O4:O10-O3:O9,E2-P4:P10,N(E2>P4:P10)) However, instead of copying it for each E row I want to use ARRAYFORMULA to automatically ...
0
votes
0answers
17 views

Set boders to a range if first cell in row is not empty

I need to set borders to a range in row if first cell in this row is not empty. I have this code function onEdit() { var ss = SpreadsheetApp.getActiveSpreadsheet (); var sheetmon = ss....
0
votes
1answer
10 views

Select from current cell to end of row (a la SHIFT + END)

When I have a cell selected, how can I select from that cell to the end of its row? SHIFT + END works in most spreadsheet programs, but I can't get it to work in Google Sheets. I use Chrome on Linux.
0
votes
1answer
40 views

Why don't work function onEdit(e) when using Zapier

I created a chatbot that using Zapier (through Webhook and JSON) for sending to my Google Sheet a name and a phone of user and Apps Script must generate a coupon code automatically. I used function ...
1
vote
1answer
17 views

Google Sheets' MONTH function on an empty cell returns 12

Is this an expected behaviour that month(ref) returns 12 when the ref is an empty cell? I would have thought that this is a serious bug. Am I correct? No mention of that behaviour in the official ...
1
vote
1answer
16 views

Looking for a way to get the current date represented as a numbered month with decimals

Essentially I'm hoping to find a formula that would return the current date as a month in decimal form. So if it's October 15th, the number returned would be 10.48. September 10th would come back as 9....
1
vote
1answer
21 views

Is it possible to add an IF condition to create a comment if a condition is met? [closed]

I have a sheet connected to a form and my users sometimes add cases many times which causes duplicates, I solved this by adding conditional coloring to the duplicated sheets. Now I want to know if ...
0
votes
0answers
11 views

Changing default color values in Google Sheets rows

I would like to change the color of the row highlighting. (see image) When working with data with many columns I would like something other than the light grey that sheets uses to identify selected ...
-1
votes
0answers
34 views

Advanced sorting in Google Sheets

I have a random list of students in different rooms, different gender, and different categories. A dynamic solution would help since the list might grow and students might be added in new rooms or ...
1
vote
1answer
21 views

Change default values when Google Forms autofills a Google Sheets

There's a Google form that's linked with a Google sheet: whenever a response is posted to the forme, a new line is added to the sheet (here's a quick tutorial just in case). Is it possible to have ...
0
votes
1answer
20 views

Transfer a row to another sheet then clear the original row

I saw this problem and the answer was perfect except that I do not want to delete the row. I only need the cells cleared of existing values. How to move a row into another sheet?
0
votes
0answers
16 views

Issue with SUMIFS pulling data from QUERY across multiple sheets

I would like to sum values from a QUERY generated list in the sheet I/S/N/W to the sheet BUDGET for determining a value based on the date in BUDGET!B8. The function in BUDGET!B8 is =MONTH(TODAY()). ...
0
votes
0answers
17 views

Looking for function to select array size based on matching ID codes in t-test

I'm using Google Sheets and trying to conduct a t-test on pre- and post-survey data. The follow-up survey will almost certainly have fewer respondents than the pre-survey. All respondents have a self-...
0
votes
1answer
16 views

Importing only certain rows after looking at other answered questions

=QUERY(IMPORTRANGE("URL","SHEET1!A5:P85"),"SELECT * WHERE K:K contains 'Name.s'") Error Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "K "" at line 1, ...
0
votes
0answers
30 views

Loop through all sheets with macro

I have a macro that I want applied to all my sheets. I tried applying different codes I've found online but it only goes through the first sheet before stopping. function LoopThrough() { var ...
1
vote
1answer
15 views

Google Sheets Conditional Formatting custom formula not working

I am trying to shade all rows that are associated with a specific value of column B as =$B2="BAMS", yet custom formula keeps saying the wrong formula. Any idea?
2
votes
2answers
30 views

How can I have onEdit trigger when specific cells are selected?

I can't seem to figure out how to get my script to trigger only on the edit of specific cells and on the current sheet. I've tried just about everything and it just refuses to run the script at all if ...
0
votes
1answer
20 views

What function formula should I use to calculate my salary so that I don't have to differentiate between Friday and weekend manually?

These are the formulae I am currently using where I have to manually sort out if its Friday or weekend by using conditional formatting to bring attention to the minority that needs a different formula....
0
votes
1answer
11 views

How do I lock Filter but not Filter View?

I have a large number of people looking at my spreadsheet and I constantly get the problem of someone using filter instead of filterview which is a problem and highly annoying. I make announcements in ...
1
vote
0answers
9 views

Can I collaborate with Google Sheets offline on multiple devices?

Can I do something about this: I manage multiple teams on sites with little or no signal except the small office WiFi spots. I want to have a copy of a single Google spreadsheet on multiple iPads for ...
0
votes
1answer
16 views

Google Sheets moving to a new column if cell not blank

I have to scan barcodes of inventory for techs to install I wanted to find a way to skip to the next column after I scan a certain number of meters for example: John needs 90 units his name is on A1 I ...
0
votes
1answer
35 views

Why would I be getting the error: Too many simultaneous invocations on a simple once a day trigger?

I've searched around and still can't find anything directly related to the failure message I'm receiving. The script hasn't changed and it used to run just fine, but now in the past week or so it's ...
1
vote
1answer
20 views

Concatenating cell values to a matching cell

In the following sheet I am trying to grab a list of GUID keys from tab sheet1 and concatenate them to their respective order in tab sheet2. My expected output is in tab sheet2 column B as follows: ...
1
vote
2answers
13 views

How to get a true when a character is NOT in range

I want to filter all cells in a range which are not empty and does not contain a certain character. So I tried everything with regexmatch =filter(Planning!G$5:G$37; regexmatch(Planning!G$5:G$37 ; "![...
1
vote
1answer
19 views

Google Sheets conditional formatting for Max value that meets a second condition

I'm working with a baseball statistics spreadsheet in Google Sheets, and I want to highlight the maximum value in my batting average column, but only if that value is for a player who has satisfied ...
0
votes
1answer
12 views

Boolean test of whether a time is before or after midnight in Google Sheets?

I'm having a job trying to create a Google sheet which calculates the difference between the time I fell asleep and the time I woke up. Here's what I want to happen: Sleep time Wake time ...
2
votes
1answer
23 views

Comparing Unique Values to a Range

I have the following formula =CONCATENATE (FILTER(boxmod!AC2:AC, boxmod!I2:AB=concatboxmod!C2 )) The intention of this formula is to take a range and compare it to a list of unique identifiers on ...
0
votes
1answer
24 views

Scitpt to duplicate template sheet into new workbook, then rename

I am looking for a script that will create a menu item that will allow me to copy a template sheet that I have created (multiple times) into a new workbook, and rename the copies at the same time. ...
0
votes
1answer
15 views

Function OnEdit run slow and timeout error

I have a short timestamp script for my google spreadsheet. But it run quite slow and make a lots function onEdit(e) { var TIMERECIEVE = [0,-29]; var DATETRECIEVE = [0,-28]; var DATEDONE = [0,-...
0
votes
0answers
15 views

Looking to open a timesheet in Google sheets and auto seek the current date [duplicate]

My employer has recently transitioned from excel to Google sheets as our timesheet platform, and we're finding that every time the doc is opened or recovered from suspension, it resumes at the top. ...
1
vote
2answers
19 views

Insert literal text that begins with = sign

I would like to add text that begins with an equal sign in a cell but I get the following error: #NAME? Question: How can I set a cell's text to a string that begins with the = sign?
0
votes
2answers
17 views

Copy other cells if EITHER column has specific text

In a single sheet wanting to copy D3:N if either B3:B or C3:C = S2. I'm sure there is a simple formula to do this I am just unable to find it!
1
vote
2answers
21 views

Copy the contents of a row to another sheet based on a specific value

I have a Google sheet with various rows of data, in one column (Column E), the values for each row follow the following pattern: A1 A2 B1 B2 C1 C2 etc. I want to copy the rows ...
0
votes
1answer
10 views

How to modify my formula to take 1 hour off?

E6 = Finish Time. B6 = Start Time. My working formula: =E6-B6+(E6<B6) =Time worked in Hours/Minutes How should I modify my formula to "minus 1 Hour" for a break? Sounds easy, but I can't get ...