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.

1,462 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
-1
votes
2answers
4k 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
69 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
54 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
103 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
742 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
1answer
45 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
369 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
66 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
587 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
140 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
371 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
152 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
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
555 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
118 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
868 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
514 views

Vlookup in external spreadsheet

Is it possible to use VLOOKUP with an external spreadsheet? SpreadsheetA contains a set of a few hundred addresses. SpreadsheetB is an invoice. I want to use VLOOKUP in the invoice to return ...
-1
votes
1answer
42 views

Google Spreadsheet Separating Rows By Names

I am trying to follow my own mutual fund purchases and trying to input the results into a spreadsheet to calculate the XIRR (internal rate of return). I am planning on entering my data for every ...
-1
votes
1answer
630 views

How to use getContactGroups() to list all groups the contacts belongs to

I've the following script which grabs my contacts: function onOpen() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = []; menuEntries.push( {name: "Read Contacts", ...
-1
votes
2answers
97 views

How to move a Google Sheet from one file to another while retaining external references

Background I usually add several sheets to a single Google sheets file. There is always a separate sheet that extracts information from the other sheets (via REGEXEXTRACT etc). Problem sometimes ...
-1
votes
1answer
1k views

How to use Now() to subtract from column of date/time to get hour difference

I have searched a bit and can't figure this out. I've tried using Now() in the formula along with putting it in a cell and running the formula against it. I have a column of date/times. I would ...
-1
votes
1answer
2k views

How to freeze a row until a similar row shows up on Google Sheets?

I would like to make an effect similar to how for example iOS apps persist a table's row until someone has scrolled beside it. example This behavior happens all the time in iOS apps. You can see it ...
-1
votes
1answer
2k views

Need Help With a Script to Automatically Insert Rows in a Sheet Based on Other Rows/ Data Inserted in Another Sheet

I have a sheet titled 'Raw_Data' that I insert data into every day by inserting new rows above the data from the last day. I have another sheet titled 'Overall_Formulas' that populates formulas based ...
-1
votes
1answer
806 views

Google Sheets trendline broken; file unavailable

In Google Sheets, I've created a line graph. I want to add a trend line. I do so by clicking the blue line and selecting Linear from the trendline dropdown. No trendline appears. Then when I right ...
-1
votes
1answer
92 views

Update values on a second spreadsheet and automatically link and update values in the first spreadsheet

I have two spreadsheets: Spreadsheet 1, the initial data is entered manually. I do an IMPORTRANGE into Spreadsheet 2 with order by Col2. This is what I get when I perform IMPORTRANGE (sort on ID): ...
-1
votes
1answer
214 views

Google Sheet Query returns first 3 rows in one row

I have made a simple query that combines data from 4 separate sheets: =query({'1.MA'!A13:O200;'2.PF'!A13:O200;'3.BM'!A13:O200;'4.C'!A13:O200},"Select * where Col2 is not null ") The objective is to ...
-1
votes
1answer
12 views

Validate that a combination of cells are unique in a row in Google Sheets

I'm looking accomplish the following but in a Google sheet. https://superuser.com/questions/902400/validate-that-a-combination-of-cells-are-unique-in-a-row-in-excel I have a column of first names ...
-1
votes
1answer
16 views

Multiplying several numbers within a text string, is this possible?

I have cell A1 which is a text string: 100 blue cars, 100 red cars, 100 yellow cars In cell B1 I have a number which is a multiplier say 2. Is it possible to change the numbers within cell A1 ...
-1
votes
1answer
172 views

Force `importrange()' to update data

I'm using the importrange() formula inside of Google Sheets to import a bunch of data from multiple spreadsheets into one sheet. On this one sheet I'm using circa 20 importrange() formula's each ...
-1
votes
2answers
52 views

IF and THEN statements on spreadsheets using color

I am creating spreadsheets with multiple tabs for children to work through. To show they have completed each tab, when they have finished the work correctly I am using the following statements: =IF(...
-1
votes
1answer
30 views

Description based on each checkbox “checked” to populate into a summary on the 2nd tab (sheet)

I am building a spreadsheet that has to tab (sheets), the 1st I have an "assessment checklist" with about 250-30 checkboxes based on what is found. Example (Checkbox is in "A" column and description ...
-1
votes
1answer
53 views

Convert string to URL in column

I just want to convert IDs as strings to URLs automatically. I tried this: =HYPERLINK(CONCATENATE("http://myjira.com/projects/BUWDI/issues/";A2);A2) I got an error... What do you think?
-1
votes
1answer
512 views

Reconnecting form responses to current sheet after file move to Team Drive

I have searched for an answer for the "current" Google Forms problem I am having. It seems the answers here for this issue are outdated as Google keeps changing their technology about as fast as I ...
-1
votes
1answer
71 views

Google Script Range Error - Owner Range?

I've been playing around with Google Scripts and I'm trying to pull some data into a sheet. It all seems to check out except one line where I get a range error, I know (or I think I do) that it has ...
-1
votes
2answers
1k views

How to make date automatically display only day number and suffix (1st, 2nd, etc.)?

Title explains it. I need to know how to create a custom number format in which a cell would display only the date and suffix of a full date entered into the cell example:1/05/2019 -> 5th I have ...
-1
votes
1answer
134 views

Importing selective rows from JSON to Google Sheets and avg

I would like to import two rows from JSON into a Google Sheet. The JSON is structured like this: {"type":"A","features":[{"type":"Feature","properties":{"countA":27,"name":"NameA","countB":2},"...
-1
votes
1answer
94 views

Getting e-mail adresses using a filter in Google Sheets /w scripts

I am trying to extra email replies from gmail. I already found some code that is supposed to work. My question is: how can one expand it to support a filter as a variable? (in this case so a filter ...
-1
votes
3answers
769 views

How to run multiple OnEdit scripts in the same sheet?

I am using an auto-timestamp script in Google Sheets. Where when you input something in a particular cell, the script will automatically add a timestamp in a particular cell. But the problem is that I ...
-1
votes
2answers
2k views

IMPORTRANGE row limit?

I have a document where I am trying to mirror some data from one Google sheet to another. I have over 1000 rows in the first document; when I use the IMPORTRANGE formula it works fine as long as the ...
-1
votes
1answer
132 views

Formula for adding columns of a row till the last column in Google Spreadsheet

I want to sum the columns of a row and display the sum in the last column. As shown in the example below sum of each row is displayed in column TOTAL COL1 COL2 COL3 COL4 TOTAL ...
-2
votes
1answer
34 views

How to select multiple individual cells on Google Sheets on my Android mobile phone?

How to select multiple individual cells on Google Sheets on my Android mobile phone? Example: On Windows it is possible by using the CTRL button + left mouse button click, but I did not find any ...
-2
votes
1answer
46 views

Is there a spreadsheet function, than means same formula as a specified cell?

In google sheets. I wish to have the same function in several cells. I could copy the function to all the other cells (this will work). However if I want to change the function, then I need to copy it ...
-2
votes
1answer
28 views

Google Sheets: How can I query all names and display it in another sheet according to it's category?

So I have an employee list and a checkbox according to their skills. How can I get the employee list and display them to another sheet?
-2
votes
2answers
42 views

Allow users to make yes/no decisions using checkboxes

I'm looking for a way my users can interact with my scripts. I'd like to use checkboxes to work like a "yes/no" command, where the script does "A" if the checkbox is ticked, and &...
-2
votes
1answer
27 views

Google Sheets atomisation

I hope you are all well and healthy! I am looking for a solution in Google Sheets to automate something like this: and it could auto generate something like this Thank you in advance
-2
votes
1answer
15 views

Import Values and Sort them by there Type

I have a little trouble with Google Drive sheets / forms. I need to import the answer from one of our forms into another document. The main problem for me is now, I only need the import if the game ...
-2
votes
1answer
14 views

Calculate conditional total

I would like to compute for the total unpaid (status is a checkbox,true/false) for any specific person. Name Amount Paid Me 100 ✓ You 20 (unchecked/False) Me 50 ...
-2
votes
1answer
28 views

Certain cells do not 'Work' in formulas

So I'll just dive right in. A cell(s) won't work in a formula, for whatever reason. I've tried everything, including duplicating the data to a new section in the spreadsheet, resetting the font/...