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.

1
vote
1answer
11 views

If a cell equals to this date, then filter through another sheet and grab that cell that has the same date

When you input a date in sheet 1, I want it to automatically grab data from sheet 2 and input text that matches that date back into sheet 1
0
votes
1answer
18 views

Google Sheets Cell Reference +1

I am wanting to enter a bunch of cell references that are affected by a change in only one of them. On one sheet tab, I have a list of dates in column C, and dollar figures in both D and E. In another ...
0
votes
1answer
9 views

Having a formula output to a different cell

I have a Google Sheet in the following format: ID | VAL ------------- 1 | dataH 2 | dataW 3 | dataX 4 | dataC 5 | dataG 6 | dataL ... | ... ID and VAL are on cells A1 and B1, ...
0
votes
0answers
9 views

Google Sheet - auto copy once a day a value from a row to another row in another sheet

I have a Google spreadsheet that is being modified during the day, and need to copy and paste the final information of a row in a different row in a different tab, each day, pasting the new ...
1
vote
1answer
19 views

IMPORTRANGE from a separate sheet based on closest date to today's date

Sheet1 will have data with different past dates Sheet2 - I want to import data from the 1st sheet and find the closest date to today's date. https://docs.google.com/spreadsheets/d/...
0
votes
1answer
13 views

Show each n-th column, hide others in Google Sheets

I have several big tables, and I know that each 4th column is interesting for me, i.e B,F,J,N, etc (The total number of columns is different in different documents) Is there an easy way (some sort of ...
0
votes
1answer
10 views

How can I copy a row of data automatically to another spreadsheet when a condition is met in a drop-down menu?

Our small business uses a spreadsheet to track data from inquiries. Once booked, I need the data to automatically copy in the "Current Bookings" tab in the same sheet when "booked" is selected from a ...
1
vote
1answer
19 views

Using ARRAYFORMULA and QUERY to produce results in multiple columns

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

Getting a direct image link with file extension to a published Google Sheets chart

I have a Google Sheet with a chart built from data in the sheet. I've published the chart, but the URL isn't an image link. I need it to be a direct link to a valid image format (either .jpg or .png). ...
1
vote
1answer
11 views

How to go to the first blank cell directly in a column of Google Sheets?

I use column A mainly. A1 is the name of data type, and A2, A3, A4, ... are data. When I open this file, It shows the screen containing the first cell, A1. If I fill data from A2 to A250, then I ...
1
vote
1answer
9 views

Concatenate - Show blank cell result if a cell is empty

Could someone help me? I am trying to create a social media keyphrase strategy using keywords. I would like to know if Google Sheets could return a blank cell if one of the cells in the concatenate ...
0
votes
1answer
7 views

How to convert an array of unique records into an array where records are repeated N times?

I have a Google sheet that records items and their quantities. In order to input them into our SalesForce system, I need to split each record into repeated rows, according to the quantity of the item. ...
1
vote
1answer
23 views

Google Sheets Conditional Formatting not working for all cells

This is my first question using StackExchange, so please bear with me. I am making a spreadsheet for tracking due dates for training. Sheet 1 shows due dates that people with view only capabilities ...
0
votes
1answer
11 views

Display results of success over attempts in X/Y format

I am tracking the number of successes out of the total number of attempts. So, for example, Column A would be the number of successes. Column B would be the number of fails. I would like Column C to ...
0
votes
2answers
18 views

inputBox in onEdit does not work for users

I have a strange problem... Simple function like: function onEdit() { var ss = SpreadsheetApp.getActiveSpreadsheet() var sheet = ss.getActiveSheet() var ui = SpreadsheetApp.getUi() var response = ...
1
vote
1answer
8 views

Help using 2 parameters to output a specific category

I'm looking to be able to enter an "AGE" and a "V02" in a formula that tells me what "category" the person would be in ie; good, excellent, etc.
0
votes
1answer
10 views

Center page view Google Sheets

80% of the time we are working in Google Sheets like this one. https://docs.google.com/spreadsheets/d/1kYHghFZTyKxz_CpME0s1SaJdFo3AG8OSeWCi7Q-bAVE/edit#gid=274025131 It would be better if the content ...
1
vote
1answer
11 views

Creating Tabs in Google Docs

I know in Google Sheets you can compile multiple spreadsheets by using the tab feature at the bottom. I was wondering if there is any way to do this with Google Docs.
1
vote
2answers
37 views

Does Google Sheets allow moving a chart to its own sheet?

I know that Google Sheets used to allow moving a chart to its own worksheet so that nothing else is in that sheet. All the instructions I've found for how to do so are no longer relevant to the most ...
0
votes
1answer
12 views

Split data of a row in multiple rows

I've got some problem with data coming from a Google form. The result are: A|A|A|A|B|B|B|B|C|C|C|C| What I want to get is: A|A|A|A| B|B|B|B| C|C|C|C| Is there a way to get a similar result?
1
vote
1answer
28 views

How to get data to fill alternative cells when data is present in a zigzag pattern

https://docs.google.com/spreadsheets/d/1kwGbFd88cgifQRK_ZqsahQt1aDLzMTnmxHKs7mML5jA/edit?usp=sharing How to get data entered in one cell to fill another cell in a different location with same values ...
1
vote
1answer
11 views

Perform different functions depending on if a call is blank using Google Sheets

I want to determine the area of objects in each row. If values for length and width are present, I want to multiply the values to determine the area of the object. However, for circular objects, the ...
0
votes
1answer
8 views

Create named Versions of Google Sheets (or other documents) automatically on a schedule

I'm trying to create named versions of a file on a schedule, say once per week. Refer here for an example. The named version should be something like the file name + date so that it can be ...
0
votes
1answer
20 views

dynamic borders for columns

I have this script that sets borders for range if first cell is not empty function borders() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheetmon = ss.getActiveSheet() var rows = ...
1
vote
1answer
14 views

Google Sheets: IF, AND, OR functions

I'm looking to build a formula with the following conditions: Sales >= 70% Bills >= 32% Accounts >= 43% Meeting all the above criteria would result in "Pass" appearing as the text. Built within ...
1
vote
1answer
27 views

How to do permutation of characters and print it horizontally along that row in Google Sheets?

I am stuck here in printing permutation of two or more characters horizontally along that row in which the characters are. Is it possible? Because I searched it all over and only found vertical ...
0
votes
0answers
8 views

Does fetch() formData() and POST to google sheet work on mobile browser? [migrated]

I am gathering some data from a form and posting it to Google Sheet onClick. After posting the data, the user is redirected to another page. I wrote the code in vanilla Javascript - the app is in vue,...
-1
votes
1answer
7 views

How to display a text after a choice is selected in google form?

So I have my drop down connected to spreadsheet. There are two column which is ID and Name. In my google form, I want the user to select the ID but not the name. The name should be shown according to ...
0
votes
1answer
7 views

How do I search a string, and return all values?

How do I use the script editor to search a string in one cell, and return all results from another sheet? For Example: I have my string in cell B1 - which is the item number Data to be searched in a ...
0
votes
0answers
10 views

Is there a way to allow access to Importrange at once to all the links in a worksheet?

I am importing data from more than 7 sheets and I have 10 such sheets. Can anyone help me if there is a way to "Allow Access" automatically instead allowing access everytime?
0
votes
1answer
15 views

How can I do an aggregate function with multiple columns with a Google Sheets query?

I've got two columns of 10 numbers in Google Sheets. I'd like to average the numbers in both columns using a Google Sheets query. Something like this: =query(A1:B11, "select avg(A&B)", 1) <- ...
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
6 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
15 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
12 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.