Questions tagged [google-sheets]
For questions about Google's web application to create and edit spreadsheets on a web browser or app. 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 chance that you get a satisfactory answer.
6,654
questions
0
votes
0answers
3 views
Cannot figure out how to change selected cell
I have the following script:
function onSelectionChange(e) {
const sheet = SpreadsheetApp.getActiveSheet();
const resultA1Notation = "D2"; // Change
const range = e.range;
const ...
0
votes
0answers
5 views
stacking columns in sheets
I am using an add-on called stackarray to stack a bunch of vertical data sets on top of each other. How can I add a blank cell between each dataset? I tried including one in the stackarray function, ...
0
votes
1answer
6 views
Jump to last row in Column A, with blank rows between data and land in Column A
This script works and jumps from a user menu on tool bar to last row but since columns are A to Z, it always lands on the last row and in Column Z and I just navigate to the left to Column A
Is there ...
0
votes
1answer
18 views
Formula parse error on importrange query
I'm trying to recreate what's happening in this video at 7:17
https://youtu.be/5S7laJS9meU?t=437
I want to merge two sheets and remove the empty rows.
=QUERY({IMPORTRANGE(L2 ,"sheet1!A2:F20")...
1
vote
2answers
18 views
how to create a more accurate calculation of months with google spreadsheets?
Im using this formula =DATEDIF(B2; C2; "M") on Googles spreadsheet to calculate months.
I expected when entering a begindate B2 22-4-2013 and enddate C2 31-8-2014 to get an exact result: 16,...
0
votes
0answers
8 views
Arrays and repeating cells [closed]
I am using an add-on called stackarray to stack a bunch of vertical data sets on top of each other. How can I add a blank cell between each dataset? I tried including one in the stackarray function, ...
0
votes
0answers
4 views
How do I copy protection rules from one Workbook to another in Google Sheets
I am an educator and since the beginning of the year we moved all our Mark Books to Google Sheets. Between the classes and subjects we have about 200 active workbooks with some complicated IF ...
0
votes
0answers
5 views
Is there a function to query/add values from a tab/sheet and keep fixed even if deleted or changed from the reference sheet/tab?
I have a sheet with dynamic values entered by a team. These data can change daily (deleted or changed or added more data) like list of names who are willing to participate in an event. I want to make ...
0
votes
0answers
5 views
When using sheet protection, how to add exception to a slicer?
I'm using a slicer in a sheet and I would like to protect all the cells in this sheet except this slicer so other users can manipulate data. I tried to add the cells behind the slicer like H1:H2 or E1:...
0
votes
1answer
9 views
Find whitespace and replace with string from formula in Google Sheet
I have list of cities and I want to create URL for each of them in Google Sheet.
Scenario is most of the part of the URL is same only at the end city name is different so I created a formula
=...
0
votes
0answers
5 views
How to sum range of values if date =specific date but keep result fixed next day?
If I want to sum of dynamic cell values (numbers) across range if date is specific (like 2 Feb 2021) and keep the maximum result fixed so I can draw a chart of percent reached.. Like elections for ...
0
votes
0answers
9 views
Google Sheets formulas that needs to read data from drop down boxes
I am trying to create a master spreadsheet that is pulling information from several other sheets into this one master sheet.
I then plan on using this master sheet to pull statistics.
In the sign up ...
0
votes
0answers
9 views
Google Sheets importxml help [duplicate]
I am trying to scrape the number of photos statistic from a Facebook Group (currently 432,780) at:
https://m.facebook.com/groups/553511078046085?view=info
I have copied the relevant xpath and used the ...
2
votes
2answers
21 views
DGET fails to look properly when two entries are similar
I am running into a weird DGET error in Google Sheets.
in one table, I have the following entries:
Column A Column B
Horse Wins
Daedalus I 15
Daedalus II 12
Panacea I ...
0
votes
0answers
10 views
Work flow using that employees can use to submit expenses for reimbursement and also request supplies [closed]
Can anyone recommend a work flow using some kind of web application that my employees can use to submit expenses for reimbursement and also request supplies? We're not a large company, around 10 ...
0
votes
0answers
9 views
How to save a specific sheet of Google Spreadsheet a CSV values in a single cell of another sheet within the same Spreadsheet?
Can scripts be used to export the contents of one sheet as a comma separated values, and save those comma separated values as text within a cell of a different sheet within the same spreadsheet?
...
0
votes
0answers
8 views
Obtain breakdown based on a different column
Is it possible to obtain nested values of a column immediately after the value they are related to from another column?
Basically, I have a value (Order Item Cost coming from a unique Order Item Id), ...
1
vote
0answers
12 views
Adding together two amounts from different sheets on Google Sheets
I have a Google Spreadsheet, which has two sheets within.
I want to take an £ amount from Sheet 1, and a £ amount from Sheet 2, and add them together in Sheet 3.
Is this possible?
0
votes
0answers
12 views
Auto-sorting in Google Sheets and excel by custom valued sorting
I want my data to be auto sorted by 2 columns every-time I append or change values. Also sorting is not just A to Z or Ascending/Descending but Custom valued sorting as example shown below:
High
...
1
vote
0answers
12 views
Count number of times duplicates appear
I'm struggling with this one.
Column A has a students name.
Column B has the name of the exam they took.
If they fail the exam they will need to take it again.
I would like to know which exams are ...
0
votes
1answer
19 views
Is it possible to import data into Google Sheets that is in a <div>?
I've been using =ImportHTML() but it seems a bit limited to what it can import from websites, from what I've seen it looks like you can only import from a site that has the data in a table or a list, ...
0
votes
2answers
31 views
How to make the cells of query function results with borders that are dynamic according to rows/column numbers?
https://docs.google.com/spreadsheets/d/1VsEvt2KnYGnlN_qHjkjPohVHF5MUqfyHD7tSk0VpkqA/edit?usp=sharing
I would like to make the cells that have query results in All_Patients tab with borders instead of ...
0
votes
1answer
17 views
Google Sheets - value in cell different from text
Within one cell, I would like the value of the cell to be treated differently from the text inside that cell. For example, I would like the cell to display "$148 (123 euros)", but the value ...
1
vote
2answers
30 views
How do i use the query in google sheets to return N results from each tab
How do I use the query in Google Sheets to return N results from each tab.
So I have this formula:
=QUERY({sheet1!A2:D;sheet2!A2:D}; "select * where Col4>=65 order by Col4 desc Limit 3")
...
1
vote
3answers
24 views
Count instances of character in a column
Is there a way to count instances of characters down a column, without creating further columns that do row-by-row counts of each character?
Below is an outline of what I'm trying to do.
Note that no ...
0
votes
0answers
17 views
How do I make formulas effective only starting from tomorrow in Google Sheets?
I made a Google Sheet for insulin-dependent diabetics, to calculate insulin units based on their meal content and glucose reading inputs. Each day has its own block of cells for the patient. When I ...
0
votes
1answer
10 views
Jump to cell address mentioned in other cell
A1 shows address of last cell un a column (e.g. A572 or A577). Every time address will change if adding new row (there can be blank rows in between of rows with data).
I need a link in B1 to click and ...
1
vote
0answers
12 views
Extract the first three unique characters of a name to create a code
I have a column D where I store the VENDOR COMPANY NAME.
I have a column B where I want to calculate a SHORTCODE unique for each vendor. This code will be the first 3 letters of the VENDOR COMPANY ...
0
votes
0answers
15 views
Google sheets: Regexextract next or previous whole word or number from cell in sheets
How do I extract the next word or number (including comma and delimiter) to a search string from a cell in sheets?
And how the previous?
={$AV$14;
ArrayFormula(IF(ISBLANK($AT$2:$AT),,
iferror(
value(...
0
votes
0answers
16 views
How do I update a single Google Sheet tab with information from two tabs on the same sheet when a form is submitted?
I have two forms that contain similar information. One asks for more in-depth information that is needed sometimes from external customers while the internal one doesn't need as much information.
I ...
0
votes
1answer
35 views
is there a formula that will allow me to pull data from one google sheet tab to another based on a if contains conditional
I have a list of start dates in one tab in Google sheets. I want to pull only the start dates that reflect 2021 into a new tab, as well as pull the corresponding info for that entry from the other ...
1
vote
1answer
22 views
Is there a way to detect with Google-Apps-Script whether Google Sheets is opened from Android app or from desktop browser?
I have a small script that I have further reduced for the sake of this question. Basically it implements the onEdit function in which it may show an alert (using SpreadsheetApp.getUi().alert()) and ...
0
votes
1answer
9 views
Google sheet sum minus sum gives small number when should be 0
I'm doing accounting in google sheet so my column C and D are all numbers with 2 decimal points. But somehow when I subtract sum of debit and sum of credit, the result is a small number when it should ...
0
votes
1answer
13 views
How to convert a rational number like 1.6 to the higher integer number?
How can i convert the number 1.6 to 2, 3.8 to 4 and 1.2 to 2. Any number that has0.0 to be converted to the next number . Round will round the number to the nearest integer but I want to the higher ...
1
vote
1answer
20 views
How to solve the issue of sorting numbers in 00-00 format?
https://docs.google.com/spreadsheets/d/1VsEvt2KnYGnlN_qHjkjPohVHF5MUqfyHD7tSk0VpkqA/edit#gid=1418057437
I have this sheet that I enter all data of patients in (Data_Entry) tab and I want these data to ...
0
votes
0answers
17 views
Jump to a cell, address of which is located in another cell
A1 has a formula that shows the address of the last row with data (A958, A959). I need to make a link in A2 to the cell address of A1. (eg. If A1 has Value C456, I need to jump to Cell C456 by ...
1
vote
0answers
19 views
How do I create a list of ranked numbers in Sheets––one that will adjust if I change a number?
I have a column of numbers 1-50 in a Google Sheet, which are a ranking list for a different column of fifty items (items are ranked relative to one another, so 1 is the best in the list and 50 is the ...
1
vote
0answers
9 views
How do I specify a consistent interval for a date-range axis in a Google Sheet Chart
I have a Google Sheet with a set of deposits and withdrawls and the running balance. I have added a chart to the sheet, so I can visualize what the balance has been over time and when deposits or ...
1
vote
2answers
19 views
How to test apps script addon
I have created an addon using this guide.
code.gs
function onOpen() {
SpreadsheetApp.getUi().createAddonMenu()
.addItem('Start', 'showSidebar');
}
function showSidebar() {
const ui = ...
2
votes
2answers
51 views
How to sum a range columns from another google sheet
I get this daily data consumption spreadsheet report from a vendor that looks like this
userid feb1 feb2 feb3 . feb29
u1 100 34 23 . 4
u2 0 24 ...
0
votes
1answer
12 views
How to match formatting of one cell in Google sheet with another?
I typed "2:30pm" into a cell in a Google sheet, and it formatted it as "2:30 PM". I typed "2:30pm" into a different cell, and it formatted it as "14:30".
What I ...
0
votes
0answers
21 views
Remove duplicates but keep data based on several criterias
I have a database with thousands of rows and want to remove duplicates but also keep records from earlier in certain conditions.
Here is my problem:
Column A will have unique numbers for referals in ...
0
votes
1answer
21 views
How to apply a conditional formatting rule on the last row of similar cell value across one column?
I am trying to conditionally format the last row that has similar values of sorted data based on country column. In this example of my test sheet, I'm sorting the data from A3:C18 by placing the ...
2
votes
1answer
42 views
Adding together multiple Sum Filter or Sumifs with multiple conditions across multiple sheets?
So I have a budgeting spread sheet. I have an overview page that I need to link to a "Checkings" sheet and a "CC-Chase" sheet.
To calculate the amount spent on a specific category ...
0
votes
0answers
16 views
Auto-generate unique sheets when you add a new row in Google Sheets?
I'm a fairly new user to Google Sheets, I'm trying to set up a sheet whereby when you add a new line in one sheet, it creates a separate check-list sheet. So when a new user is added to the master ...
3
votes
1answer
70 views
Calculate time difference within one cell
I'm trying to calculate the time difference and get the result 04:00. This obviously doesn't work because MINUS expects 2 arguments and instead gets a range.
Generally, is there a way to output a ...
1
vote
1answer
33 views
Combine multiple IF statement/condition into one Google Sheet column
How can I achieve in Google Sheet so that the column will auto populate the correct "vendor" based on the selection on "model" and "size". Refer below:
There will be 2 ...
0
votes
0answers
18 views
Can I create a Google Sheet macro to take the content from one cell and make it the hyperlink for another cell?
I do a report every month that has a column of Page titles and the next column is the URL. My boss wants the page titles to have the hyperlink applied to them so we can remove the hyperlink column. I'...
-2
votes
0answers
10 views
How do i give a certain cell a number value in google sheets? [closed]
I am not sure how to word this, but i am making a spreadsheet and wanted to know how to give a number value of 600,000 to B2. So if i put 3 into B2 it would be 1.8 million and then using B7 i could ...
0
votes
0answers
10 views
Change Values cell location from number of another cell
So I have =HYPERLINK("#gid=0&range=A2"; "TMI"&Status!B4&" - "&Status!C4&" - "&Status!I4).
What I want to do is to change number 4, after,...
