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. Please consider adding a demo spreadsheet to your question, this will greatly increase the chances to get a satisfactory answer.

Filter by
Sorted by
Tagged with
0
votes
2answers
58 views

Automatically apply new formatting and remove time from a dynamically added Date-Time in google sheets

I am new to Google sheets and don't know how to write excel/google sheet formula to apply automatic formatting to a date+time value added through an external script. I have a CRM integrated with ...
2
votes
1answer
16 views

Sumif and Sumifs with open ranges gives #ERROR in one sheet, correct result in other

Here's my test sheet: Localization is USA with timezone GMT+1 I've tried substituting ',' with ';' to no avail. https://docs.google.com/spreadsheets/d/1pZku_X6b49uMhKivnv3N1uv6sekYrhY12IlUct_Zudc/...
0
votes
0answers
20 views

Unable to filter rows in a sheet populated by QUERY

I am trying to filter one column A-Z to pair items listed in more than one selling app. However, if I apply an A-Z filter for a column with item codes, the sheet rearranges everything per the filter ...
1
vote
2answers
18 views

How do I get the address of a range with a formula?

Let's say I have a range called WidgetTypes in cells C5:M5. I want my formula in cell A1 to return C5:M5 or $C$5:$M$5. What formula goes into cell A1? cell("address",WidgetTypes) returns $C$5 but I'...
1
vote
1answer
7 views

Google Spreadsheet sum when filtering

I have spreadsheet that looks like this: Now when I filter on a company I want the total to display the sum of the whole of B column. Since I am very new to spreadsheets I was hoping someone could ...
0
votes
1answer
8 views

How many 'importranges' are being used in a selected Google Sheet

How to find out how many importranges are being used in a single Google Sheet.
1
vote
1answer
19 views

Help to modify code please

I would like to use this code : function onEdit(e) { if(e.range.rowStart === 1 && e.range.columnStart === 1) { e.range.offset(0,1).clearContent(); } } but only on a tab called '...
1
vote
1answer
36 views

How to join VLOOKUP results of multiple sheets into one cell

I am trying to use TEXTJOIN across multiple sheets, IF a cell is is the same I would like it to return the value from another cell in all sheets that are associated with it. In this example, I am ...
0
votes
1answer
215 views

Running script to replace “null” with nothing in Google Sheets

I tried to do this through a macro but I'm unable to have it actually record. I'm not a coder so I've been recording macros and using the code from there to create an add on. I need a script that can ...
0
votes
1answer
2k views

Exporting Google SpreadSheet to PDF (filename referring to a cell D7)

I have developed a script which allows me to export a spreadsheet to a pdf file with a click of a button. I have assigned the script to a macro so that it is triggered by clicking on an image. Script ...
2
votes
2answers
411 views

How to hide your selection on a shared file?

I want to share a Google Spreadsheet document as view only, no problem doing that, but the issue is that anyone that views the sheet while I'm working on it can see the cells that I'm selecting (they ...
0
votes
2answers
36 views

COUNTIF to display which results meet criteria

I'm working on a spreadsheet, where I'm using COUNTIF to check the number of times a sheet range meets a criterion. This works. I'd like to see which cells (their contents/locations) which met the ...
-1
votes
1answer
209 views

How can I use ARRAYFORMULA (or something similar) with GOOGLETRANSLATE?

I'm learning a new language and I recently discovered Google Sheets' GOOGLETRANSLATE function. I thought it'd be neat to have a personal dictionary of translations which I can even annotate. So I ...
2
votes
1answer
357 views

How do I reference the previous column of a calculated field on a pivot table? Looking to calculate percent change

I have a pivot table with a bunch of columns indicating the value over time, and would like to create a calculated field showing the change over each value. How would I reference the previous (and ...
2
votes
1answer
138 views

How can I use a formula to split a text into individual characters, regardless of length?

I have a list of words in Japanese. I want to write a formula that will input the word, e.g. ひらがな, and output it as an array of however many characters: ひ + ら + が + な, no matter the length of the word....
0
votes
1answer
60 views

Trying to fetch data from shopback.com.au 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
1k views

How to trigger a script in Google sheets when a value changes

I try to fire a script when a value in a certain cell or range of cells changes. (it's not changed by the user, so I can't use onEdit) I've tried to use onChange, but this doesn't fire my script at ...
1
vote
1answer
147 views

Run a Script When Tab Programmatically added to a Google Spreadsheet

I'm trying to pull data from an Adwords report to a Google Sheet through an Adwords script. This will be done on a daily basis. Every time the Adwords script runs, a new tab is created (and an old one ...
1
vote
1answer
2k views

Update Cell If another cell is not empty

There are two columns N and O. When any cell in column N is filled the adjoining cell in column O needs to filled with "Yes" if not it stays empty. This is the script I'm using. var s = ...
1
vote
1answer
9 views

How to auto populate potential email combinations in Google Sheets

For example I have an email for John Smith and am trying to find out his appropriate middle initial. The email format is smith_john_(middle initial)@company.com but I cannot get the email to auto ...
3
votes
1answer
38 views

Reference formulas from one sheet into multiple sheets

I would like to reference (pull in) formulas (not the result of the formula) from one sheet into multiple sheets so that they calculate on the sheet they are referenced. For example, if I have =...
0
votes
1answer
28 views

Auto numbering Google sheets

I want to have auto numbering dependent on cell drop down box in helper column so i can sorted out. If someone pick Start in A2 to have number 1 in B2. Then if they skip one or two and chose Start ...
2
votes
1answer
99 views

How to automatically create a templated Google sheet?

I'm trying to figure out how to automatically create a templated Google sheets worksheet when a new entry is made in a different sheet. So the flow would be: Person's name is added into Google sheet ...
5
votes
2answers
22k views

How can I merge two columns by using QUERY?

I have a master spreadsheet that contains columns like so: FirstName LastName Gender etc, etc, etc I need to import from that master sheet into another sheet that filters by one of the other ...
1
vote
2answers
95 views

Next Payment Date

I am wondering how to calculate the next payment date a client has. We know when their payment date starts, what the frequency of their payments is, and of course today's date. However, I cannot seem ...
-1
votes
2answers
4k views

Clear cell based on modification of another cell

Is there a function where if you modify a certain cell, the contents of another cell will be cleared? E.g. If A1 is modified the content of B1 will be cleared.
0
votes
2answers
23 views

Add columns based on rows

I'm currently working on setting up a sheet, but I want to add columns in one worksheet based on rows in another worksheet. I have no idea if this is even possible, I've tried googling it. Might be ...
2
votes
1answer
33 views

“Invalid Input, must be item on specified list” Google sheets error

I am super new to anything coding and google sheets. All of what I've done was was trying to follow the directions of another question I saw on this site. However, as expected, nothing is working! ...
1
vote
1answer
21 views

“Calculating formulas” in Google Sheet disaster

I have a worksheet with 13 sheets. the largest one has 2000 rows. I have already deleted all the possible blank rows i could. I have also read all the guides on how to make a Google Sheet faster and ...
1
vote
1answer
17 views

How to reference the result of a IMPORT function as a defined name in a single cell

Note: The motivation for my question is just formula readability. My sheet is working fine. Let's pretend I have an IMPORTXML function that's currently returning 2 columns and a varying number of ...
0
votes
2answers
240 views

How to group data (by row and column) in a Google spreadsheet?

I have data as follows: Name | Value | Y/N | Value | Y/N A | 10 | Y | 12 | N B | 100 | Y | 120 | N A | 20 | Y | 10 | Y B | 200 | N | 120 | Y C | 1000 |...
1
vote
1answer
19 views

How to calculate the following mathematical expression in Google Sheets?

I am trying to calculate the total money that can be made from dollar cost averaging every year using Google Sheets. For example: how would I evaluate this?
-1
votes
1answer
256 views

IMPORTXML error - empty table

I am trying to import a table from the lower part of the following website to Google Sheets: https://vistacapital.com.br/ I used the formula: =IMPORTXML("https://vistacapital.com.br";"//*[@id='sos-...
0
votes
1answer
87 views

Google spreadsheet mail as Excel attachment

I want to mail Google Spreadsheet as excel attachment, and also I want to write 4 to 5 lines in the body of a mail, and also I want to send this mail to many peoples some in "To", and some in "Cc" can ...
0
votes
1answer
29 views

How to apply scripting only to rows with data in them?

I have a Google Sheet that adds multiples rows per day as forms get entered. What's the best, most hands-off way to apply formulae to rows as they get added? I've built a script, but it applies the ...
0
votes
1answer
63 views

Alternate to Nested IF statements

Goal here is to have the sheets search a row, then once it finds the first instance of >=0, return the cell value that contains the month+concatenate the value in the cell next to it (or any other ...
0
votes
3answers
111 views

Checkbox to activate a column in QUERY

Is there a way to activate/deactivate a column in QUERY function? I have a query to query and selection of several columns Select Col1,Col31,Col32,Col33,Col25,Col26, and want to have a checkbox for ...
1
vote
1answer
235 views

Dynamic protection based on cell value

In my Google spreadsheet, I want to protect all cells below a checkbox if it's value is true and unprotect if it's value is false. Every cell in row 3 is a checkbox that this has to work for. Example ...
0
votes
2answers
66 views

IF Function with too many arguments

I want to apply this formula =IF(A2= "", "", IF(REGEXMATCH(A2, "Brand"), "Brand", if(regexmatch(A2, "Loans"), "Loans" , if(regexmatch(A2, "Cards"), "Credit Cards", if(A2, "Generic"),"Generic",...
1
vote
0answers
24 views

Insert image to spreadsheet from google drive folder, png image dose not show, why?

I have an image in a folder that I want to import into my spreadsheet (in a cell). I'm using the script from Insert image from Google Drive into Google Sheets (with some modifications). But this ...
0
votes
2answers
10 views

Opposite of SWITCH function? (i.e. enums in Google Sheets)

In programming, I could use a switch/case statement to do something like this: func animalNameFromType(animalType: Int) { switch animalType { case 1: return "dog" case 2: return "cat" ...
1
vote
1answer
58 views

Restrict entry to a group of cells based on the value in another cell

I am creating a work schedule where the staff enters their own schedules. I only want a certain number of staff each shift. I already use COUNTIF to show the number of staff signed up for each shift. ...
11
votes
4answers
26k views
+100

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 ...
0
votes
1answer
17 views

If checked, add row to data chart?

Basically, I have been working on some automated solutions to show the date and data for the most recent 4 weeks on this data sheet. There are still some bugs to work out, but the last thing I need to ...
0
votes
1answer
21 views

How to give a column a green border if the second row's holds a date = TODAY()?

I'm trying to make a useful schedule sheet to keep track of all of my class assignments: I want to write a script in the script editor that says, for(Column A-Z){ if(A2,B2,etc = TODAY()){ ...
0
votes
0answers
16 views

Linked Google Sheet requires authorization although public

I have made a Google Sheet that links to another Google Sheet, using IMPORTRANGE(). Although the linked sheet has Public accessibility, I am still required to authorize access to the linked sheet. Why ...
29
votes
11answers
55k views

Can I change cell padding in Google Spreadsheets?

I don't want to stretch all rows and columns to make them more readable than they are by default...
1
vote
0answers
14 views

How to have font size adapt itself to cell size?

I have a document where I use one sheet for presentation purposes. It's like a window/viewer of the content of the main sheet (the cell contents are all references to the main sheet). I built myself ...
2
votes
1answer
38 views

Timestamping purchase entries

I’ve created myself a budget tracker where I can track what I bought and how much I spent across a few categories. I specified column B (purchase entries are rows) to be the date and time at which I ...
26
votes
2answers
58k views

Fill value in multiple cells in Google Sheets

In MS Excel, after selecting a range of cells it is possible to enter a value and press CTRL+ENTER to have that value set in all selected cells. Is there a keystroke in Google Spreadsheets that would ...