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

Reference cell to copy paste in Google Sheets

I am building a Catan gameboard in Google Sheets to share and play with friends interactively. The game starts 'blank' with a frame, and players choose what kind of pieces the board should have, then ...
0
votes
0answers
9 views

Google apps - google docs are opening just as a desktop apps newer in Chroma browser

I am trying to get google docs apps to work from a Chroma browser, the UI always opens a new tab, but as louched the google docs are starting just like a desktop app. The Chroma is opening a new tab ...
-1
votes
1answer
57 views

Aggregating data by date between tabs

I have three tabs: Transactions Payees Categories This is what my Transactions tab looks like: +--------------+-------------+-----------+ | Date | Payee | Amount | +--------------+-...
-1
votes
0answers
17 views

Auto fill a column with formulas when new rows are added

I need to write a formula which looks out for new rows entered into the spreadsheet and then auto-fills the my new cell in column with preset formula. i use it to get task competion date after ...
0
votes
2answers
33 views

Why can't these strings be formatted as numbers in Google Sheets?

In this sheet, the numbers are actually text. I tried Format->Number then Number, Percent and Scientific, and they still appear as text. As experiments: I cannot add 1 to these, and I cannot pass ...
-1
votes
0answers
8 views

Can I use notification feature in Google Docs like it is available for Google Sheets?

Google Sheets has a feature called notification, is that feature available for Google Docs as well?
0
votes
1answer
18 views

How to use a UNIQUE formula with an IFS formula in Google Sheets

For example if a Uniqueifs existed, I'd have =uniqueifs('Data'!H2:H10000, 'Data'!F2:F10000, A18) If a cell in the range of 'Data'!F2:F10000' contains the contents of the cell A18, then.. Print out ...
0
votes
1answer
39 views

How to extract time out of text after specific word in Google Sheets with regex function

I hope someone can help me with my struggle. I need to get the time slots after each "Text_Text" from this one sentence that is in a single cell: Sth 03/30/2020 1:15 PM-03/30/2020 1:30 PM;"Text_Text" ...
0
votes
0answers
8 views

How to make one of lines continuous in Google Sheets?

I drew fitting plot in Google Sheet, but both series were depicted by separate dots How to make red series continuous? If I choose edit, I get only settings for dots
0
votes
1answer
18 views

In Google Spreadsheets how to make a relative column reference within a query?

I'm working with a large data set surrounding about 200 countries at the moment for a school project. As such, I'm using a query formula to transfer raw data to a computed form to be graphed later. ...
0
votes
1answer
14 views

How to add multiple conditions onto a query

so this is completely experimental and not for a class. If anything, I'm doing this because I don't really have class right now. I am self-taught and very inexperienced when it comes to anything code ...
0
votes
1answer
31 views

Reset drop-downs

I have created several drop-down menus, what I would like to know is, is it possible and if so how do I create a script so that once a user has finished with the sheet they press a button or it self ...
-1
votes
1answer
23 views

Google Sheets - Looking for script that auto capitalizes all cells in a specified column

Looking around, I found different things, but I never found the spot where you could specify the range, and I'm not familiar with this script. Can someone please show me a script for this? For ...
-1
votes
0answers
15 views

How to run Google Code Script on all tabs

I've used the script from this tutorial (https://developers.google.com/apps-script/articles/removing_duplicates) to delete the duplicates from my Google Spreadsheet but this only works for the first (...
0
votes
0answers
16 views

Need to split a column from data source in two columns

I need to retrieve specific data from accounting records. In the case of the this example spreadsheet I need to have the values in Column H to be splitted in columns L and M according to the position ...
1
vote
1answer
30 views

TypeError: Cannot read property 'range' of undefined at onformsubmit again after creating an event object

This is what I have written in Script editor so as to auto-apply a formula to a new added row when a new response is submitted on the Google Forms linked to my spreadsheet. First I was using ...
-2
votes
0answers
14 views

Trying to pull a redirect URL destination for use with IMPORTXML

I've got a spreadsheet with about 7000 rows of data. I have a URL for each row, but the URL is entered as a search query that redirects to the appropriate result: URL-LINK - www.shopcwi.com/...
0
votes
1answer
26 views

Google spreadsheets sorting confusion when using formulas

I am sort of confused about the behavior when sorting by a columns content. I have this formula in A1: ={"Page";arrayformula(iferror(filter(Events!A16:A,Events!C16:C="Feedback R"),""))} I get the ...
1
vote
2answers
43 views

G Sheet How to get average duration time?

I have a serie of time duration which are a result of a formula in a column formatted as Duration as well Duration : 80:08:00 53:05:00 50:20:00 86:21:00 59:56:00 44:27:00 #VALUE! 35:44:00 39:29:00 ...
0
votes
0answers
9 views

How can I use Zapier to send an SMS to an entire list of contacts in Google Sheets?

I have a google Sheet (A) containing a list of tasks. A Zap is triggered each time a new row is added to this sheet. I have a Google Sheet (B) containing a list of user's contact details I wish to ...
0
votes
1answer
17 views

Adding data from top rather than bottom

I want to put together a spreadsheet to input all of my future credit card transactions. Obviously, this sheet can get long fast. I would like to know if it's possible to dedicate a row at the top of ...
0
votes
0answers
11 views

Problem with the .setUnprotectedRanges method

I have a problem with the .setUnprotectedRanges method I want next ranges to be added in the loop. If the cell is not empty, another unlocked range should be added, which is in column 3. For ...
-2
votes
0answers
15 views

How to Change A Cell's Value Without a Function in That Cell [duplicate]

I'm wondering if there's a way to update a cell that has no functions inside. I have looked all over the internet and scrolled through all functions but cannot seem to find an answer. Obviously, I ...
0
votes
0answers
57 views

What is the best way to restrict access to random cells in a spreadsheet?

I want to restrict approximately 80% of cells within range(s) from being edited by anyone but a specific group of people I call 'editors'. The other 20% of cells I want to be editable by a set of ...
-3
votes
0answers
14 views

Apps Script Help Please

How do I edit var ws = "Jamie" to include. "Sheet1", "Sheet2" ... etc function addTimestamp(e){ //variables var startRow = 2; var targetColumn = 3; var ws = "Jamie" //get ...
-2
votes
0answers
21 views

Google Forms email notification based off responses

I'm trying to make a Google Sheets script in Google App Script to respond to changes to a spreadsheet from a form. it will eventually be used to send an email to the user with a list of URLs to access ...
0
votes
0answers
21 views

Join names in single cell based on whether a date is between start and end date

I am trying to create a shift scheduling sheet. On one tab I have vacation requests and on another the schedule. I want the names of those people who have vacation to pull into a cell under that date ...
-1
votes
0answers
28 views

Doesn't return a value when turned into Arrayformula

I have a large sheet with around 4000 rows of data, and a config on another sheet. It all seems to work fine when the functions are in each cell, but once i tried turning it to an ArrayFormula, it ...
1
vote
1answer
15 views

How to check and replace cells

I need help trying to figure this out. I am extremely new to formulas and very confused. Here is my thought process: IF B1 contains a comma AND C1 is exactly "none" THEN I want C1 to be replaced ...
0
votes
1answer
28 views

Display selected row for match in any columns from a dropdown list in Google Sheets

I'd like to create a lookup from a dropdown with all unique sheet data that finds a match within any column and returns the selected row items. Let's say that each row represents a single device ...
0
votes
1answer
21 views

Google form adding more than one row every time

I know that when a new response is submitted on Google form, a new row is auto-added to the linked spreadsheet. I use several onformsubmit triggers for my form and linked spreadsheet. But I do not ...
0
votes
1answer
13 views

How can I score an assessment in Google Sheets where the answers to each question are weighted differently?

I want to make an assessment tool in Google Sheets that asks a bunch of questions and provides a score. Each question has a set of possible answers, so they're all multiple choice. But each of the ...
-1
votes
1answer
19 views

How to get the text from the Other field in a google forms question to be used in the visualization?

On a check all that apply (checkbox type) question, some respondents used the options I created and othere people used the other box. The issue is that for the people who used the other box, the data ...
0
votes
1answer
35 views

Exception: The parameters (null) don't match the method signature for FormApp.Form.getItemById. (line 38, file “Code”)

when I am running the script below I am getting the message - Exception: The parameters (null) don't match the method signature for FormApp.Form.getItemById. (line 38, file "Code") it has been working ...
0
votes
1answer
22 views

How to flip query results in Google Sheets?

I am trying to select multiple rows from one row in a source sheet based on another column in the source sheet. E.g.: Name Start Add Bob 2012-01-01 01:01:00 10:00:00 Tim ...
0
votes
0answers
30 views

how to apply changes to all dropdown questions in forms

I am using a script to update dropdowns in a Google form from a spreadsheet. There is a Questions#1 worksheet on which there are all questions that should be updated automatically in the form when any ...
0
votes
1answer
36 views

TypeError: Cannot read property 'getId' of undefined

I have written this script to my spreadsheet which is connected to a google form too, and connected dropdown function to a button on spreadsheet so as be easier to run the script. first it worked well ...
0
votes
0answers
14 views

Is it possible generate google spreadsheet within a user's drive through a web app?

I am very new to coding. I am trying to build a web app that can has a google sign-in/ authentication of each user Allow user to fill out a webform where they should enter details of customers ...
0
votes
1answer
18 views

How can I get GoogleFinance to see the return of a stock from a specified date range?

How can I see the percentage of return from a stock from a specified date range to today? For example: February 19, 2020 to today = -31.93%. So far I have: =GOOGLEFINANCE("GOOG","price") / index(...
0
votes
0answers
16 views

How can I get GoogleFinance to see 1 year return of a stock from a specified date?

This works so far but how would I do this if I wanted to do 365 days from a specified date instead of today? For example, 2020/2/19 and 365 days prior. =GOOGLEFINANCE(A2,"price") / index(...
0
votes
1answer
14 views

How to use a checkbox to choose to add the value or not in total?

I would like to create a budget based on categories of items. I would like to be able to add the items into category types so I can see what I spend of "Food" for example. I am trying to use the ...
0
votes
1answer
22 views

How to use Lookup formula for new rows in Google Sheets submitted via Google Form?

I am using the following Lookup formula in a Google Sheet: =LOOKUP(F2, AreaCodes) I need that formula to be triggered when new rows are added via a Google Form. I'm guessing the solution has ...
1
vote
1answer
43 views

Issue trying to sum different columns

I am trying to sum 3 different columns and I'm not being successful. My function is: =ARRAYFORMULA(SUMIF(MONTH(D:D), 2, A:C)) Where D is a column with dates, and A,B and C columns with currency (A-...
0
votes
0answers
13 views

Compare two GSheets files and paste a cell value if selected values match

I would like to compare two different GSheet files (NOT sheets within the same file) and return a cell value if three (or more) values match. In File 1 ( https://docs.google.com/spreadsheets/d/...
0
votes
0answers
13 views

How to fetch only the required data set for a query in ImportJSON?

I am trying to fetch the latest price of BTC/INR from a JSON HTTP URL of a exchange. https://api.wazirx.com/api/v2/trades?market=btcinr I am using the following formula in Google Sheet to Fetch the ...
0
votes
0answers
12 views

Extracting the price from an external page using IMPORTXML on Google Sheets?

I am running a Google Sheet and I want to extract the price of Bitcoin from a site called CoinMarketCap. The site is quite simple as is mainly a listing, and have a table full of different ...
0
votes
0answers
23 views

How to write multiple Google Sheets functions properly in set.formula function [duplicate]

I have a Google spreadsheet connected with a Google form. When a new response is submitted, the formula in the upper rows should be copied down to the new added (by Google form) row. I have used once ...
0
votes
1answer
16 views

How to get first element of split Google Sheets

Within Google Sheets, the function =SPLIT(IMPORTXML("url here", "//title"),"|") returns the following: {"Linear Algebra", "School of Mathematics", "College Name", "City Name"} which takes up 4 cells ...
1
vote
1answer
24 views

How do I copy data from multiple Google sheets and paste into master Google sheet while maintaining format?

Each day I paste assignments to employees in each employees respective sheet. These assignments have hyperlinks in portions of the data. At the end of each day I copy their assignments for that day ...
0
votes
0answers
14 views

Google Sheets, Scroll to the bottom of each page

I have used this function to cause the page to scroll to the bottom on load. It works on the first sheet loaded but it does nothing when you change sheets. They remain at the top requiring us to ...