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.

1
vote
2answers
11 views

Split cells not importing first 0

I am using split formula: =IFERROR(SPLIT(REGEXREPLACE(" , "&U4, "(\d)", "$1 ,"), ", ")) It works great except if I have 0250, it imports it across as 250, even with the cell it's importing ...
0
votes
0answers
5 views

IMPORTRANGE - ask permission blocked

I want to use an IMPORTRANGE formula. The formula is correct and the user as acess to both sheet as an editor. My problem comes when I allow connection from doc1 to doc2. The "Adding permissions info"...
1
vote
1answer
45 views

How can I prevent editors from accessing the script editor in Google Sheets?

I have an auto sort script on a Sheets document that I will be giving multiple people editing permission to, to add to the file. I have created a trigger so the script also runs when other users edit ...
-1
votes
0answers
13 views

Google Sheets Script

I appreciate this is similar to my previous post, but I separated it for clarity as the script has been modified (rightly or wrongly)… I believe I've got what it needs in there but it just won't run ...
0
votes
1answer
10 views

Sort sentences based on formatting

I have a list of sentences in Google Sheets, each word in a different column. There is a stupid man. How we came here we don't know anything about. Finally, it has come to this. Shaking my head. How ...
0
votes
2answers
526 views

Setting minimum row height

In the past, I used to open a new spreadsheet, resize all lines (the default feels way too cramped for me), and then enter data. If at some point I entered data longer than the row height, it would ...
0
votes
1answer
107 views

Translate UrlFetchApp Response HTML To Google Sheet

I'm new to working with APIs/CURL and trying to figure out how to get a response (not response code) into a sheet. Here's my code: function myFunction() { var url = "https://platform.quip.com/1/...
1
vote
0answers
12 views

Change button image on click

Is there a way I can change a button image on click? At the moment I am using circles over an image which I am switching between red and green. is there a way I can automate these into buttons that ...
0
votes
2answers
30 views

Export PDF link sheet ID auto change

I have the link for export as PDF a specific range on a sheet =HYPERLINK("*****export?exportFormat=pdf&format=pdf&gid=962914045&range=A1:H68&pagenumbers=false&gridlines=false&...
2
votes
1answer
478 views

Any way to separately Validate and create a Dropdowns in Google Sheets?

Sample Scenario: Sheet 1 has a 3 Dropdowns from sheet 2 Sheet 2 has two lists, {"A";"B"}, and a second dynamic list that copies the first IF the value doesn't already appear on sheet 1 The behaviour ...
1
vote
1answer
7 views

Using SUM in Google Sheets with indirect ranges

I need to use SUM formula to fill the Total formula until next Total comes in Google Sheets like this: Total 6 A 1 B 2 C 3 Total 15 A 9 B 6
5
votes
2answers
6k views

IMPORTDATA Google Sheet CSV on Google Drive

So I'm starting to really get into utilizing Google Drive to streamline my many personal projects. One of the things I'd love to do is figure out how to download CSV files onto my Google Drive, and ...
1
vote
2answers
192 views

Jump to today's date when opened

I have found a similar question here: How to make Google Sheet jump to today's column when opened? I want to do a similar thing, but I have the dates in row 5 not in the column. I tried several ...
0
votes
2answers
44 views

Copying cell values to AVERAGE function

I’m pulling Free Cash Flow Data from Morningstar to Google Spreadsheet. I have formula: =IMPORTREGEX("http://financials.morningstar.com/ajax/exportKR2CSV.html?&callback=?&t="&B4,"Free ...
0
votes
2answers
22 views

Google Sheet use filters to sort with Unique

In google sheet, I have a data set A: In another sheet I am creating a dynamic list using the UNIQUE formula: However, when I use filters to sort the data, it gets all scattered leaving empty cells ...
0
votes
1answer
139 views

2 timestamps in different cells at the same time [duplicate]

function onEdit(event) { var timezone = "GMT-7"; var timestamp_format = "MM-dd-yyyy-hh:mm"; // Timestamp Format. var updateColName = "Ext Warehouse"; var timeStampColName = "Date/Time Change";...
0
votes
1answer
8 views

How to get 15 mins high low data from Google Finance?

I would like to retrieve 15 mins High Low data from Google Finance to Google Sheets. But unable to get any formula. Please guide me.
3
votes
1answer
6k views

How can I solve error “Exceeded maximum execution time” in Google Sheets?

I found a script which I really need (link): function getTotalSum(cell) { var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); var sum = 0; for (var i = 0; i < sheets.length ...
3
votes
1answer
2k 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 ...
2
votes
1answer
162 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 != '...
5
votes
4answers
6k views

Can Google Sheets have a discontiguous named range?

I have a data set with multiple values that are not adjacent. I wish to make a named range of these values. Simplified example data: A B 1 ABC 536936062976506 2 XYZ ...
0
votes
2answers
17 views

Using NumberRangeFilterBuilder for a slider controlled variable input?

I've been looking for a slider control in sheets where I can play with a variable input by moving the slider left or right. Within the following example: A1 = Value | B1 = VAR | C1 = A1*B1 I want ...
0
votes
1answer
17 views

Conditional formatting formulas in Google Sheets

I want to use Conditional formatting so all of the cells in column A change colors whenever the matching cells in column B have the word "later". ex: If cell B20 contains "later", then I want A20 to ...
1
vote
2answers
605 views

Duplicate sheet but keep protected range with exact same user as only editor?

The following code duplicates a sheet based on a template, and copies over the protected range permissions from the template sheet to the new sheet. However, the permission range is now there in the ...
1
vote
2answers
17 views

Conditional Highlight: color change cell only if multiple others filled in

I have a shift sign-up sheet in which I would like the cells in column A to be highlighted only once there is a value (name) in the corresponding cells in columns B:D. In this example, I would like ...
1
vote
1answer
24 views

How to use the SPLIT function in Google Spreadsheets with different delimiters and with some delimiters with multiple characters?

My question is similar to this one: SPLIT function in Google Spreadsheets with OR statement but has the following difference: I want to use the delimiter "apple" and the delimiter "." within the ...
1
vote
1answer
15 views

Total of cells formula for numbers as text

I want to total up the numbers of split cells. I have an example as follows: 1234 0000 5678 each number is in its own cell, I want to total it all up underneath to get 6912, so 1234 + 0000 + 5678 = ...
0
votes
1answer
25 views

QUERY with Week Number

I use query to extract two columns from another sheet with data. Column A has dates Column B - numbers I want to extract only rows that correspond to a specific calendar week number. For example 19. ...
0
votes
1answer
12 views

How to nullify empty output errors in Google Sheets?

I am using the following code in many cells of the sheet, =SPLIT(REGEXREPLACE("" & S2, "(\d)", "$1,"), ",") not all are going to contain data at any one time. How do I stop the empty cell value ...
1
vote
2answers
1k views

CPU vs. RAM for Google Sheets

I use Chrome and Google Sheets with large spreadsheets which have IMPORTRANGEs, VLOOKUPs and pivot tables. I am looking to improve speed. For this use which has the bigger impact CPU or RAM? Perhaps ...
-1
votes
0answers
11 views

How do you write an equation to look in one cell for an IF AND result and return the adjacent cell's value in Google Sheets?

I am trying to write an equation for a catering form that recognizes a number of beverages the client wants and returns the corresponding price per cup based on a pre-determined volumetric dependent ...
0
votes
1answer
35 views

Why would I be getting the error: Too many simultaneous invocations on a simple once a day trigger?

I've searched around and still can't find anything directly related to the failure message I'm receiving. The script hasn't changed and it used to run just fine, but now in the past week or so it's ...
0
votes
1answer
9 views

COUNTA and FILTER returning a 1 when it should return a 0

I am using a summary page to show stats from a registration page. I want to show the number of registrations based on their date of registration (which is in column A of the Registrations page. I ...
1
vote
2answers
69 views

How can I allow results from a Google Sheets 'filter' action to be mutable?

Brief: The results of a filter are not mutable. I cannot directly add/edit rows to the cells in and around a filter result due to a #REF Error: not expanded because it would overwrite data error. ...
1
vote
2answers
1k views

How can I set the increment value for Google Spreadsheets to auto-increment?

I have a series of cells referencing other cells: | Row | a | b | col B shows | |-------------------------------| | 1 | 1 | =a2 | 2 | | 2 | 2 | =a4 | 4 | | 3 | ...
0
votes
1answer
12 views

Get translation using Google Sheets and IMPORTXML

I want to scrap the phonetic guide for a translation in Google Translator using Google Sheets. For instance, if you enter the page: https://translate.google.com/#view=home&op=translate&sl=...
0
votes
1answer
29 views

Google Sheets onEdit - Run Two Scripts

I'm trying to run two scripts in one sheet - Only the last one runs though - I'm sure I'm missing something small but any ideas? function onEdit(e) { moveRowsFromSheetToSheet_(e); ...
0
votes
1answer
31 views

How do you get current date to be added to a cell when a check box is checked in another cell

I want to get the current date added into a cell when a tick box is checked in another cell. I currently have another formula in the date cell which changes its colour when the tick box is checked, ...
0
votes
1answer
8 views

Split function doesn't get the result that I expect

A1 value is 485065/1, 2, 3 and I expect 485065 and 1, 2, 3 but it comes out 485065 and 1, 2, 2003. =split(a1,"/",false) Please help me why it does.
0
votes
0answers
5 views

ga:dcmLastEventAttributionType (CM Attribution Type (CM Model)) with Last Interaction Conversions

we have a Google Sheet that is using the Google Analytics Add-on. we can pull all kinds of 'normal' metrics fine but we need to pull our Last Interaction Conversions from GA into this Spreadsheet. ...
1
vote
2answers
27 views

Importrange when copied to other cells automatically change range

I am using importrange to pull data from my responses page to my spreadsheet. I have completed the first sheet but I now need to go into the sheet to follow on where sheet one has run out of space. ...
0
votes
1answer
10 views

Trying to fetch data from this website with ImportXML function in google sheet

I am trying to fetch store name and cashback percentage from this website using google sheet's importxml function. Put following commands with no success. Please help =importxml("https://www....
0
votes
1answer
12 views

1 plot with several lines with a common X axis but unique X and Y values… is this possible?

I am trying to create a graph which shows several smooth lines that are defined by an X and Y value. the problem is that they have to share the X-axis. Is there a way to efficiently add the other ...
0
votes
0answers
4 views

Google Sheets Grouping with filter

I am trying to achieve the following: a sheet where a client is in one row and below it his/her items appear, they form a group these groups stay together for the actions cut/paste/filter/sort, as ...
-1
votes
1answer
8 views

Script to Duplicate Template Tab Multiple Times and Rename Tabs from Cell Range?

Basically, I have a list of 200 people's names in the range 'Names'!A1:A200. I have another tab named Template. I need a script to duplicate Template 200 times and rename each of the tabs to the name ...
1
vote
1answer
9 views

Issue with finding the averages of multiple rows at once

I am currently tasked with hundreds of rows of data for a project. I need to take the average of each row, but there are way too many rows for me to do by each individual row. Is there any way to take ...
6
votes
4answers
17k views

How can I make some data Autosort in a spreadsheet on edit?

I am working with a simple spreadsheet that has name/lastnames in it. I need for the spreadsheet to automatically sort the entry alphabetically by the last name (column B). I have used the following ...
0
votes
1answer
28 views

OnEdit won't apply to all tabs in Google Sheets

I am running a script that automatically organizes priority in a to-do list. It works great on the current tab, but when moved to a sheet within the worksheet the same OnEdit function will not run. ...
-1
votes
1answer
1k views

Delete multiple sheet tabs in Google Sheets

In a Google Sheets spreadsheet (Excel terminology = workbook) is it possible to delete multiple tabs (Excel terminology = worksheet) in one go without having to right click each one and delete it.
0
votes
1answer
3k views

Make cell “flash” onOpen rather than onEdit

I need a Google sheets script which will make the background color of a cell flash. Currently I have a script which works fine, but onEdit. It would be far better in this case if the script could work ...