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
39
votes
2 answers

GoogleFinance often returns #N/A and internal error messages while getting stock quotes

Anyone know workarounds to make GoogleFinance actually work? It works for a while so it is not a problem with my formulas but then periodically the cells that were showing stock quotes suddenly show "#N/A" and if you hover over them it shows that…
stackonfire
  • 1,525
  • 4
  • 17
  • 23
39
votes
5 answers

You do not have permission to call openById

Problem: When I run the script, Google tells me, You do not have permission to call openById I had copied a script from another one of my Google spreadsheets and changed the target_ssKey variable's cell reference and created properly-sized Named…
user3014111
  • 529
  • 1
  • 4
  • 7
38
votes
6 answers

Extract digits from string - Google spreadsheet

In Google spreadsheets, I need a formula to extract all digits (0 to 9) contained into an arbitrary string, that might contain any possible character and put them into a single cell. Examples (Input -> Output) d32Ελληνικάfe9j.r/3-fF66 ->…
thanos.a
  • 2,246
  • 3
  • 33
  • 29
38
votes
10 answers

How do you create a "reverse pivot" in Google Sheets?

I am trying to produce a "reverse pivot" function. I have searched long and hard for such a function, but cannot find one that is already out there. I have a summary table with anywhere up to 20 columns and hundreds of rows, however I would like to…
Marc Henson
  • 407
  • 1
  • 5
  • 7
37
votes
2 answers

Automatic colouring of a specific value wherever found in a table

I am looking for a way to automatically change the color of the minimum cell in a row in Google Sheets. So, for a table like: 1 | 2 | 3 4 | 2 | 1 2 | 1 | 6 it would color all the cells with 1 in them.
Verhogen
  • 27,221
  • 34
  • 90
  • 109
37
votes
2 answers

Google Sheets Pattern Matching/RegEx for COUNTIF

The documentation for pattern matching for Google Sheets has not been helpful. I've been reading and searching for a while now and can't find this particular issue. Maybe I'm having a hard time finding the correct terms to search for but here is the…
Martin
  • 447
  • 1
  • 5
  • 12
37
votes
8 answers

How to parse an HTML string in Google Apps Script without using XmlService?

I want to create a scraper using Google Spreadsheets with Google Apps Script. I know it is possible and I have seen some tutorials and threads about it. The main idea is to use: var html =…
user3347814
  • 1,138
  • 9
  • 28
  • 50
37
votes
3 answers

Google Sheets: how to create dropdown list using data from another sheet

I need to populate a drop down list in a cell (let's say cell B2) of Spreadsheet A (using data validation) on basis of data located in Spreadsheet B (range - C3:C15). How do I do that?
keshet
  • 1,716
  • 5
  • 27
  • 54
37
votes
5 answers

How to get the current time in Google spreadsheet using script editor?

There is an option to write a script and bind it to a trigger. The question is, how to get the current time in the script body? function myFunction() { var currentTime = // <
J.Olufsen
  • 13,415
  • 44
  • 120
  • 185
36
votes
4 answers

HTTP Google Sheets API v4 how to access without OAuth 2.0?

my idea is to create a google sheet, make it public and then access it from my work computer linux/bash to read/write values on a daily basis. i have a public google doc sheet that anyone can find/edit. this is the sheet ID:…
user5471256
36
votes
8 answers

Generate all possible combinations for Columns(cross join or Cartesian product)

I have a Google SpreadSheets doc with three columns A, B and C. I need to populate the Column C with all the possible combinations of the values in Columns A and B. Please take a look a the capture to see what I mean. I found this to be done in…
JPashs
  • 13,044
  • 10
  • 42
  • 65
36
votes
5 answers

External API call in Google Spreedsheet is possible?

I have created a Google Spreadsheet with five columns; Once the user filled the values in the first three columns, It has to call a 3rd party API and fill the value(response) in the forth and fifth columns. Is it possible to write a code in Google…
It's me
  • 1,065
  • 6
  • 15
  • 30
35
votes
5 answers

Format entire row if a particular cell in the row is not empty

I want to format the whole row depending on if a particular cell in that is empty or not. I want conditional formatting to go through all rows and say add a background color if column C in that row is not empty. How do I fix this?
user3378720
  • 359
  • 1
  • 3
  • 3
34
votes
2 answers

How do you insert a single row into Google sheets using Sheets APIv4 Java

If I want to insert a row into Google sheets using Java at a specified index, then write to that row, how would I go about that?
A_Elric
  • 3,508
  • 13
  • 52
  • 85
34
votes
6 answers

How to enclose every cell with double quotes in Google docs spreadsheet

I have utf-8 data which I would like to save as CSV. My old version of Excel mangles utf-8, so I have to resort to using google's spreadsheet which handles utf-8 beautifully. Some of my data have commas in them, so I must wrap every field of data in…
sal i
  • 341
  • 1
  • 3
  • 4
1 2 3
…
99 100