Stack Overflow
Stack Overflow

Questions tagged [google-sheets]

Do NOT share spreadsheets/images as the only source of data. Use markdown TEXT tables instead. Use this tag for questions about programmatically interacting with Google Sheets. Use with: [google-apps-script] for questions relating to the built-in scripting language, [google-sheets-formula] for questions relating to formula design or [google-sheets-api] and a language tag (eg:[python]) for questions relating to sheets API usage. Do NOT use with [excel].

Google Sheets is a cloud-based application and service for creating and collaborating on spreadsheet documents. The service supports an internal API for Google Apps Script as well as an External API.

Sharing your data:

Sharing your data helps other community members in visualizing your data. This can be done through

  • Tables(Mandatory):
    Markdown help can be found here. You can easily create a table using the formula: =ARRAYFORMULA("|"&A1:G20), if you want to share A1:G20. However, the first row A1:G1 must be a header row AND the second row A2:G2 should only contain dashes -- in all the cells.

  • CSV:
    Use File > Export to csv

  • Published Google sheets(in addition to text table):
    In the sheets file, click File > Share > Publish to web.

  • Share to others(in addition to text table):
    In the sheets file, click File > Share to others > Anyone with link. Note, however that sharing Google sheets this way makes your email address visible to public.

  • Screenshots(in addition to text table):
    This shows your data structure but makes it hard for anyone to copy data from the question for testing

If questions depends on external links/images, they will be closed. Text tables is mandatory.

Related tags:

52687 questions
539
votes
7 answers

Conditional formatting based on another cell's value

I'm using Google Sheets for a daily dashboard. What I need is to change the background color of cell B5 based on the value of another cell - C5. If C5 is greater than 80% then the background color is green but if it's below, it will be amber/red. …
Mark Magalona
  • 5,429
  • 2
  • 13
  • 5
296
votes
6 answers

How to highlight cell if value duplicate in same column for google spreadsheet?

I am looking for formula for google spreadsheet highlight cell if value duplicate in same column can anyone please assist me for this query?
user3331309
  • 2,977
  • 2
  • 12
  • 4
224
votes
21 answers

Get the last non-empty cell in a column in Google Sheets

I use the following function =DAYS360(A2, A35) to calculate the difference between two dates in my column. However, the column is ever expanding and I currently have to manually change 'A35' as I update my spreadsheet. Is there a way (in Google…
Michael S
  • 4,352
  • 5
  • 23
  • 29
216
votes
24 answers

Import data into Google Colaboratory

What are the common ways to import private data into Google Colaboratory notebooks? Is it possible to import a non-public Google sheet? You can't read from system files. The introductory docs link to a guide on using BigQuery, but that seems a…
Grae
  • 2,308
  • 2
  • 13
  • 10
203
votes
8 answers

Count cells that contain any text

I want to count the cells that contain anything within a range. Any cell that contain text, or numbers or something else should do a plus one in my result-cell. I found this function, countif(range; criteria) but this doesn't work for me, because I…
steak
  • 2,117
  • 2
  • 13
  • 12
194
votes
8 answers

Google Sheets API returns "The caller does not have permission" when using server key

I've generated a server key in the API Manager and attempted to execute the following on my Mac: curl 'https://sheets.googleapis.com/v4/spreadsheets/MySheetID?ranges=A1:B5&key=TheServerKeyIGeneratedInAPIManager' But this is what it returns: { …
Instabrite
  • 2,079
  • 2
  • 10
  • 7
194
votes
4 answers

Get first item from Split()

I use the SPLIT function to split a string which looks something like 1.23/1.15. Right now it yields two cells, as it should. But how do I get a certain element from the result? I would like to do something like this: SPLIT("1.23/1.15", "/")[0] to…
filur
  • 2,116
  • 6
  • 24
  • 47
174
votes
17 answers

Apply formula to the entire column

I'm changing all the zip codes from Column A into Column B with the formula: =TEXT(A1,"00000") Like this: I want every cell in Column B to be transformed using the formula above. But I have over 40,000 rows, so it is not feasible to drag the…
skydv
  • 1,789
  • 3
  • 12
  • 7
167
votes
13 answers

How to debug Google Apps Script (aka where does Logger.log log to?)

In Google Sheets, you can add some scripting functionality. I'm adding something for the onEdit event, but I can't tell if it's working. As far as I can tell, you can't debug a live event from Google Sheets, so you have to do it from the debugger,…
d0c_s4vage
  • 3,947
  • 6
  • 23
  • 32
155
votes
3 answers

How to get the correct range to set the value to a cell?

I want to set text or number in Google Sheet from script. I want to set Hello or number 9 in cell F2. I found this code so far: SpreadsheetApp.getActiveRange().setValue('hello'); but that doesn't specify which cell.
user635600
150
votes
11 answers

How can I do time/hours arithmetic in Google Spreadsheet?

How do I do time/hour arithmetic in a Google spreadsheet? I have a value that is time (e.g., 36:00:00) and I want to divide it by another time (e.g., 3:00:00) and get 12. If I divide just one by the other, I get 288:00:00 when what I want is 12 (or…
user128807
  • 10,447
  • 17
  • 53
  • 72
146
votes
5 answers

Displaying Lakhs and Crores in Google Sheets

I would like to use Google Sheets for its convenient global access, but I really need to display numbers in Lakh and Crore (Indian style) format to avoid confusion among managers. 150,000 should display as 1,50,000 and 12,000,000 should display as…
Adam Starrh
  • 6,428
  • 8
  • 50
  • 89
143
votes
9 answers

Google SpreadSheet Query: Can I remove column header?

I'm doing this query at my google spreadsheet: =QUERY(H4:L35;"select sum(L) where H='First Week'"; -1) But it returns a little table with "sum" as header and result below it. What I want is just the result! How I remove header? Can I?
Poliane Brito
  • 2,142
  • 3
  • 18
  • 26
138
votes
13 answers

Count rows with not empty value

In a Google Spreadsheet: How can I count the rows of a given area that have a value? All hints about this I found up to now lead to formulas that do count the rows which have a not empty content (including formula), but a cell with =IF(1=2;"";"") …
Robbit
  • 1,537
  • 2
  • 9
  • 10
127
votes
24 answers

Convert column index into corresponding column letter

I need to convert a Google Spreadsheet column index into its corresponding letter value, for example, given a spreadsheet: I need to do this (this function obviously does not exist, it's an example): getColumnLetterByIndex(4); // this should…
BeNdErR
  • 17,471
  • 21
  • 72
  • 103
1
2 3
…
99 100