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
4
votes
1 answer

Get specific sheet by its GID in Google Apps Script

With this: getSheetByName('test'); I can select a Sheet by it's name. But how can I select it by the GID which is at the end of the url? In my example: "...edit#gid=1063355045" I already tried something like getSheetById('1063355045'); - but this…
Max Di Campo
  • 408
  • 1
  • 4
  • 12
4
votes
2 answers

In Google Sheets how do I enter the plus sign at the start of a field value?

I'm creating a spreadsheet where one column must hold phone numbers. Some phone numbers have the plus sign at the beginning of the string. However, Attempting to enter a '+' at the start of a field activates a feature of Google Sheets. How do I turn…
bob
  • 753
  • 4
  • 15
  • 27
4
votes
1 answer

Detect Note changes with onChange

Is it possible for Google Sheets to detect when you've edited your notes or inserted a new one using onChange? function onChange(e) { var ss = SpreadsheetApp.getActiveSheet(); var s =…
Brandon Park
  • 77
  • 6
4
votes
4 answers

Regexextract decimal number from text with decimals

I have a simple google sheets REGEXEXTRACT function that extracts numbers from NFL lines and over unders. Here's the problem: The function =VALUE(REGEXEXTRACT(I3,"-*\d*.?\d+")) properly extracts -13.5 from text Line: GB -13.5 But when I use the…
ryanbuckner
  • 77
  • 8
4
votes
3 answers

Google sheets - cross join / cartesian join from two separate columns

Hope that someone can help me with a cartesian product in Google Sheets. I have data in two separate columns and wish to create all possible combinations of the two columns in a separate tab. The first column is ID (text) and the second is date…
William Pontoppidan
  • 67
  • 1
  • 6
4
votes
3 answers

Google spreadsheet left formula with ellipsis

I'm using importxml to import some data from an other website. This results in some cells to show long text. I'm using =LEFT formula to limit the number of characters in a cell but words stop very abruptly and i was wondering if i can add ellipsis…
John Grischam
  • 224
  • 1
  • 19
4
votes
3 answers

Convert 2D range to 1D range in Google Sheets

How can I dynamically rearrange a 2D range in Google Sheets into a 1D range, where the rows are "stacked" on top of each other, like shown below? When I say dynamically, I mean that the range is of a dynamic size using a FILTER, with a unknown…
Nick T
  • 63
  • 8
4
votes
4 answers

How to extract words entirely written in uppercase with accents (Diacritics) with a Google Sheet REGEXEXTRACT formula?

Ok, it looks simple but when the words start or finish with an accents it is the mess. I've looked on Stack Overflow and others and haven't really found a way to solve this problem. I would like, to be able with a Google sheet formula, to extract …
Antonin Thuillier
  • 41
  • 1
4
votes
2 answers

I can't find "name" attribute while inspecting input elements of google form. How to find it?

I have a html form from which I want to store data to google spreadsheet. I created google form but when I try to inspect name attribute to link it with HTML form attribute, I couldn't find any. I want name="entry.###" attribute. Here is what I say…
Muhammad Ahmad Tariq
  • 127
  • 1
  • 10
4
votes
2 answers

Is there a way to authenticate gspread with the default service account?

If I want to create/read/update spreadsheets using gspread, I know first have to authenticate like so: import gspread gc = gspread.service_account() Where I can also specify the filename to point to a service account json key, but is there a way I…
niczky12
  • 4,953
  • 1
  • 24
  • 34
4
votes
3 answers

Disable Spreadsheet copy - Google Sheets

I would like to allow users to use my spreadsheet but not copy it as it contains intellectual property. I tried going to sharing settings and disabling: Editors can change permissions and share Viewers and commenters can see the option to…
michaeldon
  • 507
  • 3
  • 11
4
votes
2 answers

Google Sheets Formula Sometimes does not work with Merged Cells

So I have created an invoice spreadsheet in Google Sheets and have used merged cells extensively in order to create a cleaner look for the invoice. Here is the problem that I am having: sometimes, my simple subtraction formula does not give me a…
MuteThunder
  • 43
  • 6
4
votes
5 answers

React JS and Google Spreadsheets: Cannot Read Property '2' of Null

I'm currently working with React JS and Google Spreadsheets and am following something similar to this article. However, I'm running into a problem. Whenever I try to connect to my spreadsheet, my React app runs successfully for a few seconds and…
Tejas_hooray
  • 606
  • 1
  • 6
  • 16
4
votes
2 answers

Google Sheets - sumif using condition on row and column

I have a spreadsheet that looks like the following TABLE 1 ID/Month | May | June | July | August | September | October ID101 | 30 | 50 | 50 | 80 | 20 | 60 ID201 | 20 | 30 | 10 | 40 | 30 | 50 ID101 | 10 |…
song
  • 83
  • 2
  • 7
4
votes
2 answers

How to set the border-bottom-style only on last row of table in HTML?

I have this HTML code that will display a table consists of rows of filtered data fetched from a google sheet. The filter is based on the value in column 'qty' where the criteria are just to show rows of datarange when the qty value is not zero.…
dell
  • 171
  • 13
1 2 3
…
99
100