Tag: Sheets

google sheets – Dynamic Line Charts for Stock Performance Overtime

I’ve been riding the struggle bus for a few days now trying to figure out dynamic charts.

I am trying to create a dynamic chart that will show stock data for specific increments of time. (30, 60, 90 days, 1, 2,3,5, and 10 Years).

I’ve watched a few tutorials, but they are all for basic, non-complex data. I am trying to do it for line charts with two sets of data. I’ve managed to create individual stand alone charts for each set of data, but I can’t figure out how to build it all into one dynamic chart. Please help. Link below

https://docs.google.com/spreadsheets/d/1IHot5YvtyBNDWDIp0hhnqWpohg9CkubTYJEJHez8K0c/edit?usp=sharing

Google Sheets Google Forms updates

I have created a Google Form for which collects data from a user. Once the use submits the form I have scripts that copy the data to several other Google sheets and then send an email to the appropriate people with the data. This works well. However, when the user edits the form it updates the data in the Form Responses 1 tab correctly but a new row is added to the other sheets. I want to be able to just update the appropriate row/cells in the other sheets, not create a new row because I have two rows addressing the same info.

I have tried saving the URL of the form, i.e., the edit URL, to each of the target sheets so I can match on it later but,

  1. There seems to be a significant delay for the edit URL to be populated in the Form Response 1 tab so the copy fails because at the time of the copy the URL is not there.
  2. how can I determine if the response is from an edited form not a new response?

I am rather new to this so any guidance is appreciated.

Dynamically form a cell value in Google Sheets

This might be very trivial, but I was not able to obtain any concrete result from my Google searches, so asking here.

I want to fill a column with the content of some other column (in some other sheet). The value at the existing place is not organised to the current requirements, so I just can’t directly assign it to the new column. I have another column that specifies the ordering for the new column. So, I want to create my new column with the help of this ordering.

In short, I was wishing to assign my new column as sheet1!A${sheet2!A1} (this doesn’t work).

Say, if I have value 23 at sheet2!A1, then the corresponding value in the new column should be of the cell sheet1!A23.

Searching Multiple Columns From Single Source Sheet in Google Sheets

I have a Sheet where I have all of my data entered. I have been able to filter the information to other sheets no issues when only searching in one column. I am having issues when trying to search 2 columns at the same time.

On the Source Page, I have my date listed under the following headers: Player Name (A), OVR (B), Series (C), Primary (D), Secondary (E), Bats (F), Throws (G), How To Acquire (H), Team of Card (I) & Teams Played For (J).

For most sheets, I have been able to use the following formulae:
=SORT(FILTER(Cards!1:1000,SEARCH(“Giants”,Cards!J1:J1000)),2,false,1,true)

My issue comes from when I try to search both Primary and Secondary at the same time. I was able to use a query that searched both columns but am unable to do using SEARCH.

google sheets – Function/formula to return only the first string proceeding the first carriage return

Is there a function/formula to return only the first string proceeding the first carriage return using Google Sheets?

Example Text:

Bob & Mary Smith   
bob@email.com   
555-111-1234

Assuming the above text is in cell A1, I would like the formula to return the following to Cell B1 “Bob & Mary Smith”

google sheets – I need to extract a data validated cell content and use it as a part of a new formula

I would like to give the user the option to choose between DAYS360() and NETWORKDAYS().
For that, I thought about adding these 2 to a data validation cell.
Afterwards, I would like to be able to add that choice as part of a new formula that calculates the worked days as such
=DAYS360(D12,E12)
Can that be accomplished within Google Sheets without scripts?
I’ve tried myself some formulas, but couldn’t get to any solution.
Thanks.

How to change IFS function to switch in Google sheets?

I have this formula =ifs(OR(A1="January",$A$1="March",$A$1="May"),$A10,OR($A$1="April",$A$1="June"),$B11,OR($A$1="February"),$C10) I would like to use switch instead of IFS without repeating A1=””:

so something like this switch(A1=("January" or "March" or "May"), A10, ("April"or"June"),B10, ("February"),C10)

I know its wrong. So A1 cell is Month name that user will enter A10 , B10, C10 are values to be shown according to criteria in the formula If I use Switch, I need to repeat lot of words as well: so I expect the formula will be like this: switch(OR(A1="January",A1="March",A1="May"), A10, OR(A1"April",A1="June"),B10, "February",C10)

Any thought how not to repeat the (A1=””)? if the value equals to the criteria then

Google sheets range of sheets?

I have multiple sheets in my Google Spreadsheet. Let’s suppose that they are named “1”, “2”, … “n”. I would like to sum the contents of the B4 cells in all of these sheets. Is it possible to specify a range of sheets, or do I need to meticulously include a reference to each sheet?

How to know if Google Sheets IMPORTDATA, IMPORTFEED, IMPORTHTML or IMPORTXML functions are able to get data from a resource hosted on a website?

If the content is added dynamically (by using Javascript), it can’t be imported by using Google Sheets built-in functions. Also if the website webmaster have taken certain measures, this functions will not able to import the data.

To check if the content is added dynamically, using Chrome,

  1. Open the URL of the source data.
  2. Press F12 to open Chrome Developer Tools
  3. Press Control+Shift+P to open the Command Menu.
  4. Start typing javascript, select Disable JavaScript, and then press Enter to run the command. JavaScript is now disabled.

JavaScript will remain disabled in this tab so long as you have DevTools open.

Reload the page to see if the content that you want to import is shown, if it’s shown it could be imported by using Google Sheets built-in functions, otherwise it’s not possible but might be possible by using other means for doing web scraping.

According to Wikipedia,

Web scraping, web harvesting, or web data extraction is data scraping used for extracting data from websites.

The webmasters could use robots.txt file to block access to website. In such case the result will be #N/A Could not fetch url.

The webpage could be designed to return a special a custom message instead of the data.

IMPORTDATA, IMPORTFEED, IMPORTHTML and IMPORTXML are able to get content from resources hosted on websites that are:

  • Publicly available. This means that the resource doesn’t require authorization / to be logged in into any service to access it.
  • The content is “static”. This mean that if you open the resource using the view source code option of modern web browsers it will be displayed as plain text.
    • NOTE: The Chrome’s Inspect tool shows the parsed DOM; in other works the actual structure/content of the web page which could be dynamically modified by JavaScript code or browser extensions/plugins.
  • The content has the appropriated structure.
    • IMPORTDATA works with structured content as csv or tsv doesn’t matter of the file extension of the resource.
    • IMPORTFEED works with marked up content as ATOM/RSS
    • IMPORTHTML works with marked up content as HTML that includes properly markedup list or tables.
    • IMPORTXML works with marked up content as XML or any of its variants like XHTML.
  • Google servers are not blocked by means of robots.txt or the user agent.

On W3C Markup Validator there are several tools to checkout is the resources had been properly marked up.

Regarding CSV check out Are there known services to validate CSV files

It’s worth to note that the spreadsheet

  • should have enough room for the imported content; Google Sheets has a 5 million cell limit by spreadsheet, according to this post a columns limit of 18278, and a 50 thousand characters as cell content even as a value or formula.
  • it doesn’t handle well large in-cell content; the “limit” depends on the user screen size and resolution as now it’s possible to zoom in/out.

References

Related

The following question is about a different result, #N/A Could not fetch url