Tagged Questions

One of productivity apps in Google Drive, that allows you to create and edit worksheets

learn more… | top users | synonyms (1)

1
vote
1answer
271 views

How do I sum all even or odd columns in Google Spreadsheets?

The following is given as a solution for Excel: =SUM(IF(MOD(COLUMN(B16:AO16),2)=0,B16:AO16,0)) =0 changes to =1 to toggle between odd and even columns. However, this doesn't work in Google ...
0
votes
1answer
69 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, ...
0
votes
3answers
63 views

How to calculate average screen reso. based on number of visitors using a spreadsheet?

I want to find out what the average screen resolution is for visitors to my website – or rather, what is the average width and the average height in pixels. To qualify what I mean by "average" – and ...
0
votes
1answer
60 views

Conditional counts based on dates

I am creating a master tracker for all the technology in my school and it is pulling data from many sheets and placing it in to blocks and I am having trouble figuring out the following ...
0
votes
1answer
41 views

How to match info between tabs in a Google spreadsheet

How can I take info from one tab and add it to another? For example, I have Tab 1 with column A—call it “cat breeds”—and I have Tab 2 with column A—call it “cat breeds”—as well. They need to match so ...
2
votes
1answer
251 views

How can I reference a cell in a custom function while setting up data validation in Google spreadsheets?

I made the following Google script to check whether a string is less than some length: function isLengthLessThan(length, text) { return (text.length < length) }; How can I set up data ...
3
votes
2answers
169 views

Single drop down with data from separate columns

I have a spreadsheet that I need to add new data to but the previous user made it so that I can't add data in a new row without moving down a lot of data which would mess with all the calculations. ...
3
votes
2answers
160 views

Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries)

I'm compiling a database of business address info, including key staff for each business. The Google spreadsheet I've inherited for this project includes a column for 'key staff' (in column B), with ...
2
votes
1answer
61 views

Is there a way to neatly format length in Google Spreadsheets across multiple orders of magnitude?

I have a spreadsheet with a column for length with values ranging from millions of kilometers to fractions of micrometers. Viewing it all in meters as unformatted numbers directly is awkward since you ...
0
votes
2answers
39 views

Which formula sums all values from a column in a line that contains a specific word?

Let's use this random spreadsheet to illustrate my problem. What I want to do is a sum of all Page Views (F) that contains the Category (B) named /Online Computers. Which formula can I use to do ...
1
vote
0answers
84 views

Yet Another Mail Merge - sending to multiple recipients

I'm required to send a parametrized mail message to multiple recipients, based on values in a Google spreadsheet. I've followed the instructions in this guide on how to use Yet Another Mail Merge. ...
0
votes
0answers
202 views

Google spreadsheet disappearing text

Whenever I enter text into a box and press enter the text disappears. I can only view the text by clicking on the box it's in. What am i doing wrong? Windows 8, Firefox 29. It only occurs in firefox. ...
1
vote
2answers
77 views

Date autofill increments year instead of day

Earlier, when I had dates in a column like: Monday, 21 April, 2014 Tuesday, 22 April, 2014 and I extended the last cell to lower cells I could expect them to be filled with following days: ...
1
vote
1answer
40 views

Add a sheet from a template

Is there a way to add a new sheet to a spreadsheet from a template? If I'm using a template for time, I'd like to add a new sheet from the same template every two weeks.
2
votes
1answer
52 views

Appending text to each column in a Google Drive Spreadsheet

I have a Google Drive spreadsheet that has about 200 columns. Each one is a name, and to each one I want to append -banner.jpg. Is there a way to batch process this? For example: abcde fghij ...
2
votes
1answer
572 views

How to share only one tab in the new Google Spreadsheets?

I have a Google spreadsheet with two tabs. First one is a public one where I want to invite specific customers to edit it. Second one is just for my team and I would like it to be invisible to the ...
0
votes
1answer
67 views

Transfer data from a form to a new spreadsheet

I have a simple form that inputs entries into a spreadsheet in Google Doc's. All I want to do is open a new sheet and import the data from the forms spreadsheet so I can manipulate it in my own way. ...
0
votes
0answers
66 views

Getting error while using a Google Forms/Spreadsheets Auto-Increment Script

Similar to Kelly's problem here: Can I add an autoincrement field to a Google Spreadsheet based on a Google Form? I need to create a unique ID upon submission of a form which will be used as a case ...
2
votes
0answers
53 views

Can't unmerge cells

I have a lot of data in a Google spreadsheet that depends on charts I spent ages trying to make look pretty. There is a stupid block of merged cells that won't unmerge, and when I try to ...
-1
votes
1answer
21 views

How can I get a column with the sum of the proceeding rows from another column?

I've got a series of withdrawals and deposits for an account, each in a row. I'd like another column that for each row, displays the total amount in the account after that row's withdrawal or deposit. ...
1
vote
1answer
145 views

Error with exact date 21/02/1971 - function MINUS expects number values

I am getting this really bizarre error. In cell A1 I have 21/02/1971 (Brazilian-style date), in A2 I have =today()-A1. I receive the following error: #VALUE! Error: Function MINUS parameter 2 ...
2
votes
1answer
40 views

Google Spreadsheets SUMIFS with Text Value of Cells

Is there a way to use the text value of cells with a conditional statement such as sumifs so that on one sheet I can have all the expenses # A B C 1 R. Tusk Travel ...
0
votes
0answers
22 views

Create email with google script with google fonts

I've created various scripts to automatically send email to users who submit a form. no I'd like to touch them up a bit by using a specific Google Font (Philosopher). Here is the script I tried ...
1
vote
2answers
227 views

How can I easily fill a column in Google Spreadsheet with random values from a list?

I need to create some data for a simulation. So I wanted to take a spreadsheet, and for each column, fill it with random data falling within a domain. A uniform distribution is good enough. For a ...
0
votes
1answer
47 views

Link a range of vertically ordered rows, to a horizontal range of columns [duplicate]

Is there a range function in Google Spreadsheets where I can link the values of SheetA!A1:A10 to SheetB!A1:J1? So basically rotating the values from rows to columns.
0
votes
2answers
77 views

Google spreadsheets - How to calculate a value based on the row/col number of another cell value

I am trying to build a spreadsheet that one sheet holds a list of names divided into groups. The second sheet, should list all the names, sorted, with a number next to each name according to a ...
0
votes
1answer
96 views

Publish single google spreadsheet to the web

I have many sheets in one spreadsheet, how do I publish single spreadsheet to the web? When I attempt to publish, it doesn't show any option to pick the sheets I want to publish.
1
vote
1answer
615 views

Send an email with attachment using Google Apps Script

I have a Google Spreadsheet with a list of emails. I want to build a routine that send email automatically to those email addresses. I also want to attach a PDF to this email. The PDF file is located ...
0
votes
0answers
46 views

Google Spreadsheet not refreshing correctly

I have a single sheet (see the sheet Data: AEX) that loads data using the importdata function (4 columns). All following columns contain formulas based on the imported data, some formulas share a ...
2
votes
1answer
80 views

What is the fastest way to use Google Spreadsheets by several users - from the same account or from different accounts?

Terminology - I use spreadsheet for a document and sheet for a sheet within the document. TL; DR If several users must work on data, stemming from the same source, which is fastest: one Google ...
0
votes
0answers
25 views

Dynamic drop down in case of multiple cells for looking specific list of subsequent cells and choosing

How to use a dropdown in case of multiple cells in case of large data which I have posted at > https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/O5oKD2Afkp0 And Spreadsheet > ...
2
votes
2answers
59 views

Formula required for date and counting fabrics

How do create a formula that counts the days I am waiting for fabric to arrive, however when it does arrive I need it to stop counting the days. =TODAY()-TO_DATE(A41) This works great, however it ...
2
votes
1answer
103 views

Preventing concurrent editing of a cell in Google Spreadsheet?

We often have the case of two people overwriting each other's changes by writing on the same cell at the same time. Is there a way to make it so that cells can't be written by more than one person at ...
4
votes
2answers
1k views

How can I get Google Sheets to auto-update a reference to another sheet?

I am using an external application to enter data into a Google Spreadsheet. This workbook is then referenced by a separate workbook (with the IMPORTRANGE(spreadsheet_key, range_string) function) that ...
1
vote
2answers
92 views

How can I merge into one cell, all the non-zero values in a row each concatenated with the column header?

I hope this example is clear. I have played with join and filter but can't get the Summary field to display as below: Row 1 is a list of subjects. Row 2 is a teacher's subject allocation ...
0
votes
0answers
37 views

Delete a q​uestion in a Google Form

I want to delete a row in the Google Form, and it disappears well enough in the spreadsheet, but it is still in the presentation. I also want to modify the contents of the investigation, but it does ...
2
votes
1answer
38 views

Google Sheets Passing Range from separate sheet into custom function

I have the following custom function I've built to basically reproduce a needle haystack type of functionality function ListBy (Range, Range2, needle) { var holder = []; if(typeof Range != ...
0
votes
2answers
38 views

Add two variables to a Hyperlink

I know this works: = hyperlink( "https://www.google.com/search?q=" & B2 ) But I can't get two variables to work so the link would look like: https://www.google.com?var1=[insert cell ...
3
votes
1answer
164 views

Query syntax using cell reference

I'm having trouble figuring out a fairly simple QUERY statement in Google Spreadsheets. I'm trying to use a cell reference instead of static values and I'm running into trouble. I keep getting Error: ...
1
vote
2answers
93 views

Counting number of cells in a range that contain certain text characters

My question simply: Is it possible to generate the data in A3:A5 using a formula? A B C D E F 1 PASSED/TOTAL | EXAMS ATTEMPTED 2 ...
1
vote
1answer
106 views

How to send cell as argument for function?

I am trying to write a custom function for Google spreadsheets that checks the formatting of a given cell and acts accordingly. I know that I can give a string as argument and get it to work but then ...
2
votes
1answer
233 views

Prevent “invoked too many times per second” error in Google Apps Script

I have a Google spreadsheet consisting of different months on each tab. Every month, I add a new tab and another 50-75 rows. There are many tabs. There is a cell on each row which calls a custom ...
1
vote
1answer
85 views

How to select a particular cell from an 'array formula' result?

Take this formula: =GOOGLEFINANCE("PG", "high", DATE(2014, 4, 1)) This formula returns an 'array result', meaning it generates a little table over multiple cells, like this: The top-left cell ...
2
votes
2answers
46 views

How to summarize many-to-one table in Google Sheets

I've got two separate sheets we'll call 'Sheet A' and 'Sheet B' with a table on each. The table on 'Sheet A' has a Many-to-one relationship to the data on the second sheet. On the second sheet, I'd ...
0
votes
0answers
62 views

Pull data from one sheet (has a locked cell) to another sheet (has a locked cell) every day automatically?

I have a dynamic Google spreadsheet to which I add one more sheet every day. I want to take the data from sheet1 cell D96 (a protect cell) to sheet2 cell D3 (a protect cell) every day. I found a ...
0
votes
1answer
50 views

Cursor position in Google Spreadsheet

In a google Spreadsheet the cursor is always behind the last letter not in front. How can I change that?
2
votes
1answer
179 views

How can I change Google Spreadsheets to have iso 8601 dates as default?

I would love to set my default date-setting to be iso 8601 (relevant xkcd) for all of my Google Spreadsheets. I'm well aware that I can manually change the date-format for a given spreadsheet by: ...
1
vote
1answer
33 views

How do I refer to a range in another sheet?

Sheet 1 has numeric values in the B column. I'd like to sum them into a single value within Sheet 2, preferably without having to copy them individually first. According to the docs, I can copy a ...
2
votes
1answer
53 views

Formula value change after a script pastes into another range

I have a script that pastes a formula from one cell into a range in a column. When pasted the range just disappears. I mean the whole formula is OK except the pointer. Any ideas how to deal with ...
0
votes
0answers
64 views

Sort all sheets by column 1 in first sheet

I’m trying to sort or autosort an extensive Google spreadsheet with various codepending sheets. I have tried to use the sort function but that is to blunt to use in this case. I’m hoping to get some ...