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

learn more… | top users | synonyms (2)

2
votes
1answer
327 views

How to prevent user from downloading my Google Sheets

I have made a few useful spreadsheets for my company. I want to put them on Google Sheets, so my colleagues can use it online to make inputs and get results. But I do not want them to know the ...
2
votes
1answer
196 views

How to get more than 1 question to populate the same field/column in the Spreadsheet?

I am looking for some way to get the submitted answers to 2 or more questions to populate in the same column in the response spreadsheet, but on the next row. e.g. When gathering data from ...
2
votes
1answer
339 views

Can I subtract a cell value in another cell that already have one in Google Spreadsheets?

Let's say that I have two cells A1 (time needed) and B1 (time spent). I want that when I enter a value in B1 the value of A1 will be reduced automatically. I want that in some way I could enter in ...
2
votes
1answer
70 views

Google Spreadsheet add rows until marker

I have a spreadsheet of the form ItemA Subitem1 5 Subitem2 4 Subitem3 7 <potential marker> ItemB Subitem 4 I want to sum the values next to Subitem to a total next to ItemA, ItemB, ...
2
votes
1answer
106 views

Consolidate List of Similar Data into Single Row in Google Spreadsheets

I have a Google Spreadsheet that lists donations given by constituents with a unique account number. Several constituents have made more than one donation in a given period of time listed separately. ...
2
votes
1answer
665 views

Google spreadsheet: execute function only once to prevent circular dependency

I'm trying to do something like this: a1 = if(a1 = "", b1 + 1, ) ... aN = if(aN = "", b1 + 1, ) b1 = max(a1,1) The idea is that column A has unique ID's, and b1 keeps the highest generated id ...
2
votes
1answer
313 views

Run script when clicked on picture in published Google spreadsheet?

I used the script underneath in Google Spreadsheet and added an image that when clicked upon runs the GOTOSHEETONE function to navigate to sheet: SHEETONE. So far so good. However, when I publish my ...
2
votes
1answer
73 views

How can I use a single column as a normalized data point with multiple choices in Google Sheets?

I find I often want to have a column that is meant to be one or more choices from a fixed set. E.g. a Codebase field in a dev roadmap spreadsheet, which might have choices like {DB, API, JS, CSS, etc.}...
2
votes
1answer
260 views

SUM a column of values that whenever a cell is empty gets it from a default column

I am trying to sum a column of values but whenever a cell is empty then it sums the value from a default column. Example: +---------+---------+---------+---------+ | Default | A | B | ...
2
votes
1answer
122 views

How do I reference a cell from a Google Spreadsheet in a Google Document

I would like to create a Google Spreadsheet and reference a cell(-range) from that spreadsheet in a Google Document. Basically, I want the spreadsheet to be a variable holder where I can input data ...
2
votes
1answer
80 views

Is it possible to define user permissions on a sheet basis in Google spreadsheet?

I have a Google spreadsheet with 10 sheets. Is it possible to define user permissions on a sheet basis in Google spreadsheet? For example, on sheet 1, user X can edit but user Y can only view.
2
votes
1answer
1k views

How to make a protected range appear normal in Google spreadsheets

I have a project in Google spreadsheets shared with several other users. There are sensitive formulas throughout that I have protected to prevent accidental deletion or overwrite by the other users. ...
2
votes
1answer
707 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 1,...
2
votes
1answer
336 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 ...
2
votes
3answers
6k views

Send an email with attachment using Google Apps Script [duplicate]

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 ...
2
votes
1answer
241 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 ...
2
votes
2answers
74 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
72 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 != '...
2
votes
1answer
903 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 ...
2
votes
2answers
259 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 ...
2
votes
1answer
288 views

Leads per Week For Last 10 Weeks Formula With Countif

I have a long list of dates (like below) that indicate Leads and I am trying to get number of leads per week for the last 10 weeks. I know I can get the current week number with MID(TEXT(Today(),"yyww"...
2
votes
1answer
121 views

ARRAYFORMULA and COUNTIF

I have a formula: =IFERROR(COUNTIF(C14:V14,"I")/ COUNTIF(C14:V14,"<>"),) to compute a percentage of I inputs divided by <> (all other inputs) and I need to omit blank cells. The IFERROR ...
2
votes
2answers
223 views

Return name of range as string in Google Spreadsheets

I have a range that is named apple in Google Spreadsheets. I thought a method like getRangeName("apple") would exist. I want to get the names of many ranges and set them as the header for the columns ...
2
votes
1answer
169 views

Google Sheet sum column by referencing column header

In Google Spreadsheet, I would like to find a column using a variable matching the column header and sum the column if the match is found. Example: ------------------- Ref | X | Y | z | -----------...
2
votes
2answers
213 views

Add new row while keeping functions in new Google Spreadsheets

In the new Google Spreadsheets, I'm trying to make a simple spreadsheet which will assign points to my salesmen regarding some actions they complete. I would like to be able to add a new row below ...
2
votes
2answers
276 views

Automating Calls to New Google Sheets Add-ons

I'm building a Google Spreadsheet that uses an official add-on to pull data from Google Analytics using the new add-on store. What I'd like to have it do is automatically refresh the data every day at ...
2
votes
1answer
62 views

Moving down the fixed page numbers in a Google spreadsheet

Can you move the page numbers on the left side of the screen down? I have a list that I need numbered, but the very top of the list (meaning the first column) has the categories for said list. So that ...
2
votes
1answer
519 views

Summing Duration Format with QUERY

I want to sum the begin and end with the QUERY function, to be able to subtract them, see table: day | begin | end | -----------------------------------| monday | 9:00:00 | 13:...
2
votes
1answer
259 views

Referencing cells above and to the left in script

I'm trying to make a script for a custom function in google spreadsheet to work out the hours worked in a day function hwtd(). I've got it perfect in the cell (((F8-E8)+(F9-E9))+(IF((F10<E10), (F10-...
2
votes
1answer
2k views

Using cell reference with COUNTIFS formula

I am trying to get this formula to work =countifs(J6:J365, "=David & Victor", K6:K365, "=g3") where G3 is a date value (e.g., 02/24/14). I am trying to count the instances in my spreadsheet ...
2
votes
1answer
732 views

Can you get hyperlinks to work in a published spreadsheet?

I created a Google spreadsheet, included a link within it (using the "hyperlink" function) and then published the file to the web. It turns out that when you access it from the web, links don't work. ...
2
votes
1answer
752 views

How do I reference a cell string to use as a note in another cell?

I'm simply looking for how to link one cell's data into another cell as a note. What I have here simply errors telling me I do not have permission to setNote. :/ function noteSetter() { var ss = ...
2
votes
2answers
152 views

Altering the Sort Filter in Google Spreadsheets

I live in Australia and when I'm putting in dates, I place the date, followed by month then the year. However when I am filtering/sorting my spreadsheet it's coming up incorrectly. How do I edit ...
2
votes
1answer
555 views

MATCH Function does not work

MATCH function does not appear to work at all. Given the following data: $7,109.19 | $867.19 | -$5,374.81 | -$11,616.81 =MATCH(MAX(FILTER(B122:U122, B122:U122 < 0)),B122:U122) returns the error "...
2
votes
1answer
572 views

How do I debug “The action you're trying to perform is causing a fatal error and cannot be performed” errors?

Since today, I'm no longer able to edit a specific Google Spreadsheet. Every time I try to make an edit, no matter which kind, an error message is displayed: The action you're trying to perform is ...
2
votes
1answer
269 views

Get data from specific date in Google Spreadsheets

I’m trying to collect data from specific dates in a Google spreadsheet. For example, I want to get the data from January only. Is this possible?
2
votes
1answer
1k views

Applying IF Statement to entire column

I currently have an IF statement that checks a cell in column B if it is blank. If it is blank it returns No in the corresponding A cell. If it does have a string, it returns Yes. I currently have ...
2
votes
2answers
749 views

How to zero out cell if filtered sum is less than 0

I currently have the exact function set up here: Summing a column based on another column in Google Spreadsheet I would like to know how to make the value 0 if the real sum is less than 0. Any help? ...
2
votes
2answers
2k views

Calculate days between now and a future date

I need to calculate how many days are left from today to a certain date in the future in a Google Spreadsheet. How can I do that?
2
votes
3answers
4k views

How do I create a multiple choice question in Google Forms based on spreadsheet data?

I am making a Google Form for evaluations. I will have a multiple choice question in there and the available options have to come from a list of items, say on a spreadsheet or a text document. I have ...
2
votes
2answers
134 views

Google Spreadsheet - periodically stamp sheet contents

As the title, is there a way to have a "print" of the content of a sheet periodically? i.a., I'd like to have a trigger that every Monday at 08.15 A.M. copy the contents of a group of a cell of a ...
2
votes
2answers
2k views

Multiple views of master spreadsheet that allow editing of master data

I have a monster spreadsheet with lots of contacts in it. Each contact is a row, with different attributes as columns (first_name, last_name, number, etc.). There are a hundreds columns—i.e., too many ...
2
votes
1answer
98 views

How to COUNTIF cells are years in Google Spreadsheet?

I have several dates in let's say: A1:A with this syntax: dd/mm/yyyy and I would like to COUNTIF the cells that are years. I started with this formula, but that didn't work: =COUNTA(UNIQUE(...
2
votes
1answer
672 views

Google docs spreadsheet - limit values to other ranges

I'm trying to create a docs spreadsheet that defines various objects, and these objects sometimes reference each other (equivalent to SQL foreign key). Is there a way that I can limit the values of a ...
2
votes
1answer
182 views

Is there another way to click on the hyperlink in a Google Spreadsheet?

I am aware of the process to click a link on the spreadsheet that involves a small pop-up that says "Go to link..." However, is there a way where the user of the sheet can directly click on the link ...
2
votes
1answer
873 views

Google Spreadsheets: Unique Values From a Matrix

I have a spreadsheet that looks like this: Column A | Column B | An unimportant value 1 | Value 1, Value 2 2 | 3 | Value 3 4 | Value 3, Value 1 | Don't ...
2
votes
1answer
44 views

Can I make modifiable part of the web address passed through in a Google Spreadsheets =ImportHtml function?

Wondering if there is a way to modify part of the web address (passed through in double quotes) in a =ImportHtml function in Google Spreadsheets? eg. www.schoolgrades.com/tom www.schoolgrades.com/...
2
votes
1answer
8k views

Google Spreadsheet - How to filter, SUM and display multiple values with multiple conditions added daily?

I have a spreadsheet that collects dozens of payments made daily through different means. For example, utilities bills, credit cards bills, personnel fees, expenses, etc. They are made in cash, by ...
2
votes
1answer
645 views

Change contents of cell based on date range in Google spreadsheet

I’m trying to get a cell to change its value based on a date range. Let's say I have this list: A B date1 10 date2 25 date3 50 date4 85 On date1 I will have 10 items, date2 I ...
2
votes
1answer
2k views

Creating duplicate Google spreadsheets that filter data from a master sheet and update rows on master sheet

Basically, I have a huge list of data on Google Docs spreadsheets, and a bunch of people that read it. They are not tech savvy. I am trying to create duplicate sheets on a Google spreadsheet that are ...