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. Please consider adding a demo spreadsheet to your question, this will greatly increase the chances to get a satisfactory answer.

Filter by
Sorted by
Tagged with
-1
votes
1answer
22 views

How to navigate to a specific sheet in Google Sheets based upon a cell value

What I am hoping to do is take the =Now() date that is located on my default sheet and in the Row 1 Column 1 cell and somehow when the sheet is accessed onOpen() have it automatically change the ...
-1
votes
1answer
24 views

Indexing in Google Sheets

I wanted to find a way to add a string into the middle of emails in Google sheets. For example: We have 5 rows: bus@email.com car@email.com truck@email.com train@email.com plane@email.com I ...
-1
votes
1answer
669 views

Avoiding an authorization request for Google Scripts

I'm in the midst of trying to custom code some script that will automatically hide some sheets from some users in a shared company spreadsheet. Yesterday it seemed to be working fine, but now it seems ...
-1
votes
1answer
130 views

Setting ImportRange on a delay

Is it possible to set a timer or delay for the importrange function on Google Sheets?
-1
votes
1answer
1k views

In Google sheets, how to calculate elapsed duration between two cells, each containing date and time and display as days and hours

There is a built-in duration format, but Elapsed days is not an option. I want to display "2 days, 3 hours"
-1
votes
1answer
14 views

DAY function does not work as expected in google sheet

I have a issue with Day function; I am extracting date from text string and storing it in a column; say Sep 30, or Sep 23; I want to see DAY of this date and put it in next column; something like ...
-1
votes
1answer
34 views

Return the column header of the last >0 value in a row

I need help with a Google Sheets formula to return the column header of the last >0 value in a row (the yellow highlighted cells below are hard-keyed). Example:
-1
votes
1answer
558 views

Unprotect ranges [duplicate]

I am trying to create a script to protect my formulas in sheets that co-workers will use. I have 10 workbooks with 26 sheets each, all are virtually identical as far as the the locations of the ...
-1
votes
1answer
336 views

How to find the closest date to today from a list of dates

I have a list of dates on column A. Each day I need to automatically put the date of today or the next closest date on cell B1. 20/8/2018 6/9/2018 25/9/2018 28/9/2018 11/10/2018 30/10/2018 31/10/2018 ...
-1
votes
2answers
223 views

Using SUMIF to add amounts over a given number

I am trying to calculate the total number of overtime hours worked by staff each week. I have a formula that seems to do the job OK, but I feel there could be a better or more concise way to write it....
-1
votes
1answer
47 views

Can you set a cell to have a price on it linked to the main website?

I work in the parts department of an auto-repair shop and have been charged with organizing everything and creating an inventory sheet. I have done all of that and am now trying to improve the system ...
-1
votes
1answer
199 views

Having syntax trouble with IMPORTXML Google Sheets function

I have been studying the various IMPORTXML examples posted however I have not been able to solve for the actual data I wish to pull in to my sheet. The page is here: https://aemo.com.au/Electricity/...
-1
votes
1answer
24 views

Lookup using multi criteria

If anyone can help with this: sheeet2 has the data sheet4 is how I want to see the data. Need to search by column A and row 1 to get the value in sheet2 column E Sample spreadsheet
-1
votes
1answer
47 views

One time check to see if document is shared with a specific user

I'm trying to figure out how to write an Apps Script that checks "IF" a Google Drive URL is shared with a specific user. I do not want this to be a continuous check, just a "one time check". Example ...
-1
votes
1answer
27 views

Google Sheets Column Wrap

I have 3 columns of data but I don't want them to carry on down the page. I want the columns to fill the page (3 rows of 3 columns) and then carry on to the next page. Is this possible? Added image ...
-1
votes
1answer
331 views

Dynamic VLOOKUP + COLUMN + IMPORTRANGE

I need to keep updated a COLUMN number into VLOOKUP formula with IMPORTRANGE. =vlookup(B5;IMPORTRANGE(url;"RIASSUNTIVO!B:CC"); column(IMPORTRANGE(url;"RIASSUNTIVO!C1:C10"));FALSE) The COLUMN ...
-1
votes
1answer
162 views

Return temperature data in Google Sheets for a specific time of day for each day of the month

I have a column for date and time formatted like so: [3/4/18 1:09] (verified that this is stored as date/time values), and a column for the temperature data at that time in °C like so: [11.536] There ...
-1
votes
1answer
132 views

getEditResponseUrls - hyperlinks from second linked form not inserting

I am new to scripts, but I was able to find a one that uses getEditResponseUrls to insert the edit link from a form into a Google Sheet. I've now been asked to add a second sheet to my spreadsheet ...
-1
votes
1answer
31 views

How to make a value appear based on 2 variables

I've been having touble trying to calculate a price that is defined by 2 variables. e.g. "Variable 1 is the brand of chocolate bar" and "variable 2 is the type of chocolate bar" and the value is the ...
-1
votes
1answer
114 views

Importxml convert content only

I am getting the price value from the website. I would like to make the value as static(remove formulas) to prevent auto refresh in sheet due to that the price values will change-- I don't want it. ...
-1
votes
1answer
65 views

INDEX(GoogleFinance(“USDIDR”, “PRICE”, F530),2,2) F530 = cell with date

INDEX(GoogleFinance("USDIDR", "PRICE", F530),2,2) F530 = cell with date (formatted to date), please help, how to apply above formula for stock listed in Indian markets ( NSE or BSE)?
-1
votes
1answer
78 views

Count times user won where user is in team

I have a list of teams with a list of players next to it (Would it be better/easier if the players are in a single cell?) +Team Name+--------+--------+ | Team 1 |Player 1|Player 2| | Team 2 |...
-1
votes
1answer
100 views

Apply the conditional formatting formulas across every column of the sheet

I've got a Google Sheet that I'm trying to write some formulas for - it's basically just a bunch of numbers in columns that I'm trying to do some conditional formatting on (per column). I want to be ...
-1
votes
2answers
2k views

I got this “Error: Imported content is empty”

I am trying to create a google sheet to combine some information. I want the sheet to pull information h index of my friend this is the code =IMPORTXML("https://www.scopus.com/authid/detail.uri?...
-1
votes
1answer
59 views

How to pull an exchange rate price on Poloniex to google spreadsheet

I have a Google spreadsheet where I compute my earnings based on the exchange rate of an exchange, specifically Poloniex.com. Atm, I manually check the rates on the website and enter it manually on my ...
-1
votes
1answer
47 views

all email addresses of file FileA is added to the group email called test@test.de

I would like to modify the following script so that all email addresses of a column google drive file called Finance is added to the group email called test@test.de. How should I change the script? ...
-1
votes
1answer
209 views

Awesome table function

Is there an option in awesome-table.com with the edit links such that the result of the form will be to create a new line in the spreadsheet and only alter the values instead of editing the existing? ...
-1
votes
1answer
344 views

Why does my ARRAYFORMULA return this error?

Edit: I've completely rewritten by question because it turned out a syntax mistake was giving me the error my question was originally about, but it wasn't the direct reason my formula wasn't working. ...
-1
votes
1answer
88 views

Leave blank combined with IF data entry

I'd like the following scenario resolved if possible. I have the following formula; =if(B3>C3,"3",if(B3<C3,"0",if(B3=C3,"1"))) So that the result of football matches enters either 3, 1 or 0 ...
-1
votes
1answer
597 views

How to quote formula result?

I have a function which returns a URL from hyperlink as a first parameter to IMPORTXML function. For the function to work I need to put the output of that function in quotes, how do I do it?
-1
votes
2answers
356 views

Sum certain numbers in a range of cells that contain text and numbers

I've got a range of cells in Google Sheets that contain a mixture of text and numbers, or just text, or even blank cells. For example: Yes 32 Yes 46 Yes No 1 No Apologies 23 Apologies n/a (and blank ...
-1
votes
2answers
414 views

How to use relative references in an ARRAYFORMULA with DSUM

I have a formula: Total Hours: =DSUM(data!A1:B, "Number of Hours", {"Provider Name";A2}) The column A is a dynamic list of all the providers. How do I use an array formula to fill in all the rows ...
-1
votes
1answer
43 views

Filter but maintain duplicates

I am trying to filter a one larger range from a smaller range. The master range has duplicate entries, however, so when I try to filter from it, it pulls out all duplicates when I'd rather have it ...
-1
votes
1answer
53 views

Would Google Sheets LOOKUP recognize 0-50 as 0 through 50?

I was wondering the possibility of an answer to the question in my title. The formula I have is: =LOOKUP(A1, E1:E3, F1:F3) Here, A1 can be any value from say 0 to 1000. E1:E3 contain ...
-1
votes
1answer
328 views

Transposing with header row

In Google Sheets I'm trying to transpose a list of items in rows to display as columns. I am looking for a way to transpose the Category field to act as a header row that extends over all the items of ...
-1
votes
1answer
64 views

Formula for: Run INDEX - Search it for several specified strings - replace them with specified strings

Here is a sample spreadsheet I threw together that shows more or less what I'm working with. I have two sets of two similar formulas that produce INDEX outputs from the same set of data. The first ...
-1
votes
1answer
394 views

Google Apps Script for converting military Timestamp into 12-Hr in Forms submissions?

Has anyone created a script to use in Google Forms/Sheets to convert or display the military time to 12-hour instead once the form has been submitted by a user?
-1
votes
1answer
1k views

How can I protect an Apps Script with triggers and authorizations in a shared Google Sheet? [duplicate]

I have a Google Sheet that is shared via URL. It's a list of events. I wrote a script that syncs the list to a shared Google Calendar. The script has an installable on-edit trigger to update the ...
-1
votes
1answer
449 views

Query a column where numbers are mixed with blanks

I have a query where in a column there are numbers and blanks. It seems that the query function is treating everything in the column as string and not numbers. So when I sort, it doesn't sort ...
-1
votes
1answer
137 views

If contains, countifs?

Is there a formula which would allow me to automatically put the answer in the place where it says *answer here * ? For example it analyzes column 2 to see where the Y which would be row 2 and thus ...
-1
votes
1answer
3k views

Conditional formatting with multiple criteria over a range of cells

There is another thread about this but all of the answers there aren't helping. In the range: D12:AY500 - I have a matrix of numbers such that: y=Date, x=Time: .. I would like it so the cells ...
-1
votes
1answer
280 views

Google Spreadsheets formula should I use for highlight more than an 1hour passed between cells?

I need help with a Google spreadsheet formula. This is what I'm trying to do: Highlight the cells in “Conditional Formatting” where the time spent between 2 cells is more than 60min. In this case ...
-1
votes
1answer
133 views

Insert new cell into column B and “shift down” values every-time cell A1 changes in Google Sheets

I'm using Google sheets. I need to automatically add a cell above the existing B1 and shift the values down every-time the value in cell A1 changes. Column "A" is being populated by a google form. ...
-1
votes
1answer
40 views

How do I find a product name from a product key?

How do I get individual labels (names) for different products keys? I want to detect the product key and based on that assign the right product name to it. If it was just one product key in question,...
-1
votes
2answers
2k views

Changing cell based on date in another cell

I'm trying to work out a price sheet for some equipment I'm buying. I would like to have a cell change it's date based on today's date that's in columns so it can show me today's date as a quick ...
-1
votes
1answer
39 views

Automatically fill a cell's verification list with existing formatting

I have a lot of items to add into a list. They are things like "0 - Testing", "1 - Verification", "10 - Lessons", etc. I have all of these items arranged in cells and named out with proper formatting ...
-1
votes
1answer
457 views

index(split(ImportXML formula works in a spreadsheet of mine, not in another?

=index(split(ImportXML("http://finance.google.com/finance?q=" & V9, "//td[@data-snapfield='latest_dividend-dividend_yield']/following-sibling::*"), "/"),1,2) / 100 This formula works in a ...
-1
votes
1answer
112 views

A date column in Google Sheets, e.g column B, that fills itself when I enter a value to its neighbour cell in column A

I need to create a date column that automatically returns the date in which I filled its neighbour cell. For example, when I enter data to cell A2, B2 will return the date the data was entered to A2.
-1
votes
1answer
739 views

Using date as filter for my query formula

I was having problem with my query formula. it does not filter the range of my values which depends on the date ranges that I have put up. Here is my formula: =query(all!B2:G, "select B,C,D,G where ...
-1
votes
1answer
39 views

Is there a Google Sheets functions equivalent of mysql_fetch_row? [duplicate]

Is there any way to get the row result in Google just like mysql_fetch_row that returns only the row base on query statement? I hope there has a formula for this. Using this formula =Query(...