Questions tagged [google-sheets]

For questions about the Google's web application to create and edit spreadsheets on a web browser. Don't use it for the Google Drive Excel files editor. Please consider adding a demo spreadsheet to your question, this will greatly increase the chances to get a satisfactory answer.

Filter by
Sorted by
Tagged with
4
votes
1answer
526 views

Get a list of items missing from array

I have a spreadsheet where I'm using the trailing set of columns as a kind of foreign key; I'm trying to throw an error if someone enters a value that doesn't exist. I'm thinking one way to do this ...
5
votes
6answers
8k views

Split, reorder, concat inline

I want to split a cell, reorder the pieces and recombine. For example: | name | reordered name | | Page,Larry | Larry Page | | Brin,Sergey | Sergey Brin | I know that =SPLIT(A2, ",") ...
1
vote
1answer
149 views

Publish last 5 entries from a Google Form

We currently create and html table to list our last 5 Webinars. This is done manually. We are adding external contractors into the mix and would like to give them the ability to enter basic webinar ...
3
votes
1answer
11k views

Panel with Vertical Scroll Bar that allows navigating to different worksheets

I am attempting to build a Script that would open a window with each worksheet I have in my spreadsheet, allowing me to click on the worksheet name to navigate to that sheet. I found the following ...
4
votes
1answer
6k views

How can I speed up my Google Spreadsheet Script?

I am somewhat new to JavaScript and very new to using it in Google Spreadsheets. I have read that fewer calls to Google Spreadsheet API speeds things up, but I feel like I have as few as possible at ...
3
votes
1answer
669 views

How can I use “fill series” without appearance of unwanted commas?

before: V 2013 I am using fill series to fill cells below. expected: V 2013 V 2013 V 2013 result: V 2013 V 2,013 V 2,013 I found only http://productforums.google.com/forum/#!topic/docs/...
3
votes
1answer
108 views

A simple spreadsheet sorting

In a Google Spreadsheet I have different groups of cells in one column to auto-sort. All cells in this column have same pattern of data, all datum have it's own cell and it's needed to be sorted into ...
4
votes
1answer
503 views

Notes In Published Google Spreadsheets

I've developed a website for our active adult association. I upload an Excel spreadsheet to Google Drive. It has notes attached to cells. The notes display fine in the converted version in Drive. ...
0
votes
1answer
88 views

How do I reference a second sheet column across a row of numbers in sheet 1

I'm trying to look-up a value in sheet 2 from a number on a row in sheet one: it looks like this: Sheet 1: Row4 Number of units 0 5 25 10 15 And sheet 2: column a column b 1 2 3 ...
1
vote
0answers
84 views

Automating Google spreadsheet to transfer new info

I'm looking to setup a production line with Google Spreadsheets. Here's how I intend it to work: Our Sales Rep John has a spreadsheet specific to him only. He takes in sales leads, and inputs the ...
4
votes
2answers
4k views

Protect Google Spreadsheet formatting while allowing changes to data

I would like to protect the formatting of a cell (or a range of cells) in a Google Spreadsheet, but still allow changing the data in that (or those) cell(s). For example, I want to add a validation ...
7
votes
3answers
12k views

Colorize a cell in Google Sheets based on cell data

I'd like to colorize a cell based on its contents. 0 = red, 100 = green, and linearly interpolated between. function LinInt(x){ var ss = SpreadsheetApp.getActiveSheet(); var cell = ss....
1
vote
2answers
11k views

How to GROUP and ORDER data in a Google Spreadsheets?

I want to group and sort a particular data set. I've prepared the data using a QUERY function and using other functions. Can I order by sum? This is an example file I created: order by sum
1
vote
1answer
2k views

Data Ranges in Custom Google Spreadsheets Function

I'm trying to create a custom Function in Google Spreadsheets that takes a range as input parameter. function Example(cells){ var values = SpreadsheetApp.getActiveSpreadsheet().getRange(cells)....
2
votes
2answers
1k views

Date Formatting in Google Spreadsheet

I use a spreadsheet in my medical office for referrals. It includes places to fill in these three areas: date faxed, date of the appointment, date the appointment office notes were received. The ...
0
votes
1answer
5k views

Auto date added to adjacent cells when data is entered

I am trying to create a spreadsheet to track student loan outs that will automatically put the date in an adjacent cell. The spreadsheet would be set up like this: Column 1 would be "name" Column 2 ...
7
votes
3answers
22k views

How to extrapolate data in Google Sheets?

Given a set of values such as: 10, 12, 14, 16, 18 I would like to "estimate" that the next values will be: 20, 22, 24 and so on. I realize there are several different ways to extrapolate, and they ...
1
vote
1answer
1k views

Duplicate check while creating Google Contact with Google Apps Script

I have the following code which creates Google Contacts from a Spreadsheet. The problem is whenever I run the code it creates contacts but has no functionality to check if the same Google Contact ...
2
votes
2answers
456 views

How to get a value from column 'A' in every 30th row in Google Sheets?

I have a sheet from which I want to get values from column A of every 30th row. I tried =A(2+30) but it just gives me an error.
4
votes
2answers
4k views

Google Spreadsheets function to copy array

Is there an array function to copy an array? I use the workaround =TRANSPOSE(TRANSPOSE(A1:A30)) currently. An example of when this is useful: you have a worksheet of raw data, and another worksheet ...
2
votes
1answer
123 views

Why is my imported time 668 seconds ahead of the value in the original cell? Is this reproducible? [closed]

I wonder if anyone can explain or reproduce the following test case. It demonstrates that GMT-06:00 Central Time - Tegucigalpa is 668 seconds ahead of GMT-06:00 Central Time, which seems like an ...
7
votes
1answer
4k views

Automatically create a new row each day, on top of older rows

I am looking for a way to automatically create a new row on top of older rows each day. Inside of this row should be the current date.
4
votes
1answer
801 views

Pull Twitter Posts into Google Spreadsheets

I would like to set-up a Google Spreadsheet that automatically pulls Twitter search results into the cells. For instance, I'd like to search for "pizza" near "baltimore" and then have any pizza ...
1
vote
1answer
1k views

Changing an onEdit function to run onOpen

I have the function below (found online and adapted and it works) that is an on-edit function, but I want it to run/trigger when an import is done OR on open, as the data will not really be edited it ...
4
votes
1answer
194 views

Sharing a Google Spreadsheet but making some information private

I book reservations for a sports team and need to be able to communicate with other parts of the arena what hospitality areas are booked/available. In the past there have been multiple e-mails, ...
0
votes
4answers
552 views

How to convert datetime interval to hours?

I have this spreadsheet: A B C D E 1 08/13/2013 02:10 4 2 08/13/2013 02:19 10 00:09:00 160 In D2, I have this formula : =if(...
17
votes
2answers
24k views

In Google Spreadsheet, finding what formulas reference a given value

I would like to find out which cells have formula dependencies in a large spreadsheet. I am looking for a way to do something like OpenOffice Tools>Detective>Trace dependents and Edit>Find &...
1
vote
2answers
1k views

ImportHTML #VALUE! error for specific URL

I'm trying to get the data from http://wodindex.wikispaces.com/ into my spreadsheets. Also from a different subdomain of wikispaces.com the grabbing doesn't work. Trying to get data from this site ...
2
votes
1answer
570 views

Is it possible to use the importHTML function with a concatenated URL?

What I'm trying to do is use a URL previously made with the CONCATENATE function in an IMPORTHTML function. When I try: importHTML (H1, "table", 0) Where H1 is the cell with the concatenated URL, I ...
1
vote
2answers
328 views

How can I make a Google spreadsheet Function reference one column's value and adjust it to a different Value?

I am trying to create a spreadsheet that will calculate varying sizes. I need it to reference a key where a letter is an established size such as H=1984, S=672, etc. so as that that column can be ...
3
votes
1answer
252 views

Data Validation Criteria

I am currently trying to change cell validations cell range in Google Spreadsheet base upon a cell's input. For instance, if A1 = 123 then have B1 (which is the data verification cell) pull from C1:...
1
vote
0answers
140 views

Automatic data sharing between Google Docs files [duplicate]

Is it possible to automatically update information on a Google Presentation or Document based on information in a Google spreadsheet? For example, I could create a presentation that contains a graph ...
3
votes
0answers
182 views

Comments disappeared from Google Spreadsheet cell [duplicate]

I have been working on a shared Google Spreadsheet for months. All of a sudden, the comments have disappeared from the cells and now only appear in the discussion thread. How can I get them back to ...
5
votes
2answers
3k views

How to avoid “invoked too many times per second” error

I made a Google Apps Script, but often I get this error: Script invoked too many times per second in this Google user account. This is the script: function papersControl(tipo, col, prev_cell, quant) ...
4
votes
2answers
3k views

Is it possible to select a whole column for chart data in Google Spreadsheets?

I've got a Google Spreadsheet that's written to by a sensor system approximately every thirty seconds. Each insertion adds a row to the bottom of the spreadsheet. I've got a graph of this data. I'...
2
votes
1answer
2k views

Copy subset of rows from one sheet to another, based on value in column

I have a sheet with accounts, consisting of 3 columns: Accountnr Name Category This sheet is not nescessarily sorted on any column.The Category is one of three possible values. I want to fill a ...
4
votes
2answers
17k views

Remove empty rows in Google Spreadsheets

Is there a way to in Google Spreadsheets to remove / delete empty rows? Ideally I'd like to be able to select the columns to check.. ie. Select columns D, E and remove all the empty rows from them or ...
2
votes
1answer
189 views

Custom actions in Google Spreadsheets

I find that I often change specific cells from black on white to white on black when I'm making spreadsheets. It becomes tedious to change the background color, then the foreground color over and over ...
1
vote
3answers
2k views

REGEXEXTRACT and French accents in Google Sheets

I have the following formula: =REGEXEXTRACT(source!A2,"^[\w\s\À-ÿ]+") where source!A2="Ancien Marché Saint-Jacques (1125, rue Ontario Est)" and I would like to extract Ancien Marché Saint-...
2
votes
1answer
2k views

How to automatically open a created text document after it being created

I've used this script to create a Google Document (like word document) with Google Spreadsheet. But, when I do so, the new text document created from a template is placed in the default chosen folder. ...
26
votes
4answers
90k views

How to automatically insert a new row and retain functions/formulas from last row?

I have a table with cells that have functions/formulas, like this one: I need a script that creates a new row, copying with it the functions/formulas of the last used row. I find this script which ...
2
votes
1answer
1k views

Create new Google Spreadsheets with timestamp

I'm trying to create a new spreadsheet once we select the specific function from the custom menu item that's selected. I want the new spreadsheet to be "Sheet" and "Timestamp". I have this created ...
3
votes
1answer
9k views

How to make a simple two column data chart in Google Spreadsheets?

I have a column with dates and a column with an amount of money. I just want to make the simplest chart possible, a line chart that shows money as a function of time, so I can see in what dates I tend ...
-1
votes
1answer
909 views

Mileage Calculator Google Spreadsheet

I am trying to calculate miles driven for two different companies. I want to make a cell that will add up only data from Company A, and a cell that will add up data from only Company B. EX: COMPANY ...
2
votes
1answer
2k views

How can I get a cell to change its value based on another cell's color?

I have three colors in column F: green, grey and red. What I want is for column G to display a number (1, 2, 3) depending on the color value of F. Is there a way to do this?
1
vote
1answer
658 views

How do I reference only the elements of a multi-column range that meet particular conditions?

I have a spreadsheet made up of several individual sheets: one sheet for each category of product and one sheet designed to be a "global view." In each sheet is a 3-column range of variable length ...
27
votes
5answers
90k views

How to insert the current date into a Google Spreadsheet?

I've got a spreadsheet in Google Drive and one column is meant for the date the entry is made. Is there a button to do this? I'm aware of the functions =NOW() and =TODAY() but their description says "...
2
votes
1answer
537 views

Relative paths to other sheets inside a Google Spreadsheet

I'm trying to put our food recipe collection into a Google Spreadsheet. On the first page we have an index of all the foods. Each recipe is located on a separate sheet. We would like to be able to ...
6
votes
2answers
11k views

Multiple substitutions in a single text

Is it possible to write such a formula that it will substitute in a text value all occurrences of few values with their replacements? For example I would like to substitute all occurrences of aaa ...
2
votes
1answer
2k views

Chart with points in Google Spreadsheets

I have a Google Spreadsheet with a line chart. Despite the name "line chart", the data does not appear regularly spaced on the x-axis but according to a date column. (This shows up correctly, no need ...

1 104 105 106 107 108 117