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
52
votes
4 answers

Google Spreadsheet, copy column in sheet to column in another sheet

I'm trying to figure out how to have the entire contents of a column in one sheet show in another sheet. For example I have column D in sheet2 that I want to show in column A of sheet1. Something like: =ARRAYFORMULA(Sheet2!D) But that gives me a…
JuggaloBrotha
  • 535
  • 1
  • 5
  • 6
52
votes
7 answers

Number of days between past date and current date in Google spreadsheet

I want to calculate the number of days passed between past date and a current date. My past date is in the format dd/mm/yyyy format. I have used below mentioned formulas but giving the proper output. =DAYS360(A2,TODAY()) =MINUS(D2,TODAY()) In the…
vkrams
  • 7,267
  • 17
  • 79
  • 129
51
votes
10 answers

Google Spreadsheet sum which always ends on the cell above

How to create a Google Spreadsheet sum() which always ends on the cell above, even when new cells are added? I have several such calculations to make on each single column so solutions like this won't help. Example: On column B, I have several…
Druvision
  • 1,463
  • 1
  • 15
  • 16
50
votes
7 answers

How can I create a hyperlink in the middle of cell text?

In google spreadsheets, it is possible to add a hyperlink to a cell as: =hyperlink("www.google.com", "search the web") This will show the text search the web which will hyperlink to google. However, I am wondering: How can this rule be inserting…
dublintech
  • 16,815
  • 29
  • 84
  • 115
50
votes
4 answers

Split string and get last element

Let's say I have a column which has values like: foo/bar chunky/bacon/flavor /baz/quz/qux/bax I.e. a variable number of strings separated by /. In another column I want to get the last element from each of these strings, after they have been split…
Max Williams
  • 32,435
  • 31
  • 130
  • 197
49
votes
4 answers

Google Sheets API: How to find a row by value and update it's content

I am working on an Android application that uses a Google Spreadsheet as a database. The application should GET, APPEND and UPDATE values in a spreadsheet, using the Sheets API v4. The first two functions are working fine but I have difficulties…
ferenckovacsx
  • 611
  • 1
  • 5
  • 6
49
votes
6 answers

Get Google Sheet by ID?

I know that Google Apps Script has a getSheetId() method for the Sheet Class, but is there any way to select a sheet within a spreadsheet by referencing the ID? I don't see anything like getSheetById() in the Spreadsheet Class documentation.
Finn Smith
  • 772
  • 2
  • 7
  • 21
49
votes
2 answers

Debugging a custom function in Google Apps Script

I am trying to create my first custom function for a Google Spreadsheet in Apps Script and I am having a hard time using the debugger. I am working on the custom function demo code from the Google documentation and I have set a breakpoint in the…
Jpsy
  • 20,077
  • 7
  • 118
  • 115
48
votes
6 answers

Counting number of occurrences in column?

What would be a good approach to calculate the number of occurrences in a spreadsheet column? Can this be done with a single array formula? Example (column A is input, columns B and C are to be auto-generated): | A | B | C …
feklee
  • 7,555
  • 9
  • 54
  • 72
47
votes
6 answers

Summarize grouping by year and month

I have a worksheet Movements with two fields: mydate | amount 2009-01-01 | 10.00 2009-01-02 | 13.00 2009-02-01 | 11.00 2009-02-05 | 12.00 2009-02-08 | 52.00 I want to have in another worksheet MonthSum that displays the sums of the data…
nulll
  • 1,465
  • 1
  • 17
  • 28
47
votes
6 answers

How to edit legend labels in google spreadsheet plots?

I'm trying to plot some data in Google spreadsheet: And as you may see all of the series are in a same column and I can't use the any of the rows as headers. My plot looks like this: I would appreciate if you could help me know how I can edit/add…
Foad S. Farimani
  • 12,396
  • 15
  • 78
  • 193
46
votes
10 answers

New Google Spreadsheets publish limitation

I am testing the new Google Spreadsheets as there is a new feature I really need: the 200 sheets limit has been lifted (more info here: https://support.google.com/drive/answer/3541068). However, I can't publish a spreadsheet to CSV like you can in…
steven.levey
  • 836
  • 2
  • 10
  • 21
45
votes
3 answers

how do I find the values that are in one column but not in the other

I have two columns A and B in a google spreadsheet. I want to find all the numbers that are in column A but not in B. How do I do that? B could have the same numbers from column A. So if column A has numbers: 1, 2, 3 and B has numbers 3, 4,5 I want…
user816604
44
votes
7 answers

Timezone conversion in a Google spreadsheet

I know this looks simple. In a Google spreadsheet, I have a column where I enter time in one timezone (GMT) And another column should automatically get time in another time zone(Pacific Time) GMT | PT ----------|------------ 5:00 AM | 9:00…
SangyK
  • 799
  • 2
  • 6
  • 16
44
votes
4 answers

How to access Google Spreadsheets with a service account credentials?

I have created a server side application in PHP that's supposed to work with Google Spreadsheets. I'm able to authenticate successfully with OAuth 2.0 authentication, but when requesting the list of the spreadsheets from Google, I only get the…
ZurabWeb
  • 1,241
  • 1
  • 12
  • 21
1 2 3
…
99 100