A productivity application from Google that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

0
votes
2answers
126 views

Retrieve last entry via ImportXML

Looking for help pulling specific cells from a web site into a Google Spreadsheet. I want to pull "last" price from this page but only a few specific ones. Such as Dow, Nasdaq 100, S&P 500, ...
2
votes
1answer
2 views

Can I protect rows in a Google Docs spreadsheet from sorting?

I can sort a Google Docs spreadsheet on any column, but that sorts ALL of the rows. I don't want to sort the rows that contain the column labels. Is there a way to protect them from being sorted? I ...
0
votes
0answers
1 view

Google sheets: Automatically including new rows on separate sheet

I have a google sheet document with two separate sheets, Sheet1 and Sheet2. I want column A on both sheets to stay the same. Column A of S2 simply has ='Sheet1'!A2 for cells A2 on down. Let's call ...
1
vote
1answer
22 views

Script to delete a dynamic range of rows

I'm looking for a script that allows me to delete a dynamic range of rows. Basically I have a script that adds rows to the bottom of a list but I want another script that will delete all rows in the ...
0
votes
1answer
40 views

Copy a row and add to the bottom of a list

I'm looking for script that will allow me to copy a specific row and paste it to the bottom of a list. The bottom of the list will not be the last row on the sheet. Using VBA I would have used an ...
1
vote
2answers
22 views

How to Determine the Xpath Query to a Datapoint in a Table for Use with IMPORTXML?

I'm looking to retrieve the most recent financial figure for a single data point from a table using IMPORTXML. The target page displays three sets of financial figures for a company on quarterly and ...
3
votes
1answer
101 views

Problem: Script does not trigger on FormSubmit: Removing Duplicates in Google Sheet of Form Responses based on a column

I am trying to remove older duplicate form responses based on a column using the following code. The credit for the code goes to: ...
2
votes
1answer
33 views

Selecting a single column from all rows for chart range

I want to specify a certain column from all rows (omitting the first few) to be the range for my chart. Currently I input this to specify the range: L6:L (note: this automatically changes to L6:L20 ...
2
votes
2answers
27 views

Organize employee time log into a table with a row for each employee

I want to get all the Timestamp of an employee (EmpID) from one sheet to another using Google apps script or otherwise. Explanation In Google Sheets, Sheet1 has the following items in 3 columns | ...
0
votes
0answers
6 views

Conditional format — copy format of another cell (Google Sheets)

I'm using Google Sheets spreadsheet, hope it's ok to ask about it here (in superuser). Chances are the methods to accomplish this might be different from Excel or LibreOffice, if it can be done at ...
2
votes
1answer
69 views

Change logs for Google Spreadsheets

We've got a large shared spreadsheet which is a chart showing user permissions. It's writable by several people. Although Google Drive allows you to roll back the changes simply its interface isn't ...
1
vote
2answers
25 views

JavaScript To Dynamically & Daily Select The Next Worksheet In An Open Google Sheets File

At work using Chrome, I have a Google Sheets file saved on my Google Drive folder that's shared with me with edit privileges that I keep open on my MacBook Pro. After I've originally opened the file ...
1
vote
1answer
9 views

How to use user-defined functions for custom formatting?

I’m writing a simple Google spreadsheet to list every account number which could come in handy to our non-profit in the future. Of course, one can expect some typo will get in some point in the ...
1
vote
1answer
37 views

How can I automatically set “sheets last updated” in a cell on Google Docs Spreadsheets?

Simple task!.. output a date and time every time something in the sheet is edited! function onEdit() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets("Data"); var ...
1
vote
1answer
12 views

If a string is contained in a row return return the value of the first cell of the row

I've this Google Spreadsheet: https://docs.google.com/spreadsheets/d/1-QV49oKCf58HgLBgC8gJ3gc6IXJZAo2xvh6vPTjY05o/edit?usp=sharing check the row in Sheet1 where the domain in Sheet2 is contained ...
2
votes
1answer
12 views

Autoincrement a date in a formula when dragging horizontally

I have a date in column A, and a formula in column B that references the date. I'm trying to drag the formula over to 6 more cells horizontally, representing the 7 days of the week, Column A being ...
0
votes
0answers
10 views

Google Forms - Show unique ID on the form (before submitting)

I need an anonymous form (no names, emails etc) that shows unique ID so that if a person decided to reveal that ID to me I will be able to find his/her answers in the spreadsheet. The ID can show on ...
1
vote
1answer
14 views

How do I copy data if cell contains text?

Basically, I have a book which gets data anytime someone fills a form. There are five groups I need the data divided into. If a cell contains one of the five answers all the answers by user need ...
3
votes
2answers
28 views

Conditional format for column headers where all cells are numbers

I'm trying to color the header if all of the cells in that column are numbers. Blank or non-numbers should break this. I have: custom formula: =ISNUMBER(A3:A) But it styles the cells instead. Not ...
4
votes
1answer
5k views

How do I combine a cell value with a string in a Google Spreadsheets formula?

I have the following IF condition in some cell: =IF(A1>A2, "value is <C5>", "value is <D5>") where <C5> and <D5> should be the values of cells C5 and D5. I know it is a ...
3
votes
1answer
85 views

Using Google Spreasheet's IMPORTXML Function to Download Selected Multiple Columns

I use Excel's XML mapping to download RSS feeds and select elements to display in column. I'm currently using Google Spreadsheet's IMPORTXML functions and trying to recreate my below Excel results. ...
2
votes
1answer
103 views

Extracting GICS codes from Fidelity website

This is the website: https://eresearch.fidelity.com/eresearch/goto/evaluate/snapshot.jhtml?symbols=aapl&type=o-NavBar I'm trying to pull out the following two pieces of data into a Google Sheet: ...
3
votes
1answer
333 views

In Google Spreadsheets the xPath method with Google Maps don't work anymore?

I've used this query to calculate distance between two locations, now it won't work anymore, and returns the error xPath query didn't get any result (translated from Italian, I don't know if is the ...
-1
votes
1answer
3k views

Google Docs ImportXML can't scrape anymore?

ImportXML lets you get information from filetypes such as xml, html, csv, tsv, as well as RSS and Atom feeds. This example in Google Docs Help for importXml function - =importXml("toysrus.com";; ...
0
votes
1answer
78 views

Unable to import pricing info from toysrus.com into a Google Spreadsheet

I'm trying to pull live pricing from a given toysrus.com item into a Google Spreadsheet (one item/price per row), and am having no luck whatsoever. Importxml(url, xpath) does not work (similar to the ...
1
vote
2answers
345 views

IMPORTXML: xpath to get non-distinct parent node data for each child node

Google Spreadsheets has an IMPORTXML function, which I am trying to use to maintain a list of TV episodes, based on the API at http://services.tvrage.com/feeds/episode_list.php?sid=3183. I am trying ...
2
votes
1answer
179 views

ImportXML with XPath does not return an entry of a table

Using Google Sheets with IMPORTXML to scrape a piece of information from a website via XPath. Here's the site (click here). The specific piece of information I'm trying to extract is the Price/Sales ...
6
votes
1answer
55 views

How to open a Google Sheets file and go immediately to the end of the file

I have a Google Spreadsheet with 100 lines. Every time I open that file, I want to insert a row on the end of my table. How can I jump to the last line of the table when I open the doc? Because ...
18
votes
8answers
27k views

How to prevent cut and paste data changing references in formulas?

Google Docs has a feature where if you have a reference to a data cell in a formula, and you cut the data from that cell and paste it in a new location, the reference in the formula is updated to ...
1
vote
1answer
2k views

Insert column across multiple sheets

I have a Google spreadsheet with multiple sheets. All the sheets have the same header row, but different data (it's a month by month look at some stats). Is there a way to insert a column on one ...
1
vote
0answers
9 views

Different bar types based on column value

Instead of having one column for each new "type" below, is there any way to achieve the same result using the non highlighted cells in the image below? To elaborate, I would like different bar types ...
0
votes
0answers
12 views

Add days of the month to a chart [duplicate]

I have 2 columns, a boolean value (nok/ok) and a date. I am tracking in which particular date I have completed a study module(s). When I add a chart to show the progress through time, the chart shows ...
1
vote
0answers
13 views

Pasting multiple formulas at once

I have a column of formulas (each created by concatenating several parts of a formula in cells to the left of it on each row), I want to be able to copy and paste these into a new sheet, whilst ...
0
votes
0answers
11 views

How to format cell if date is 7 days past the date listed in cell [duplicate]

I'm trying to keep track of regular due dates based on the last time the task was completed. Example: I have a task that needs to be completed about once every month. In column B I'm inputting the ...
0
votes
0answers
15 views

One form submit produces two updates to spreadsheet

I have an application in which a user submits a form, the data are logged in a spreadsheet, the form submit triggers a script that records the new data and deletes any previous form responses from ...
0
votes
2answers
30 views

How to sum cells in Google sheets that contain numbers and text

This has already been addressed in Excel but I was wondering how to do the same in Google Sheets. So suppose the B cell has this formula: ="frontend (days) total: "&sum(B2,B10,B14,B20) and the ...
3
votes
2answers
67 views

Coloring cells according to the value to which its content is closest

I am trying to make a guide for my dragons on a game I play but I'm not quite sure how to code what I am trying to do. Link ex. if has values "Guardian"(C2:C100) and "Dragoness"(I2:I100) in its row ...
1
vote
1answer
17 views

Combining columns horizontally with an arrayformula

I need to join columns with a formula (not a script) in Google Sheets in a different way than others I've found--here is my original data, supposing that the Avengers train by playing racquetball: ...
3
votes
2answers
38 views

How can I sort in Google Sheets by cells containing section (or version) numbers?

I'm indexing a document with sections and sub-sections. I want to have one column representing the section-number and be able to sort by this column. However when the sub-section gets higher than 10, ...
1
vote
1answer
23 views

Hide row if cell contains specific text

I am trying to hide a row when a cell is filled with "x". I have tried using scripts but they don't work. So there are "A", "B" and "C" filled in column F. I want the script to hide rows where "A" ...
3
votes
1answer
42 views

Highlight/reject cells that contain any of the values from another sheet

I have a sheet/tab that contains nothing but a single column where each row is a word, let's call it wordlist. In another sheet/tab, I would like to make it so that for one of the columns, if the ...
0
votes
1answer
17 views

Hide a row of cell when a specific cell is filled

I am trying to hide a row of cell when a specific cell is filled in on Google Sheets For example: On row 2 cell E2 is filled in with "1", I want to be able to hide the entire second row. Is there ...
0
votes
0answers
8 views

Google Spreadsheets: add hours to date problem

I've encountered some kind of strange problem with Google spreadsheets. I've 2 cells, in one I've date, lets say its 10/10/2010, in 2nd one is hour, 12:00:00. I get those from input cells, but when I ...
2
votes
1answer
63 views

How to show instead of hide repeated values in pivot table?

By standard, repeated values in pivot table are only shown once. I made an example sheet to illustrate: This becomes a problem for people who use filters over pivot tables: Notice how Donald's ...
2
votes
1answer
19 views

Lock two Google Sheet cells to each other

Is there any way to lock two Google Sheet cells to each other? What I need: I have data in cells A1 , B1 ; A2 , B2 , ... A1-B2 / A2-B2 must be adjacent. I need to sometimes copy A1-B1 to different ...
3
votes
1answer
20 views

Using cell value to define sheet reference, e.g.: “=A1!B2”

I have data across different sheets in my Google Spreadsheet, called "data(Joe)", "data(John)", etc. From another spreadsheet I want to review that data in a structured way. I would like to be able to ...
3
votes
1answer
39 views

Unable to parse query string for function Query, when using ImportRange

This is the problem I'm getting: Error - Unable to parse query string for function QUERY parameter 2: NO_COLUMND The formula is of the form =query(importange("...", "Sheet!A:V"), "select D ...
52
votes
4answers
43k views

Embed Google Spreadsheet in Google Document

Is it possible to embed a Google Spreadsheet into a Google Document à la Microsoft Excel and Microsoft Word? How about a chart from a Spreadsheet?
2
votes
1answer
89 views

Combining data from two columns to one (in separate rows - not comma separated) and removing blank lines

I have a Google Sheet which receives information from a google form. I would like to collate some of this data in a separate sheet (within the same document). I would like the new sheet to combine ...
0
votes
1answer
20 views

Sync multiple cells so that if any one of them is changed, the others change as well

I have 4 hard coded cells on 4 different sheets (within a google sheet file) that I'd like to keep synced. If any of those four cells have been changed, I'd like the other three cells to be updated to ...