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,391
questions
0
votes
0answers
2 views
How to select a random non-blank row?
Let's assume two sheets.
One sheet "data" has filled rows in the range A:C.
Another sheet called "return" should display a random row from "data".
Therefore my question, ...
0
votes
1answer
7 views
TypeError - Move row from one sheet to another
I am experiencing the following issue and was wondering if anyone with more experience can shed some light. I have very little experience with code.
Row is transferring correctly, but I am getting the ...
1
vote
1answer
9 views
Trying to add multiple formulas in one cell
I'm very new to Google spreadsheets and I'm trying to add muliple formulas in one cell. This is my code so far, I'm not very good at formulas.
={SUBSTITUTE(C:C,"wd","Web Development&...
-1
votes
0answers
7 views
importrange & if & indirect combo
I have two separate live sheets;
On the 1st sheet (sheet A) tab F, when I write Y into any cell in Column P, say P6, I need the data in the next column(Q) same row (6): Q6, to be transferred (...
0
votes
0answers
10 views
Google Sheets - find duplicates across multiple columns
My spreadsheet contains information about companies and is structured ledger-like in this way:
Company Quarter Revenues EBITDA
A 2010.1 5 1
A 2010.2 6 1
B ...
0
votes
1answer
9 views
Changing the fontColor of cells when editing
I'm trying to write an Apps Script that change the font color of a cell when it value changes.
I found a way to to change de fontColor of a range, and add to trigger 'onEdit', but the all range is ...
0
votes
2answers
22 views
Google Sheets for if contains “*”
I have found the the regexmatch solution for substring: Google Sheets formula for "if contains"
However, why for "*" is not working? I'm trying to make a condition if the name ...
2
votes
1answer
23 views
Using regex to select the text between two characters IF there is text
I have this text:
<a class="_f5rco33" href="/account-settings"><div class="_ojs7nk">Account</div></a>
And I want to grab the word "Account&...
2
votes
2answers
26 views
Categorize text in Google Sheets based on matching partial text in the Category index
I'm looking to match, with VLOOKUP, part of the words in a column in the main sheet to an index of keywords/categories where partial words are stored for ease in another sheet.
Here's an example ...
0
votes
0answers
12 views
How to get the previous edit of a cell in another cell using formula or script
I'm trying to figure out a way to bring the previous edit of a particular cell in another cell. I cannot use version history because every cell may e edited at a different time in different versions. ...
0
votes
0answers
15 views
Bulk Insert images into Google Sheets as Drawing [closed]
I have generally bunch of pics/slides which I like to add into sheets using Insert > Drawing which allows me to annotate/edit freely.
The is that I need to do that 1 by 1 which is very annoying so ...
1
vote
1answer
22 views
Locking a formula when adding rows
Currently I have this formula in there
=IF('Form Responses 3'!G$20="","",COUNTIF('Form Responses 3'!G$1:G$167,F20))
It does what I want it to do if I go in a tweak it later, but I ...
0
votes
3answers
45 views
=IMPORTHTML forces content to date format
I am a beginner with code and formulas here, so I was wondering if anyone could do exactly what was done on this question:
Trying to use Google Sheets importHTML() to import a table. It forces content ...
-1
votes
2answers
27 views
Formula to output the date for next Friday once it has passed 20:00 on Friday
I would like a formula to output the date for the upcoming Friday and switch to the next Friday once it passes 20:00 on Friday night.
I have tried some solutions from other questions however I can ...
0
votes
0answers
24 views
How to create movable image in Google Sheets? [closed]
I want to create a movable image in Google Sheets so my students can click and drag images with ease.
Is there a script I can add to an image so that the arrow cursor will appear over the image making ...
1
vote
1answer
27 views
Summing Columns Based off Different Criteria in a group by in a Query Function in Google Sheets?
I would like to know how to sum a column based on different criteria in a group by in google sheets query function.
Here's the data
And
Here's what I want
Currently, I have a separate query for each ...
2
votes
2answers
40 views
Using INDEX to move a value, but also do an operation on it?
I've built a crop planning calendar in which I enter the date I want a crop to be ready in one sheet, and it is automatically populated in the following sheet using INDEX.
The goal is to enter the ...
1
vote
1answer
24 views
Google Sheet color rows 2 by 2
I cannot seem to find a way on how to color 2 rows in a row. All tutorials are to color alternating rows.
Namely, this is the situation
A1:O1 Header Title
A2:02 Header Subtitle
A3:O3 Name
A4:O4 Value
...
0
votes
3answers
55 views
How do I create a compound interest table where I can see each calculation indefinitely
I want to set up a compound interest table. I want to start with $15 and multiply by 1.0125 indefinitely. I want to be able to see the answer for each calculation (for example, I want to see what the ...
1
vote
1answer
21 views
Replacing items in comma delimited cell with same string each time
I'm trying to create a formula that will replace any contents of comma delimited cell with single string. The number of items in delimited cell will vary...
Specifically:
524352435,2345235523,...
0
votes
2answers
29 views
Google Sheets, view two sheets side by side
In a Google Sheets, I have a Results main sheet, and the Details sheet.
I would like to modify the Details and see the Results changing.
Is it possible to split the view between two sheets in the same ...
-1
votes
1answer
36 views
Generate programmatically hyperlinks to sheets in Google Sheets
I know that is possible to generate hyperlinks programmatically with a script or a formula to a website taking a cell value as a reference. But in this case I need to generate a hyperlink to another ...
0
votes
0answers
15 views
Google docs produces a pop up when copying and pasting nearly every time
When I copy and paste into google docs it almost always (3 times out of 4) produces the following box even when I use the keyboard shortcuts (on firefox). Is there any way to permanently tell it to ...
0
votes
0answers
20 views
How do I combine multiple results of two filter formulas in Sheets?
Share Link: https://docs.google.com/spreadsheets/d/1-SoQS5Y4EvExbYTOjBGdx7EAq4OYlS0sPENFsblrTr4/edit?usp=sharing
Formula Currently: =CONCATENATE("[",FILTER('BLS - Occupational Employment ...
0
votes
2answers
16 views
Multiple IF Functions with Tick Boxes
Is there a formula I use that states, if either check box is checked in row 1, B2, C2, OR D2 (so any of the 3) - it appears in E2 as Present. If none are checked, then absent. The formula I've been ...
0
votes
2answers
18 views
How Can I Exclude Vacation Weeks From A Week Number Count?
I am creating a Google Sheet for teachers that automatically fills in the week number beginning with the first week of school in early Sept. and continuing to the last week of school in mid June. I am ...
1
vote
4answers
39 views
Editing comma delimited cell in Google Sheets to replace some items
I have a cell that contains mixed input of one or more delimited items eg:
apple,ABC1,banana,BBC2,FGC3
I need to replace the items that contain ALPHA code eg ABC1 (from a finite list of about 25 codes)...
0
votes
0answers
8 views
How to set a script to delete a row that contain specific word in column D on google sheet and to auto delete the data after any hour set [closed]
I am new to this, I wanted to set an automation using UiPath every hour. However, The process to pasting and filtering certain row will take some time. If i can a have script to run on pasting and ...
0
votes
1answer
47 views
Google Sheets: Need conditional format formula to parse number-hyphen-number input into “2-Tuesday” type
I want to write a conditional formatting formula that will convert
1-3 to 1-Thursday,
2-2 to 2-Wednesday,
3-1 to 3-Tuesday,
4-0 to 4-Monday.
The rationalization being I have a list of committee ...
1
vote
1answer
26 views
Find and replace Google Script
I've read a lot of questions and I tried several Script for Google Sheets but nothing works for a very simple case:
I would love to replace all the points "." with the commas "," ...
0
votes
1answer
13 views
Google Sheets: Supplement cells automatically
My goal is a function/macro/script that automatically inserts a text in every selected cell by a key combination (or similar). The inserted text needs to stand at the beginning as seen in the example ...
0
votes
1answer
14 views
Top 2 Values in each Row Conditional Formatting
I have a google sheet with 1006 rows which I am trying to use conditional formatting to identify the two top values across 7 columns (J to P) for each row.
I managed to get 1 row to work (highlighted ...
1
vote
1answer
36 views
How to prevent duplicate URLs in google sheet? [closed]
Is it possible to prevent duplicate URLs in google sheet like the following:
User enters URL: https://superuser.com/questions/ in a row of column C.
If user wants to enter any subdomain or other URLs ...
0
votes
0answers
37 views
How do I do some data validation dropdown items can be selectable by only owner of the document?
I have a Google Sheets document which is shared with nearly thirty people and I added data validation dropdown to all the cells to not entering different values except I defined.
This data validation ...
0
votes
2answers
32 views
copy a google form retaining responses and summary charts
I have wrongly submitted a google form to two different groups of people.
So I have created a copy of the form after generating the spreadsheet.
However, in the responses tab of the copied form, I get ...
0
votes
1answer
20 views
Recreating a financial year with dates, works in excel but not in Google sheets
So I am trying to recreate something like this picture:
Financial Year in excel
But I just found out that the cell B7 does not work, the cell B7 contains the formula:
=IF($A$3="Financial Year&...
0
votes
1answer
26 views
Summarize least relevant entries of table into a “Others” row
I have a table with ~100 rows that can increase or decrease over time. I want to filter this table so that it shows the top N items and the sum of the rest in synthetic row called "Others". ...
1
vote
2answers
31 views
Google sheets long URLs
I have a google sheet that generates URLs from the values in cells. When the generated URL is short, the text produced is blue, underlined, and functions as a link to the URL. When the generated URL ...
1
vote
1answer
20 views
Use of Countifs to check values from two different columns to match a criteria
I have a data with 3 tabs. Tab 1 has information to be analyzed in tab 3. So in tab 1 I have column J and K which have responses of either "yes" or "No" in one row. So if for ...
0
votes
1answer
22 views
Trying to search(from sheet1 ) for a value on in any row of sheet2
I have 2 sheets "Main" and "Data Storage"
Main: should allow me to search "Data Storage" and locate information form any column(let say Column B') and provide me with ...
1
vote
1answer
33 views
In Google Apps Script how do I check if a reference is out of range?
I have some Google Apps Script code which works with Google Sheets. In one segment of code there are some instances where it throws an exception.
Here is the exception:
Exception: Cell reference out ...
0
votes
2answers
23 views
Removing specific text from a box?
Is there a way to remove text from a box in google sheets? Here is a example:
Total: [=sum(stuff here)]
User 1 5 points
User 2 8 points
The [5 points] is one cell. If I just try to sum it up, it ...
0
votes
2answers
43 views
Google Sheets FILTER function produces no result after certain start date
I have a sheet that is supposed to show which people are working where between a start date and an end date.
The FILTER() function is in cell A3.
=IFERROR(SORT(
{
FILTER({'...
1
vote
1answer
10 views
Attempting to use a LOOKUP with an Importrange tab on Google Sheets to make sure values across two sheets are correct, but I keep getting errors
This is the sheet I am working on. The trouble starts on the tab titled "Sectors and subsectors", on row 56 under the column named "checksum".
What I am attempting to do is as ...
0
votes
1answer
22 views
Google Sheets - Filtering Chart Data with Checkboxes
In Google Sheets, I'm making a 'workbook' that keeps track of inventory in a shop. There are 3 sheets in the workbook.
The first one is the raw data which is 'Item Name', 'Initial Stock', 'Stock Used',...
0
votes
1answer
18 views
Strikethrough name in cell in specific range if it contains/matches name from another cell range on same sheet
So we've got a calendar on google sheets, it has a bunch of different things besides just the days but the part I'm working on is easily displaying who is not going to be working that day.
We have a ...
0
votes
0answers
6 views
Sheets - Import XML pulling specific table values not working [duplicate]
I have been trying to pull specific values from the "on-ice" chart and applying this to many players at once but I cannot seem to string together the correct path to achieve this.
Here is ...
0
votes
0answers
16 views
Conditional formatting with color scale macro/script for many columns
I need to make daily report and put color scale under conditional formatting tab.
But since there are so many columns (I have 76 column), I need automatic script to make it like below picture.
The ...
2
votes
2answers
64 views
How do I find the max value in a column only if it contains certain texts in google sheets?
The data has numbers in text format and texts with numbers.
How do I find the max of the numbers in text format without converting to numbers?
How do I find the max if it contains certain texts (ie, ...
0
votes
0answers
10 views
How do I set the font for a published Google Sheets chart?
Google Sheets supports the publishing of interactive charts, but the resulting charts do not use the font set in the sheet.
How do I set the font for a published Google Sheets chart?
