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
58
votes
10 answers

Importing data into R from google spreadsheet

There seems to be a change in the google spreadsheet publishing options. It is no longer possible to publish to the web as csv or tab file (see this recent post). Thus the usual way to use RCurl to import data into R from a google spreadsheed does…
jokel
  • 982
  • 3
  • 11
  • 15
57
votes
7 answers

find & replace commas with newline on Google Spreadsheet

I've been trying to figure out how to replace commas with line-breaks. I've tried using (/r/n/), char(10), the Unicode representation (can't remember now), but it rather predictably replaces the comma with whatever I type in the other box, not…
Dani
  • 2,480
  • 3
  • 21
  • 27
57
votes
4 answers

Suppress #N/A returned by Google Sheets vlookup

I have a Google Sheet (example) with a basic vlookup to create a summable column. It returns "#N/A" for every search key not found, and attaches the following error to those cells: Error Did not find value 'me@me.com' in VLOOKUP evaluation. After…
Skipwave
  • 603
  • 1
  • 5
  • 7
57
votes
3 answers

Count occurrences of given character per cell

Question For example if I wanted to count the number of Ns in a column of strings how can I do this in Google Spreadsheets at a per cell basis (i.e. a formula that points at one cell at a time that I can drag down)? Background I'm having to decide…
hello_there_andy
  • 2,039
  • 2
  • 21
  • 51
57
votes
6 answers

Create a new sheet in a Google Sheets with Google Apps Script

How to create a new sheet in a Google Sheets with Google Apps Script? I know it seems obvious but I just want to create a new sheet with a specific name.
Léo Davesne
  • 2,103
  • 1
  • 21
  • 24
57
votes
7 answers

Hidden field in a Google Form

I am adding a unique ID to each submission of the form. Right now, I made that ID the first field, and pre-filled it with the ID, along with a help text asking the user not to modify this field. Is there any option by which I can not show this…
Kira
  • 955
  • 1
  • 11
  • 25
56
votes
9 answers

How to create custom keyboard shortcuts for google app script functions?

I'm trying to get a sense of the viability of replacing some of my Microsoft Excel spreadsheets with Google Doc Spreadsheets. How can I create a custom keyboard shortcut to a google app script function in a google docs spreadsheet? This is…
User
  • 62,498
  • 72
  • 186
  • 247
55
votes
5 answers

How to label scatterplot points by name?

I am trying to figure out how to get labels to show on either Google sheets, Excel, or Numbers. I have information that looks like this name|x_val|y_val ---------------- a | 1| 1 b | 2| 4 c | 1| 2 Then I would want my final…
loonyuni
  • 1,373
  • 3
  • 16
  • 24
54
votes
8 answers

Stacking multiple columns on to one?

I am using Google SpreadSheet, and I'm trying to have multiple sheets containg a list of words. On the final sheet, I would like to create a summative list, which is a combination of all the values in the column. I got it sort working using…
abritez
  • 2,616
  • 3
  • 29
  • 36
54
votes
6 answers

How to convert a date to a string in Google Sheet

I am trying to figure out how to convert a date into a string in a google sheet. I have one date field that has varying formats. I want create another column that's literally just the same but as a text. For example, if I had the following data date…
Vincent
  • 7,808
  • 13
  • 49
  • 63
54
votes
4 answers

Find Cell Matching Value And Return Rownumber

The employee sheet contains the name of the employee in cell C2. The name of the employee should also be on the data sheet in the range B3:B153. How can I get the rownumber of the cell on the data sheet that matches the employee name? I tried the…
LennartB
  • 559
  • 1
  • 5
  • 6
54
votes
4 answers

How to take only certain columns from a FILTER result?

I'm using FILTER to extract rows from a range, and want to take only certain columns. For example, I filter by D, but want only columns B,C in reverse order. I tried to use QUERY: =QUERY(filter(B:D,D:D>=2), "select C,B") - Error: can't analyze query…
Jonathan
  • 6,939
  • 4
  • 44
  • 61
53
votes
2 answers

Sheet.getRange(1,1,1,12) what does the numbers in bracket specify?

Sheet.getRange(1,1,1,12) I cannot understand the arguments 1,1,1,12 . What is this - the sheet id or row or what? method getRange(row, column, optNumRows, optNumColumns) here what does optNumRows and optNumColumns mean???
Sneha Grewal
  • 581
  • 1
  • 6
  • 8
52
votes
15 answers

How to Force New Google Spreadsheets to refresh and recalculate?

There were some codes written for this purpose but with the new add-ons they are no longer applicable.
iddo
  • 551
  • 1
  • 4
  • 5
52
votes
7 answers

On Google Spreadsheet how can you query 'GoogleFinance' for a past exchange rate?

I'd like to know if it is possible to query a past exchange rate on Google Spreadsheet. For example; using formula =GoogleFinance("CURRENCY:USDEUR") will return the USD/EUR rate at this present moment. How can you retrieve a historic rate?
antr
  • 867
  • 1
  • 9
  • 19
1 2 3
…
99 100