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

learn more… | top users | synonyms (1)

2
votes
1answer
36 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
20 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
170 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
32 views

Link a range of vertically ordered rows, to a horizontal range of columns

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
63 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
89 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
421 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
41 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
66 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
24 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
57 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
93 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 ...
3
votes
2answers
903 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
84 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
34 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
31 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
34 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
128 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
86 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
91 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
170 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
70 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
44 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
56 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
45 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
152 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
27 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
50 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
62 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 ...
1
vote
1answer
145 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 ...
0
votes
1answer
60 views

Query to copy from Sheet A to B based on true/false

I'm trying to copy data from Sheet A to Sheet B based on if one of the columns has True or False. I'm trying to use: =QUERY( 'A'!A1:I200 , "select * where I = 'True'" ) but this only gives me ...
0
votes
0answers
53 views

Use a combination of the ARRAYFORMULA, SUM and arithmetic operations with wildcard characters

This is in further addition to the existing question at How can I refer to two different columns in a COUNTIF function? . The accepted answer worked like a charm. However, I have a requirement of ...
1
vote
2answers
259 views

How to calculate difference in hours between two time fields

I have two time-formatted cells and I need to calculate the difference (eg 10:30 PM) in hours between the two. Ie. cell 1: 10:30 AM, cell 2: 1:15 PM. Simply =B1-A1 will not do the trick.
0
votes
0answers
45 views

Unique ID in Google Sheets to persist so it's not updated when row is updated via the form?

I'm using this: function onFormSubmit(e) { var sheet = SpreadsheetApp.getActiveSheet(); var row = SpreadsheetApp.getActiveSheet().getLastRow(); var bugCount = ...
2
votes
1answer
60 views

How to make my Google Spreadsheet automatically calculate “days since”

I have a spreadsheet with several columns and one of them is for date (column D). How do I make the next column (E) automatically display the days passed from date in D until now on open? I have ...
3
votes
0answers
133 views

How can I create a Google Spreadsheet whose name and cells auto-complete with cells completed on another sheet?

I'm using Google Spreadsheets to manage projects, and I'm not familiar with scripts or macros (though I'd really like to learn how to use it, so if you have any good tutorials or tips, let me know). ...
3
votes
1answer
259 views

How Can I Solve Error “Exceeded maximum execution time” in Google Spreadsheet?

I found a script which I really need (link) but it gives an error (Exceeded maximum execution time) generally. How can I solve this error? This is the script: function getTotalSum(cell) { var ...
1
vote
1answer
100 views

Date format in Google Spreadsheet is not correct

Whenever entering the date e.g. day-month-year, it automatically changes to month-day-year. I went into format>number>more formats>more date and time formats, and I changed it to day-month-year. And ...
3
votes
1answer
94 views

Individual time stamps for individual sheets in a Google Spreadsheet containing multiple sheets

I am using the New Google Sheets, and I have created a "Master Document" that contains a multitude of sheets, with each expressing different kinds of data. I want a "Last modified" time stamp to ...
1
vote
1answer
50 views

“Go to” search in google spreadsheet

I want to have a search feature at the top of my spreadsheet that will actually take me to a row, on the same sheet, based on searching a column for the matching information. So, I want any user to ...
0
votes
0answers
86 views

How can I synchronise the rows of a Google spreadsheet with Asana tasks?

My startup uses a Google spreadsheet for its client database (linked to a Google form, so that clients can fill their own data in if needs be). We also use Asana for our tasks and would like client ...
0
votes
0answers
48 views

How to create a simple dialog form in Google Spreadsheets with GAS

The functionality that I am going for is this: Click on a menu option Interactive box comes up with 6 textboxes that correspond with a label each In two of those boxes, at most three, a number is ...
2
votes
1answer
99 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 ...
1
vote
1answer
771 views

Google Spreadsheet - changing row colour if value set?

I'm trying to use the following script to change the row colour if a value in column N is set: function onEdit() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ...
0
votes
2answers
116 views

Reflect changes in on sheet to another on Google spreadsheet

I have two sheets on a Google Spreadsheet. One of which is the copy of the other. Now what I want is, when I edit one sheet, the same edit must reflect on the other. How can I do that ?
1
vote
1answer
28 views

“Object not found” error when saving a Google Apps spreadsheet in Box after edit

The corporate entity that I work for is rolling out Box.com, and since we like the collaborative features of Google Apps, I am experimenting with the Box.com integration with Google Apps. Using the ...
1
vote
1answer
125 views

Google Spreadsheet SQL subquery “where not in”

I'm attempting to perform the following SQL command concept, but with two sheets in the same google spreadsheet document: select * from table1 where column1 not in (select column1 from table2) How ...
0
votes
0answers
55 views

Google Spreadsheet Sub Columns/ Multidimensional data

So, I'm trying to create a spreadsheet to track and graph a rifle team's performance. One page with data, and the rest with the graphs. The data needs to be organized by the date of the practice, ...
2
votes
0answers
71 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
1answer
61 views

Google Spreadsheets: Identifying and Working with Any Cells that Match

I am trying to find a function, or a series of functions, that allow me to (1) Identify if any cell in column A matches any other cell in column A, (2) add numbers in Column B that occupy the same ...