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.
7,258
questions
0
votes
1answer
24 views
Loop through each row on one sheet coping specific cells of each row to a second sheet until an empty row(last row) is found
I'm new to sheets script. I want to Loop through each row on one sheet (Driver List) while copying specific cells of each row to a second sheet (Driver List2) until an empty row (last row) is found. I'...
0
votes
0answers
12 views
In Google Sheets: compare data from sheet1 & Sheet2 and if they match copy information from one sheet to the other
I have 2 sheets in Google Sheets. What I want to have happened is if the date on Sheet1 cell A! and cell B! is the SAME as the data on Sheet2 cells C! and D!, then copy the data from Sheet2 cells G &...
0
votes
1answer
20 views
Sheets query: Losing string values?
How can I get =query to handle a column of mixed numbers and strings?
I have a column of accounts names. Some are purely numeric (e.g. 123), and some are alphanumeric ("x456").
A
123
...
0
votes
1answer
20 views
Google Sheets: one query for Sum and Filter?
If I have a sheet with data like this:
A B
x 1
y 2
z 3
x 4
I can sum the values with
=query(A:B, "select A, sum(B) group by A")
How can I then filter this result? For ...
0
votes
1answer
27 views
MAX and MIN to ignore ERROR or N/A
I already have a list of auto-generated numbers every few minutes, in which that number fluctuates, but sometimes the number is an ERROR or N/A.
For example my list is:
2
6
2
1
#N/A
#N/A
4
5
I want ...
0
votes
1answer
11 views
Importing multiple user responses on a Form to a single row on a Sheet
I have a Google Form containing three linear scale questions. The form collects emails and then a value between 1 and 3 for each question. I need the people who respond to the form to do so biweekly....
0
votes
0answers
12 views
Google Sheets - assign a year and date rank in ARRAYFORMULA
I want to number production runs in my system based on a continual count for that year, which is then preceeded by the year (with duplicates sharing the same number). For example, if the data set was
...
0
votes
0answers
12 views
How do I choose to add certain cells only on Google Sheets? [duplicate]
I am making a sales sheet for my schools business. We allow three forms of payment(Cash, Credit Card, and Debit Card). For every sale we record how much the total purchase was and how the customer ...
0
votes
0answers
20 views
Can I convert a number in a cell to read as text in another cell?
I have a numerical value in a cell. If the number is greater than 2 then I'd like it to say 'YES' or 'Y' in another cell. If it's equal or less than 2 then 'NO' or 'N' - is that possible?
0
votes
2answers
26 views
Google Sheets - If column contains specific value results in "A", if not "B"
[ANSWERED]
If a cell in column F contains "0" I want column H to show "COMPLETE". If the cell in column F contains anything else I want column H to show "INCOMPLETE".
(...
0
votes
0answers
8 views
Date computation dependent on another cell that has a data validation option
I am working on a lead generation tracking spreadsheet in Google Sheets with the hopes of having a follow-up date auto-populate. There are different amounts of time when we want to follow up with ...
0
votes
1answer
16 views
How to sum certain numbers from a column based of two criterions?
I have this formula:
=ROUND(sumif(Percent, ">0")/COUNTIF(Percent, ">0"), 3)*100 & "%" & " : " & ROUND(sumif(Percent, "<0")/...
1
vote
1answer
18 views
How to Set Values for Discontinuous Cell Locations All at Once?
I am trying to populate a form template on a sheet. Say the a1 locations of the fields on the form to populate are:
[B7, B9, D8, D11]
(In actuality, I have 65 different fields.)
I want to populate ...
0
votes
1answer
28 views
How to extract specific words and separate them by commas?
I want to separate text from cells:
A1 = Bats, Cats, Mats, Rats E
A2 = Bats, Pattern, Rats W
A3 = Cats, Mats
A4 = Pattern
A5 = Mats
I want to remove Pattern and Mats from the rows and extract rest of ...
0
votes
1answer
10 views
total count of rows that a date falls between two dates
I have a start date and an end date in each row and I want to count the number of rows (total count) that fall on a specific date.
I tried this:
=COUNTIFS(D2, ">="&$A$2:$A, D2, "&...
0
votes
0answers
12 views
How can I take the value in one cell and distribute it among other cells?
What I'm trying to do is make a budget sheet that will automatically split and spread whatever value I place into an input cell into various "accounts" that are also within the sheet.
So ...
0
votes
1answer
24 views
Data validation drop-down list based on criteria in the row?
I want a data validation drop-down list that populates based on the value in another column in the same row. I've found a lot of great examples with multiple dependent drop-down lists, but ...
0
votes
0answers
23 views
Reversing order of the y-axis in Google Sheet line graph
I have a simple line graph with Google sheets that looks to show the league position trend for multiple teams on a weekly basis.
The X-axis reflects each 'Gameweek' whilst the y-axis shows the ...
0
votes
1answer
12 views
GoogleSheet VLookup and test for Error
I have a list of for example 25 cryptos that I invest in. In a separate sheet called "CryptoJayTargets", I have his price estimate for year end for a list of 15 cryptos.
I want to create a ...
0
votes
1answer
30 views
Double Sorting on Google Sheets
My sheet has a drop-down menu with options 1.-4. in column C.
I want it to then further sort each option segment using column A. So that option 1. is sorted A-Z separately from option 2. etc. How can ...
0
votes
0answers
13 views
Google Sheets FILTER function adding space after a period (first entry only)
I'm having an issue where, when filtering a range, the first entry (the one occupying the cell containing the formula)has a space added after the first period.
ex. "0.Filter Data" reads as &...
1
vote
1answer
10 views
GOOGLEFINANCE referencing "price", "close", "high", "low"
I want to change the
=GOOGLEFINANCE(A1,"price") to
=GOOGLEFINANCE(A1,C1) where C1 contains "price".
Why does that not work?
https://docs.google.com/spreadsheets/d/...
0
votes
1answer
21 views
Stacking A Range of Columns Into One (W/O 50,000 Limit)
The Problem:
I have somewhere between 20 and 30 columns of varying lengths (dynamic and can change). I would love the ability to be able to stack them in Google Sheets.
I have found some solutions ...
0
votes
1answer
17 views
How to group limited data based in multiple parameters in Google Sheets
I have a Google Sheet containing all responses to the questionnaire from multiple branches. It is like an audit where I have to answer OK or NOK, and if it's NOK I can indicate later that this fault ...
0
votes
2answers
14 views
Adding up cells in a column till the value of sum reaches a certain value - Google Sheets
I have a column with a list of numbers. I want to calculate the sum of the numbers, starting from the top until the value of sum reaches a fixed value, and then stop and return the cell address of the ...
0
votes
0answers
13 views
How to create a google sheet file that inputs a phrase in a certain range when another phrase is entered in a different range
I'm trying to create a google document with a classified theme (And no, it's not actually classified) And I want it set to where if you put certain text in a table or cell, it gives you a link to the ...
0
votes
1answer
31 views
How to get specific data if the date is on weekend?
I'm trying to create a timesheet where I can calculate the normal hours, overtime and weekend hours.
Here is the sheet I'm working on:
https://docs.google.com/spreadsheets/d/...
0
votes
2answers
32 views
Custom Function to Cross-Joining
I am looking for a flexible custom function to be able to cross-join (cartesian product) 2 columns of varying lengths in Google Sheets.
My research so far has led me to ask because:
using a ...
-1
votes
1answer
29 views
How to get the newest value of column E and display the value in A1?
I need help to make Google spreadsheet like this.
You can input values in column E, and the value of the newest input is displayed in A1.
I tried the below code, but it didn't work.
function ...
0
votes
1answer
20 views
How do I correct the automatic borders that are scripted into this Google Sheet?
I have this Google Sheet document that already has functionality built in which applies automatic borders. I don't know how it is doing this. I've tried looking in the Script Editor, but I don't see ...
0
votes
1answer
28 views
Import data automatically into a proper report on Google Sheets
I am feeling this is a long shot, but I found an answer to a previous issue that I felt it was complicated, so I'll give it a shot of asking the following.
Each month, I work manually on a report to ...
1
vote
2answers
27 views
Transferring an Excel document to G Sheets, how do I fix the slightly broken auto-borders implementation?
I'd like to transfer this Excel spreadsheet to Google Sheets, maintaining the automatic background formatting functionality, particularly in the case of the borders.
Here is the same spreadsheet ...
0
votes
2answers
11 views
Getting the ThumbnailLink of a freshly created File in GAS
I've been trying create a file and then access its ThumbnailLink in GAS.
this works well -
let _file = DriveApp.createFile("TEMPFILE.svg", svg);
let file = Drive.Files.get(_file.getId());
(...
0
votes
2answers
16 views
Can Google Sheets macros respect protected ranges?
I have developed a Google Sheets spreadsheet with loads of macros, but I want to have a protected version of it, a blank "master copy" that I then duplicate every time I want to use it.
I ...
0
votes
1answer
26 views
Convert Script into HTML and have links embedded
I have the below script and want to have the URL links embedded instead of the whole link appearing on the automatic emails, but don't know how to convert this script into an htmlbody script to have ...
0
votes
0answers
26 views
How to edit cell contents / type in cell without double clicking or pressing enter in Google Sheets
I have google sheets on my mac, and have this spreadsheet that I've been working on. Previously, I was able to add content to an empty cell just by clicking on the cell / having the cell selected, and ...
0
votes
0answers
16 views
How to send quiz respondents quiz scores through emails automatically?
I'm trying to create a quiz where upon completion, the quiz results will be sent to respondents immediately through email.
I'm trying to do this by linking the quiz app (AidaForm) with Google Sheets, ...
0
votes
1answer
12 views
REGEXMATCH for single cell min/max character criteria?
I'm very new to the regex scene so forgive my level of intelligence! I want a single cell to be 5 or 7 characters long via data validation and an error message for a false return.
I've come up with ...
0
votes
0answers
14 views
How can I reference values from one list in the order of another list?
I don't know if that title even fits but here is the setup:
I have drawn numbers randomly from an urn without putting them back and I have a list of that in a row. For a small example's sake lets say ...
0
votes
0answers
8 views
How can I Import table from this webpage? https://khadya.cg.nic.in/citizen/citizenhome.aspx [duplicate]
I'm Unable to import HTML table from this URL : https://khadya.cg.nic.in/citizen/citizenhome.aspx
I tried to read solutions for the errors I Receive, which are, "Could not fetch URL:.." and &...
0
votes
1answer
18 views
Merged cells not preserved when copying and pasting
I wrote a script to copy and paste a block of cells from one sheet to another in Google Sheets, and everything is working fine except the merged cells are no longer merged after the cells are pasted. ...
1
vote
1answer
14 views
Round up duration calculation to the next multiple of five minutes
I currently have this formula:
=text(D2 - C2, "hh:mm:ss")
I need this formula to round up to the nearest 5 minutes.
I have tried mround(), ceiling() and roundup(), but I'm not getting the ...
0
votes
1answer
41 views
How does one automatically update rows/data with a Slicer filter when a spreadsheet is edited?
How does one automatically update a Slicer when data is changed on a spreadsheet?
I would like it so that when I change cells and data on the spreadsheet, the Slicer filter will automatically update (...
0
votes
1answer
21 views
Is there a way to embed a specific cell from a Google Sheets to Google Docs?
I want to fill in a sentence with blank spaces on Google Docs from information that I can provide from Google Sheets. Is this possible?
"For my student number X = [student_number], the required ...
0
votes
1answer
8 views
Google Sheets - Counting a range for containing strings
I've made a google form that automatically goes onto a google spreadsheet, but I have an issue.
When it saves the multiple choice questions, the answers are saved as one string with commas in-between (...
0
votes
1answer
19 views
In Google Sheets, how do you use their Fill Down (dragging formula thing) without copying cell styles / formatting?
Cannot find this anywhere I look. Lot of SEO spam on Google Sheets.
I have a table that has a ton of different styles:
Header
small font
BIG FONT
bold font
italic font
The "small font" ...
1
vote
1answer
18 views
Google Sheets - How to use results from INDEX formula in a new QUERY function
In Google Sheets I have an =INDEX function which is producing a number of references for data in another worksheet.
I need to use those references in a QUERY function.
I've not found a good way and ...
1
vote
2answers
43 views
How to prevent cell formulas from moving down when data comes in from 3rd party app
So I need to create a quiz with AidaForm, and link the results of this quiz with Google Sheets. There is some additive calculation involved, so in the Google Sheets document I would type in the ...
0
votes
1answer
17 views
Counting unique instances with multiple columns
I'm not sure if I'm wording this properly.. I have two columns - one with an event name that can contain duplicate values, and another that contains the week number the event occurred. The event can ...
0
votes
1answer
19 views
Google sheets conditional format to highlight a column containing items from another list
I have rows of data, Col. A will be the order number and there will be duplicates of the order number (this is for each individual item purchase and MUST NOT be removed to give unique numbers). Then I ...
