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.
0
votes
0answers
5 views
How can I add a formula after text in the same cell in Google Sheets
I'm not really sure how to phrase this. I would like to engage a formula, following some text, in a given cell. Perhaps my attempt will make more sense..
Total amount Raised by Students =sum(A11:...
0
votes
0answers
3 views
Google Sheets- How to express a range of values with nested if loops
This is a nested if loop I wrote in google spreadsheets
Whenever I enter a value for B5 between 0 and 1 like 0.5, it says there is "no match".
I think there is something wrong with the last section ...
0
votes
1answer
15 views
Google Spreadsheet IMPORTXML problem to extract from a web page
I'm trying to extract the "Comestibilité" and "Remarques" parts from a page using IMPORTXML in my Google Spreadsheet but I just can't get to them, they are generated by javascript but I cannot figure ...
0
votes
1answer
9 views
Are permissions granted by IMPORTRAGE bi-directional or one-way in Google Sheets?
If I want to import "Sheet A" from one workbook into "Sheet B" in another workbook by using =IMPORTRANGE() inside of Sheet B - when I grant it access to Sheet A is that permissions only 1 way? Meaning ...
0
votes
0answers
10 views
How to auto add user name who have access to Google Spreadsheet?
I already have a script for timestamp but I want to add also the username who have access as well in the Spreadsheet, perhaps to be entered automatically in the third column of offset col. Hope ...
0
votes
1answer
9 views
.getActiveSheet function work for every tab but one
I have the following function that adds the latest edit time to the L1 cell in our spreadsheet -
function onEdit(e) {
// Prevent errors if no object is passed.
if (!e) return;
// Get the ...
0
votes
0answers
13 views
Clear cells throughout a column based on value in corresponding column
I have the below script, but am needing to make a slight adjustment/have a query
Is it possible/how can I restrict this to only work with one specific sheet, not all within the workbook?
How can I ...
1
vote
0answers
7 views
Using Slack's Google Drive integration, but files open in the wrong Google profile
I use 2 different windows to manage each of my Google profiles. When I click through a link from Slack from a colleague sharing a file, it always opens up the wrong profile and I can't view it.
How ...
0
votes
0answers
8 views
Can I activate a filtered view in Google Sheets from a script?
Can I activate a filtered view in Google Sheets using a script? I've been looking for a preexisting function but can not find one. Is this possible to do?
I can't use normal filters as more then one ...
0
votes
1answer
17 views
How to remove duplicate rows based on a column (leaving first one out) in Google Spreadsheets
I am trying to remove duplicates from my Google spreadsheet file.
Here is an initial table example
A B C D
HelloWorld1 2 6 41
HelloWorld1 4 3 5
HelloWorld2 6 46 6
...
0
votes
0answers
8 views
Speed up Google Sheets scripts
I use following scripts to do the following:
Insert newline
Copy info from line below to newline
script takes extremely long, how do I
function rijToevoegen() {
var spreadsheet = SpreadsheetApp....
0
votes
0answers
21 views
Active Date Counter (Google Spreadsheets)
This is a weird request, but I wanted to see if anyone knew if what I'm asking is possible, and if so how to do it. If you take a look at the image below, you will see the following columns: Steam ...
2
votes
1answer
13 views
During Google Sheets formula entry, want keyboard shortcut to select a whole column
In MS Excel, I can type the following formula
=SUM(A:A)
Without taking my hands off the keyboard. I start in cell B1, I type =SUM( then I use the cursor keys to move one cell to the left, hit CTRL-...
0
votes
0answers
20 views
Google Sheet Line Chart where series are in rows
I have a Google Sheet with data in the following format:
| Timestamp | Sensor | Value |
|------------------|--------|-------|
| 2018-11-14 0:00 | A | 100 |
| 2018-11-14 0:01 | A |...
0
votes
0answers
9 views
How to import acquisition date from Google Analytics to Google Sheet on daily basis
I have been using the Google Analytics Spreadsheet Add-on for a while now to export data from GA and to run calculation on it via spreadsheet.
one of the things that I'm not able to do is to create a ...
0
votes
0answers
15 views
How to convert VBA to Google Sheet script
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = ActiveCell.Address
End Sub
1
vote
0answers
28 views
Difficulty appending to cell in Google Sheets
My goal is to automate making a calendar on Google Sheets. I used =DATE(C1,A1,B1) to make a date and then each following cell, I added 1 to the previous cell.
However, now I want to also automate ...
0
votes
0answers
11 views
Data Validation Custom Formula
I'm trying to create a drop-down menu that is dependent on a previous cell in a row.
The data coming in from a DB is M or F. Male or female. Then from what is displayed there I want the next column ...
0
votes
0answers
10 views
Arrayformula and Sum queries. Possible alternatives
I am using a sheet to list and summarise teachers absences and substitutions. Here's the worksheet
Basically in the sheet "Substitutions" all absences and substituions are stored record by record:
...
0
votes
0answers
20 views
Is duplication of Structure and Behaviour a 'fact of life' in the world of Google Sheets. Is it possible to reduce that duplication further
Please note, I am not asking how to remove duplicates from a column of data.
We are transitioning to a relational database system to store data. But, in the mean time we are using Google Sheets to ...
0
votes
1answer
16 views
Google Sheets - select highest value for each unique label
I have a simple spreadsheet which is tracking timeseries data for several users:
Time | User | info
00:01| bob | hello
00:03| sue | hello
00:05| bob | goodbye
00:10| sue | hello again
I would ...
0
votes
0answers
18 views
How to format a negative number red within a mixed formula result
We use the following formula:
="Stu/W: " & TEXT((A$2/B2),"[Red]#,###,##0.00") &"€"
and wish to have the resulting negative currency numbers colored red and positive blue.
Unfortunately ...
1
vote
1answer
19 views
Conditional Formatting with Multiple Conditions Weekday and Time
I'm working with Google Sheets and want to highlight cells in long date format (e.g. 11/13/2018 21:00:00) that are Tuesday and 21:00:00. Here is my attempt:
=AND((weekday(C4:C124)=3),(EQ(RIGHT(C4:...
0
votes
0answers
15 views
How to calculate DATE DIFFERENCE in GOOGLE Sheets?
I have a google sheet with NHL schedules (hockey).
I want to calculate the difference between two games of a specific team.
Example:
Nashville played on 2018-11-12 and last game before was 2018-11-10
...
0
votes
0answers
11 views
Dates entire week Google Spreadsheet with script
I'm building a tracker and I want different pages to represent different weeks, but I don't want to mannualy put in all the dates of the entire week, every week.
Is there a script that I can use that ...
0
votes
0answers
7 views
How to highlight a cell in column A if the text in column B matches text in a list on another tab?
I have a Google Sheet set up with place names in column B as below.
I have another tab on the same spreadsheet with a list that can change from time to time depending on which stores needs items ...
0
votes
0answers
18 views
Google sheets auto add calculations to rows from Google Forms
I have a sheet 'Form responses 1' that is autofilled from a form. I was told to carry out all calculations on a seperate sheet to avoid any issues.
So in a seperate sheet I am pulling in the date, ...
0
votes
0answers
10 views
Multi url parsing in G Sheet
Sorry i am just a newbee in stackexchange, its great solution place, i have followed
Question Edited
I have got to accumulate the data from 7 to 10 urls
which are rss feeds, xml feed and from json ...
0
votes
0answers
6 views
How to stop Google Docs from accidentally switching user accounts
I have been using Google Docs and Google Sheets for years together with the co-director of our company - switching between her account and my account when needed. Within the last couple of months I've ...
-1
votes
0answers
8 views
Google Sheets: cursor staying in cell
By comparison between Excel where when you press keys Ctrl+Enter, the cursor doesn't move from the current cell, in Google Sheets there's no such option. Well I think there's not such option. Can you ...
0
votes
0answers
10 views
How do you get values from column C and D if a value in column B exists
I have a yearly budget spreadsheet on google sheets that has:
Date in column A
Name of shop in column B
How much I spent there in column C
Loyalty points collected in column D
The list is updated ...
-1
votes
1answer
7 views
Setting ImportRange on a delay
Is it possible to set a timer or delay for the importrange function on Google Sheets?
0
votes
0answers
22 views
How to summarize multiple tables
I have a 13 tables that are the 4 week schedules for the coming year. I would like to summarize them to show how many of each type of shift each employee has been scheduled for.
Here is an example:
...
-1
votes
0answers
20 views
Create new worksheets from a list of names in Google
I have a list of names that I would like to create worksheets for, with their name as the Title of the Worksheet.
Is there a script that I can use to make this happen?
0
votes
1answer
13 views
Multiplication Sums across cells?
I know this isn't very professional but i'm very very new to google sheets and spreadsheets in general.
So if I know that Tier 4.3 leather is worth 6 points in the contest, someone brings me 739 of ...
0
votes
0answers
10 views
Next lowest value query
New to queries.
E8 = calculated value based on manually inputted data in B4
D2:D13 = contains set numerical values ranging from .00722 to .003608 from lowest to highest
B7 = query field that ...
0
votes
1answer
8 views
How to get data from a different sheet based one the adjacent sheet in Google Sheets?
How would I change the sheet name in B3 when the name is changed in A3?
Could someone please assist me with this?
0
votes
1answer
16 views
Copy data to new column in Google Sheets
I have a sheet with IMPORTHTML that I want to copy data from one column to a new column daily.
function saveData() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet....
0
votes
1answer
17 views
Stop Google Sheets formatting text as URL
When I type "product.name" or "product.id" into a cell, it's auto–formatted as a URL (different colour, underlined, hover shows it as an external link). But it's not a URL, it's plain text.
Similar ...
1
vote
2answers
16 views
How to count the number of empty rows since the last entry in a column?
I'm trying to get Google Sheets to tell me how many rows it has been since the last "1" was present in column A (see example table below). How do I get Google Sheets to output column B?
An ...
0
votes
0answers
9 views
Modified custom function to add new parameter
First real post here, but I wanted to share back since I find the quite a few of my ideas for things I am working on here.
I was looking for a way to extract only the sheet id for a hyperlinked cell ...
0
votes
1answer
19 views
Sum of rows until a condition changes
I have a Google Sheet with the following format:
A B C D
10-12-18 17.36 Start
10-12-18 17.49 Stop 13
10-12-18 17.49 Start
10-12-18 17.51 Stop 2
...
0
votes
1answer
12 views
Why are sheet Filter Views not updating for new rows?
I have a sheet with two filter views, which apply filter on one column and sort on another. The sheet is populated from the API nightly - it has all worked great but today I noticed things in the ...
0
votes
0answers
6 views
Formula to compare the last two values in a row
I have a spreadsheet with 12 columns (B2:M2), that represent the months of the year. The columns of each row will either be blank, or contain a numeric value for a given month.
What I'm trying to do,...
1
vote
0answers
7 views
Dynamic Sized Image in Google Sheets For Printing Based On Cell
I've made a calculator with Google Sheets to make it easier for Olympic shooters to train at home. The data that it produces is very helpful, but I would like to make it easier for the users to print ...
0
votes
1answer
29 views
Formula for go Google Sheet1 column K
I need a function for column K of Google Sheet1, that says "If the row of Sheet2 contains the same RID number as a row in Sheet1, then fill Sheet1 with the email address located in column K of Sheet2.
...
1
vote
2answers
11 views
Restrict ImportRange to only visible rows
I need to restrict the ImportRange function below to show only visible rows as some of the rows are filtered and some of the rows are grouped.
=importrange("WorkbookID","Sheet1!A2:K13285")
1
vote
0answers
11 views
How to Get Data from Multiple Cells and Combine into a Comment in another cell
I have a spreadsheet of orders (rows) that has room for 70 products (columns). I would like for a script to find every cell that has a value (0's are blank) and then write that value (this is the qty ...
1
vote
1answer
14 views
Sheet Specific Scripts
I'm currently using the below script to timestamp when an edit is made to column 7 on a sheet named "Sheet 1".
function onEdit(e) {
if ([7].indexOf(e.range.columnStart) != -1) {
e.range.offset(...
0
votes
0answers
5 views
Anonymous editors
I am wondering of there is a way to see which anonymous animal did which editing. IOW, I am not trying to see who the animals are, but in the edit history which animal did what editing. Is there a way ...
