Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
0answers
12 views

Retrieve specific value from pivot table

I have several sheets with a pivot table in each sheet. Each pivot table has the same structure of data, with 2 columns and 5 rows: 5 labels with a numeric value and the percentage of the total ...
1
vote
1answer
13 views

Shortcut to put focus at end of value in cell

Is there a way to do this without using a mouse? I'm in Google Sheets, with the focus on a specific cell, which already has content in it. I want the focus to move to the very end of the value in the ...
1
vote
1answer
14 views

How to color cells in column A if cells in other columns relating to those column A cells are filled in?

Column A contains student names. The other columns show work that is missing or incomplete. I'd like to be able to have cells in column A colored in if work from various classes is missing, without ...
1
vote
1answer
18 views

Creating a parent ID cell in Google Sheets

I've got a spreadsheet of various categories and I'd like to create a column which gets the parent name of that particular category. They are currently broken down as follows: 01.01 Category 1 01.02 ...
1
vote
2answers
16 views

Imported values in Google Sheets from Google Forms

Using Google Forms for a quiz... The quiz is graded /30. Value is transmitted into Google Sheets. Cell C2 displays a value of grade... here, 14/30 (as an example) Value for cell C2 reads as 14 ...
1
vote
2answers
21 views

What function can I use to conditionally format text depending on cursor position or active cell?

In Google Sheets, I have a 'Freeze' on Column A. I want the text in Column A, Row X to Conditionally Format as BOLD RED, while my cursor is on any cell for Row X. Is there a way to manage that?
1
vote
1answer
36 views

Skip empty rows while copying

I have this script in Google Sheets which is able to archive range Sheet1!A5:H to Sheet2 function tc() { var reference = 'sheet1!A2'; var rng = SpreadsheetApp.getActiveSpreadsheet().getRange(...
2
votes
2answers
55 views

Automatic currency conversion in Google Sheets

I have a personal accounting sheet that is mainly accounting in Euros, but for every manual Euro (EUR) entry I do, I want to have that entry converted to USD in the previous column next to it ...
0
votes
1answer
12 views

Comparison Column Chart

I'm trying to make a chart in Google Sheets to compare how much two people have paid towards something. What I'm looking for is the chart to have several cost categories (groceries, dining out, pets, ...
0
votes
1answer
12 views

Retrieve fund price from Vanguard website using importxml in Google Sheets

This was working in Google Sheets but now I get #N/A message: Imported Content is Empty A1: https://institutional.vanguard.com/web/c1/product-details/fund/1680 A2: //*[@id='dailyNavValue'] A3: ...
-1
votes
1answer
7 views

REGEXMATCH doesn't match, but should work

Why the following REGEXMATCH results FALSE? =REGEXMATCH("aaa E: 111 111 bbb"; " E: ([\d\s]+) bbb") How should I fix it?
0
votes
1answer
33 views

QUERY error NO_COLUMN:col1

=query(importrange("12HAbnTv4g_6m1UeCOs9ZO0gAYvy8tlaXr-cc2ecPEic","Master!A:ba"), "select col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col53 where col53<> ...
1
vote
1answer
23 views

Pie chart with sub-slices per category in Google Sheets?

I have a sheet that contains a list of books, with metadata about those books. Two columns that exist are the library within which the book is stored, and the condition of the book. I'd like to create ...
0
votes
1answer
17 views

Using checkbox to copy data form worksheet A to worksheet B

I am trying to use the new Checkbox function in Google Sheets. I can't figure out how If I click the checkbox in A9 on Worksheet 2 I want the Text in A10 and the $ amount in A11 to be copied to cells ...
0
votes
1answer
21 views

Google sheets, scatter plot, multiple datasets, with datapoint labels

I want to create a scatterplot in Google sheets with multiple datasets, and yet still be able to add data labels for each data point. Additionally, it's two independent axis, which means some X values ...
-1
votes
0answers
21 views

Replace formulas with results in google sheet only if the result contains value in it, else i want formula to stay intact

I am working on a google sheet where i am using a vlookup formula. I want to replace formulas with results, only if the result contains value in it, else formula should stay intact. The below code is ...
0
votes
1answer
16 views

How to use .setBorder based on cell content?

I'm completely new to programming and I'm trying to write this ridiculous easy script but it just won't work.. Anyone who can help me out? I think the script makes clear what I want to do but let ...
1
vote
1answer
22 views

How do I conditionally highlight cells

Not sure if I'm sharing this right but below is a link to my sheet. Cell C3 is past today so it is red. What I'm trying to do is have it go back to white when I enter the next "Last change" date in ...
1
vote
1answer
40 views

Calculate Work Hours

Given work hours for each weekday (let's say 9:00:00-17:00:00 Monday through Friday), a start timestamp (e.g., 1/15/2019 6:15:15) and end timestamp (e.g., 1/21/2019 14:35:10)... How can I get the ...
0
votes
4answers
24 views

How to pull the page number and the number of images listed from one sheet and then populate cells in another sheet accordingly?

Sorry, I didn't know how to summarize it in the title. Basically, I have a spreadsheet where I'm keeping track of the number of images that need to be on each page of a document I'm working on. On ...
0
votes
2answers
30 views

Problem with QUERY(IMPORTRANGE()) to retrieve data

I’m having a problem with the =QUERY(IMPORTRANGE()) formula while retrieving a column data from one spreadsheet to another. =QUERY(IMPORTRANGE("spreedsheetURL","Sheet1!A2"),"select Col2 WHERE Col1 ...
0
votes
0answers
50 views

Google sheets GOOGLEFINANCE command doesn't access certain stocks anymore

Up until a few days ago, I had no problems. Suddenly, the googlefinance function can no longer access the VGI stock. The exact error is "When evaluating GOOGLEFINANCE, Google Spreadsheets is not ...
1
vote
2answers
24 views

Query using date range doesn't work for specific data

I have a spreadsheet (URL below). I've set up a query to make sure I understand how to do a query with a date range. Then I tried to create a similar query on the "real" data. The "Week 1" worksheet ...
1
vote
1answer
25 views

IF statement not working in Google Sheets?

So I'm using Google Sheets and I'm trying to determine whether or not cells in a certain column AO have a higher value then cells in another column O. My formula is =IF(AO6 > O6,"UNDER","OVER"). ...
-1
votes
0answers
38 views

How to get my contacts to Google Sheets via script without down-or uploading

How to get my contacts to Google Sheets via sheet script without down-or uploading or importing exporting method. getAddress() doesn't work for me this is not an answer, but i have to, because the ...
1
vote
1answer
16 views

Hiding and Unhiding columns based on prompt

I'm trying to make a template that hides and unhides columns based the value in G2. When a user will value from 1-5 in G2, certain columns will be shown, and certain columns will be hidden. ...
1
vote
0answers
12 views

Copy formula every other row and every column

I have been playing with scripts to try and figure out this tedious task as there are 600ish rows in my sheet, times 4 sheets. So far I have come up with this function myFunction() { var ss =...
1
vote
1answer
12 views

Conditional Google Sheet Checkbox

I am creating a spreadsheet which can track all of the replacement items I am sending out for my business, however, I also want to be able to track my refunds on the same spreadsheet. I have got it so ...
1
vote
1answer
11 views

Conditional Format Copying in Google Sheets

I am looking to copy over a conditional format that changes the colour of an array of cells when checking a checkbox. This works perfectly fine on the first checkbox. However, when I try to replace ...
1
vote
2answers
17 views

Given a shared Dropbox link to an Excel file, how can I easily open it in Google Sheets?

Scenario: I get an email every two weeks with a vendor's price list as a link to an Excel file in their Dropbox. At this point, I have to: open the link — This takes me to Dropbox, where the file ...
1
vote
2answers
60 views

Cartesian Product with constraints

I have a really big database that I have to sort and divide as the example below: F1 F2 F3 C1 C2 C3 C4 results P1 X X X X X P1 ...
0
votes
1answer
14 views

Number in a cell linked with a text

I would like to ask if there is a solution to my problem: I have 200 numbers from 1 to 200 which are linked with text. For example: 1 = car, 2 = bicycle and so on. I would like to write a number (1) ...
0
votes
1answer
15 views

Prevent the stretching from changing cells border in Google spreadsheet

Is it possible to prevent the stretching from changing cells border in Google spreadsheet? I mean that if I stretch a cell which has a border above it, then the same border will be added to the cells ...
0
votes
1answer
12 views

Data Validation for Sheet1 from Sheet2 list

I'm trying to figure out how to validate data entries in one column on sheet 1 from a database I created in sheet 2. In column A sheet 1 the user is allowed to type in a string, upon completion it ...
1
vote
1answer
14 views

Apply a formula to each cell in column before computing a sum of the cells

How could I apply a formula to each cell in a column before computing a sum of the cells in Google spreadsheet? In other words, I have a column of cells with numbers. And what I want is to iterate ...
0
votes
1answer
22 views

VLOOKUP vs IMPORTHTML

I am having a Google Apps Script issue using VLOOKUP on some data generated by the IMPORTHTML function. For normal user interactions this does not seem to be an issue but scripts accessing the ...
1
vote
1answer
18 views

Populating alternatives in question 2 based on answers in question 1

I have ten people who sell something to companies, and I want to track what they do IN GOOGLE FORMS. Every agent has his own 10 companies that he contacts. So, in Question 1, I will have a drop-down ...
0
votes
2answers
15 views

How to compare duration to another duration using IF statements

=IF(TIMEVALUE(H2)<=TIMEVALUE("00:01:00.000"), 150, TIMEVALUE(H2)<=TIMEVALUE("00:01:30.000"),100, IF(TIMEVALUE(H2)<=TIMEVALUE("00:02:00.000"), 75, IF(TIMEVALUE(H2)<=TIMEVALUE("00:02:30....
1
vote
1answer
21 views

Google Sheets function returns an incorrect value

I have a very simple function for every cell in column D, for example: D24 =C24-(B24*N$2) Example with values: D24 =10-(1*6,5) This works; however, not all the time. For cell D24 this returns a ...
-1
votes
3answers
56 views

Finding closest value within a range of cells spread across the rows

Attached the Google Drive link and we have to refer to scenario 2 sheet of the spreadsheet. https://drive.google.com/drive/folders/1HKePsd4SPSNdPujnwKLvq2i8xeg5sQgy Here some value is given in rows ...
1
vote
1answer
19 views

Compare dates from two years in a graph when dates not identical in Google Sheets

I can’t figure out how to compare two data sets with date intervals form each year. Only the start and end date are the same days, the rest is spread over the period. And there can be more points in ...
0
votes
4answers
37 views

Show a Running Target Based on Today

I'm creating a running target from a quarterly Total divided by known trading days Current formula in I4 is =IF(H3<>"",I3+O$30,"") H3 = Yesterday's Sales Numbers I3 = Yesterdays Running Target ...
-1
votes
0answers
17 views

How do I get these Google Sheets values to send to Slack when a checkbox is clicked?

Basically, I need to send every individual value in a column to Slack when the corresponding "Send to Slack" checkbox is checked. I've used Zapier before for a similar task, except the data was ...
0
votes
0answers
24 views

How do I find the value in the first column according to the edited cell? (Google Sheets)

I'm trying to create a sheet in the same workbook that will log changes that are made on the master sheet. Here is the 'Master List' where changes will be made: This following information needs to ...
0
votes
1answer
45 views

How to import data from Investing.com into spreadsheet?

I want to import the live individual stock price in the Google Sheets as highlighted on the image. Is that possible? https://in.investing.com/equities/jagson-airlines-bo
0
votes
2answers
8 views

Import range from 3 sheets/tabs into 4th

I currently have 3 sheets (tabs) in a Google Sheets workbook. Each column header (Row 1) in the 3 sheets are identical. I'd like to create a 4th sheet that combines ALL the contents in the 3 sheets ...
0
votes
1answer
32 views

4 IF AND Statements in one cell

Trying to get two equations to then present one of four options. Example: Have no idea where to start, all four need to sit in the one cell.
1
vote
3answers
37 views

Google Sheets issue with literal constants in functions [duplicate]

When attempting to use any literal strings in formulas I get a Formula parse error and #ERROR! in the column with the formula. For example: =IF(D43=“x”,MULTIPLY(E42,2)). I’ve been told that ...
1
vote
2answers
47 views

Google Sheets Query with Date and Time

I need help with a query. I need to filter on some Google Form data and only show the results that have been submitted AFTER a specific timestamp. Timestamp Cell = J2 - This cell is in the following ...
0
votes
1answer
18 views

Automatically (protect or update or retain) a cell's formula when a row is added from “Zapier and Twilio”

When a row is added in this case from a (Zapier into a Google sheet, the formula in a cell not used by the incoming data is deleted. How can I retain/protect the original formula in column AE being =...