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

1
2 3 4 5
128