#importhtml

[aprendaem5minutos]

No vídeo de hoje mostramos cinco funções pouco utilizadas no Google Planilhas que podem fazer a diferença nas suas criações. O Google Sheets é uma alternativa online, gratuita e muito poderosa para o Excel da Microsoft. Bora ver? Link do vídeo: https://youtu.be/DHuBRRhVxUI #funcoes #planilha #googlesheets #googledocs #googledrive #excelgratis #importhtml #qrcode #traducao #cotacaodeacoes #conversao #medidas #curiosidadesexcel #google #excelgratuito #aulaexcel #sigodevoltaquemmesegue #sigodevoltasempre #sigodevoltanahora #sigotodosdevolta #sigotodos #mesegui #sdvperfil #sdvsdv https://www.instagram.com/p/CVQHXuYA-Vh/?utm_medium=tumblr

[extractleads45-blog]

Data extraction tools: for beginners and professionals

There are all kinds of tools for extracting unstructured data from non-reusable files such as a PDF or web pages of governments and organizations. Some are free, others are paid and in other cases languages such as Python are used to do so.

Sometimes that information is structured and, on other occasions, it is unstructured. Today there are numerous Facebook Data Extractor tools by which a developer can extract data from complex formats such as a PDF or from one or several web pages, which is known as web scraping.

Web scraping could be defined as the technique by which a team of developers is able to scratch, scrape or release data from websites of governments, public institutions or organizations to access private or public data that can be published or distributed in open format. The problem is that most of the data of interest are in non-reusable and transparent formats such as a PDF, for example.

To access and distribute this type of information there is a large number of Linkedin Data Extractor tools. This is a guide for the use of the main methods of data extraction.

Web scraping tools (data scratching)

Within Google applications, the great search engine developed its own Excel called Google Spreadsheet (Google spreadsheets). This Google Data Extractor tool has almost all the features of Microsoft Excel, but it also has some added features thanks to the content indexed on the internet by the search engine: reading RSS feeds, changes in web pages or data extraction.

All this is possible by using formulas such as ImportFeed , ImportHTML and ImportXML . With the second one, any user can extract data from tables or listings in an orderly manner from any web page. Depending on whether it is a table or a list, the type of formula varies in one of its elements.

Type of information

We can choose between 3 types of Facebook data:

Page data. Here we will find the general figures related to the fan page such as:

No. fans throughout the downloaded period

I like you and I don't like them daily (so we can know peaks that go out of the way to find the reasons for incorporation or exit)

Unique users who have interacted with the page a day or week (and determine which strategy was followed on those dates with more / less success)

Organic Reach / Payment

Impressions

People who are talking about this

Likes by country, city or language

Negative feedback: hide post, report post, etc.

Clicks to buy button or other interactions with the fan page how to call.

Publications. We usually move on to concrete. In the downloaded document we will find all kinds of information related to the published posts:

Number of people who interacted in any way with the post

Google Maps Data Extractor

Comments, likes and shares

Video data. Although in the other documents we find information about the videos, it will be where they are centralized.

[xmasqoo-haineke]

LESSON #2 (JOUR 3520) BASIC DATA ANALYSIS.

SHARE YOUR ANALYZED GOOGLE SHEET.

Data analysis with Google Sheets

Prepare by reading ... From: Basic data analysis Through: Writing about the analysis results & Recap

"To complete this assignment, reproduce each of the analyses shown in the Basic data analysis with Google Sheets lesson video LOCATED AT https://www.youtube.com/watch?v=3ZIHlkE6TYc , but using the latest-available MSA-level unemployment data, not the 2018 data shown in the video. Then, share your Google Sheet and submit it's URL so I can verify that you have reproduced the analyses correctly.

You will receive full credit if:

Your Google Sheet is reachable by me via the URL you submit. If you don't correctly share your Google Sheet, I will receive a message indicating that I don't have access to the sheet. You can verify that your sheet is accessible by logging out of your Google Account and then trying to access the sheet via the URL you have obtained, or by trying to access the URL via a different Web browser or device that is not logged into your Google account.

Your Google Sheet correctly reproduces each of the formatting procedures and analyses shown in the Basic data analysis with Google Sheets lesson video . Again, that is https://www.youtube.com/watch?v=3ZIHlkE6TYc Specifically: The sheet includes a tab that uses the =importhtml function to import the latest-available MSA unemployment data from the table at https://www.bls.gov/web/metro/laummtch.htm.

The sheet includes a second tab to which you have pasted, as "values only," the data from the first tab.

The data on the second tab has all of its column headings in a single row, with one heading per column.

The sheet includes a third tab containing data for only the 10 metropolitan statistical areas in Tennessee, plus one row of data for the U.S. as a whole.

The third tab includes a "Difference from U.S. rate" column that uses a formula to correctly calculate each MSA's difference from the U.S. unemployment rate.

The third tab includes a "Tennessee averages" row that uses formulas to correctly calculate the averages for the one-year-ago and current unemployment rates among the Tennessee MSAs (that is, the current equivalents of the "3.35" and "3.41" rates shown in the video).

The third tab's most recent unemployment data have been sorted in descending (A --> Z), so that the MSA with the smallest (that is, best) current unemployment rate is at the top of the data, and the MSA with the largest (that is, worst) current unemployment rate is at the bottom of the data.

The third tab's data contains each of the the formatting touches shown in the video: bold-faced column headings and U.S./Tennessee summary data, and underscores separating the column headings from the MSA data, and the MSA data from the U.S./Tennessee summary data.

All column widths on the third tab have been adjusted to allow sufficient space for the data the columns contain. The "Tennessee average" row's figures on the third tab have been rounded to a single decimal place, as shown in the video.

You have renamed each of the three tabs to correctly reflect the time periods and data they represent

Submit your sheet's URL to me, using this online URL Submission Form" (MTSU & BLAKE, 2021).

[fee-calculator]

How To Embed HTML Into a Google Doc

How To Embed HTML Into a Google Doc

Google Docs is a very flexible word processor in many ways, but many people don’t realize that you can embed HTML in a Google Doc.

There are several ways to do this. One is to actually copy the HTML document straight from the browser and paste it into Google Doc. Another is a trick using the importHTML function in Google Sheets to customize HTML formatting, which can then be embedded in a…

[sjon5719]

Google Sheet Functions That Can Do More Than Just Maths

We all have used Google Sheets for many reasons. One can smoothly perform addition, subtraction, multiplication, average, and other mathematical operations, but Google Sheets has more potential than that. The users can do a lot more than just solving some mathematical problems. Google Sheets are widely used for professional work in big companies to save their data and segregate them properly. It helps the users in translation, which means one can easily translate a phrase in another language. Google Sheets provides a wide variety of languages to consumers. Hence one can select the language in which he/she wants to translate. It also helps to pull out the vital information from the web and allows you to perform formatting whenever required. So if you are a Google Sheet user, or want to learn more about the exciting features of it, this article is for you. So stay with us till the end.

Capitalization issue

Sometimes when users want to collect information from different sources, chances are the data do not have uniformity. Some words might be in upper cases while others in lower, and some might have a mixture of both. To correct that, and imagine if the data is in hundreds, one has to spend long hours, which is eventually not possible. Here Google Sheets ‘PROPER’ feature kicks in.

‘PROPER’ fixes the problem by reformatting the texts in Sheets. It ensures that all the text starts with a capital letter and followed by small letters. So it includes features like UPPER, LOWER, and TRIM. All these have their specific job to do in the Sheets.

UPPER changes the text into uppercase, LOWER changes the text into lowercase and TRIM deletes the unnecessary space between the text. So in this way, Google sheet solves the problem and provides uniformity to the data.

Translate

One may receive a Google sheet having the data of an unknown language. Now it would become impossible to understand the data and work on it. But Google Sheets has a solution for it. Google Sheet introduced the ‘GOOGLETRANSLATE’ function. The Translator translates a phrase or text in any preferred language. All one needs to do is command ‘GOOGLETRANSLATE’. The user can also choose language accordingly. For example, if one wants to change a phrase into Spanish in cell A2, all he/she needs to type is command ‘GOOGLETRANSLATE(A2, “fr”,”es”)’

The general syntax is: GOOGLETRANSLATE(text, [source_language, target_language])

where: text- is the text to translate

           source_language- is the text that is to be detected.

           target_language- is the text in which the data will translate

Since there are different languages available to change the text, one must learn the code of the language to give the command correctly, some of them are given below.

English(en), Spanish(es), Korean(ko), Japanese(ja), etcetera

One can also detect the language of the data. For example, if you receive any file with unknown language data, and you want to know which language is this, you can have easy access to that also. Google Sheet has the ‘DETECTLANGUAGE’ feature, which allows detecting any language.

IMPORT RANGE

Let’s say you want to share specific details from some spreadsheets but not the entire data. Or you want to grab some data out of another. You don’t want to involve everything that was in the original file. Here Google Sheet provides an IMPORTRANGE function. It provides a hassle-free job to its users. One can easily extract out the exact information from a preferred sheet or a cell. To do that, one needs to give a simple command, and the rest will be done quickly.

The syntax of the command can be followed as:

‘IMPORTRANGE (“spreadsheeturl”, “Sheetname!Cell_number1:Cell_number2”)’

where “spreadsheeturl” should be removed by the actual spreadsheet URL. And, the name of the sheet and the details must be according to the requirement.

IMPORTHTML and IMPORTFEED

One can also use IMPORTHTML and IMPORTFEED to gather information directly from the web. One needs to give the command IMPORTHTML or IMPORTFEED followed by the required link, and the Google Sheet will automatically do the rest.

For example, to gather a list of popular restaurants, the user needs to type

‘=IMPORTHTML( “https://en.wikipedia.org/wiki/List _of_popular_restaurants”, “table”, 3)’

And the Sheet will collect all the required data within a few seconds.

Or, to get updates about sports from the New York Times, type.

‘=IMPORTFEED( “link_of_the_website.”)’

Mini Chart

Google Sheets also provide a fantastic function called SPARKLINE. With this function, one can create a mini chart of the given data in the Sheet. And can easily track all the information accordingly. It also allows users to analyze the data by using SPARKLINE quickly. To use it, one needs to follow a command, and the spreadsheet will do the rest. One can also select which chart they want to put in like.

Syntax of command

‘=SPARKLINE(data, [options])’

where, data- the range containing data, and[optional]- which means you can customize the chart with the options mentioned below:

“Line”-  for line graph

“Bar”- for bar chart

“Column”-  column chart

“Xmin”- minimum x-axis

“Xmax”- maximum x-axis

“Color”- the color of the line, and etcetera

These are features you can use in your Google Sheet and can do your work effortlessly. You can share it with your friends to make their life easy. Try these tips and features, and you will like working on the spreadsheet. I hope this article was helpful.

SOURCE:- Google Sheet Functions That Can Do More Than Just Maths

[dotomtom]

Googleマイマップ機能、超便利です😋今朝投稿したIMPORTHTML関数と組み合わせて、試しに食べログ上位のラーメン屋5店をマッピングする動画を作成しました。 他にも、こんな際にも活用できます。 ✅競合の実店舗をマッピング ✅クライアント所有の物件をマッピング（不動産案件） https://t.co/DshnqdI2xt RKawtr さんのツイートから

[donnierock]

Importar datos desde una web a una hoja de cálculo de Google Drive

Importar datos desde una web a una hoja de cálculo de Google Drive

Seguimos con el tema de la ofimática y con las hojas de cálculo de Google Drive, que parece ser un material que os interesa. Hoy vamos a ver una función muy útil aunque un poco liosa: Con IMPORTHTML() podemos importar a la hoja tablas o listados de una web.

La función recibe tres parámetros: el primero, como es lógico, es la URL de la páginadesde la que queremos importar los datos, irá entre…

[allaboutwebdata]

The following are the functions that you can make use of for web scraping using Google Sheets:

ImportFEED →  for scraping data from RSS feed

ImportHTML → for scraping data from table and list

ImportXML  → for scraping data from structured data types

These functions will extract data from websites based on different parameters provided to the function.

[valentinayue-blog]

Simple Scraping con Google Sheets (2020 actualizado)

Este herramienta de web scraping puede automatizar el proceso de copia y pegado repetitivos. En realidad, las hojas de Google pueden considerarse un web scraping básico. Puede usar una fórmula especial para extraer datos de una página web, importar los datos directamente a las hojas de Google y compartirlos con sus amigos.

En este artículo, primero le mostraré cómo construir un web scraping simple con Hojas de cálculo de Google. Luego lo compararé con Octoparse web scraping automático. Después de leerlo, tendrá una idea clara sobre qué método funcionaría mejor para sus necesidades específicas de web scraping.

Opción#1: Cree un web scraping sencillo con ImportXML en Google Spreadsheets

Paso 1: Abre una nueva hoja de Google.

Paso 2: Abra un sitio web de destino con Chrome. En este caso, elegimos ’Games sales’. Haga clic derecho en la página web y aparecerá un menú desplegable. Luego seleccione "inspeccionar". Presione una combinación de tres teclas: "Ctrl” + "Shift" + "C" para activar "Selector". Esto permitiría al panel de inspección obtener la información del elemento seleccionado dentro de la página web.

Paso 3: Copie y pegue la URL del sitio web en la hoja.

Opción#2: Intentemos obtener datos de precios con una fórmula simple: ImportXML

Paso 1: Copie el Xpath del elemento. Seleccione el elemento de precio y haga clic con el botón derecho para que aparezca el menú desplegable. Luego seleccione "Copiar", elija "Copiar XPath".

Paso 2: Escriba la fórmula en la hoja de cálculo.

=IMPORTXML(“URL”, “XPATH expression”)

Tenga en cuenta que la "expresión Xpath" es la que acabamos de copiar de Chrome. Reemplace la comilla doble "" dentro de la expresión Xpath con una comilla simple ''.

Opción#3: Hay otra fórmula que podemos usar:

=IMPORTHTML(“URL”, “QUERY”, Index)

Con esta fórmula, extraes toda la tabla.

Ahora, veamos cómo se puede lograr la misma tarea de raspado con un rastreador web, Octoparse.

Paso 1: Abra Octoparse, cree una nueva tarea seleccionando "+ Tarea" en el "Modo avanzado"

Paso 2: Elija su grupo de tareas preferido. Luego ingrese la URL del sitio web de destino y haga clic en "Guardar URL". En este caso: sitio web de Game Sale http://steamspy.com/

Paso 3: Aviso El sitio web de Game Sale se muestra en la sección de vista interactiva de Octoparse. Necesitamos crear una lista de bucles para que Octoparse revise los listados.

1. Haga clic en una fila de la tabla (podría ser cualquier archivo dentro de la tabla). Octoparse detectará elementos similares y los resaltará en rojo.

2. Necesitamos extraer por filas, así que elija "TR" (Fila de Tabla) desde el panel de control.

3. Después de seleccionar una fila, elija el comando "Seleccionar todos los subelementos" en el panel Consejos de acción. Elija el comando "Seleccionar todo" para seleccionar todas las filas de la tabla.

Paso 4: Elija "Extraer datos en el bucle" para extraer los datos.

Puede exportar los datos a Excel, CSV, TXT u otros formatos deseados. Las hojas de cálculo requieren que se copie y pegue, pero Octoparse automatiza el proceso. Además, Octoparse tiene más control sobre sitios web dinámicos con AJAX o reCaptcha.

Más recursos:

Create your first scraper with Octoparse [Video]

Los 20 mejores programas gratuitos de web scraping

Comparacion de las 5 mejores herramientas de web scraping

[ebunomoni]

Holy crap, you can (partially) scrape websites with Google Sheets? "6. Sheets can pull in data from any publicly available web page, too, as long as the page has a properly formatted table. The secret lies within the IMPORTHTML command" https://buff.ly/2IRCSlp

Holy crap, you can (partially) scrape websites with Google Sheets? "6. Sheets can pull in data from any publicly available web page, too, as long as the page has a properly formatted table. The secret lies within the IMPORTHTML command" https://buff.ly/2IRCSlp https://buff.ly/2IRCSlp

[education30and40blog]

Use Real Data and Put into Google Sheets

Use IMPORTHTML to Get Website Data into Your Google Sheets In the information age, it is easy to find real data to use rather than contrived situations in a textbook or on a worksheet. Who is Bob and why is he carrying 40 watermelons? Use this student guide with your students to help them get …

[xmasqoo-haineke]

LESSON #2 (JOUR 3520): BASIC DATA ANALYSIS WITH GOOGLE SHEETS

"TIPS AND HELPFUL LINKS:

You will find the latest MSA-level unemployment data here:

https://www.bls.gov/web/metro/laummtch.htm

And here's the =importhtml function, set up with the arguments needed to retrieve the MSA unemployment data, as shown in the video. You could copy it into Cell A1 of your Google Sheet, then press "Enter":

=importhtml("http://www.bls.gov/web/metro/laummtch.htm","table",2)

Here is a list of the 10 metropolitan statistical areas in Tennessee. The list might come in handy during the filtering step of your analysis:

Clarksville, TN - KY [Metropolitan Statistical Area] Memphis, TN - MS - AR [Metropolitan Statistical Area] Knoxville, TN [Metropolitan Statistical Area] Chattanooga, TN-GA [Metropolitan Statistical Area] Jackson, TN [Metropolitan Statistical Area] Morristown, TN [Metropolitan Statistical Area] Kingsport - Bristol - Bristol, TN-VA [Metropolitan Statistical Area] Johnson City, TN [Metropolitan Statistical Area] Nashville - Davidson - Murfreesboro - Franklin, TN [Metropolitan Statistical Area] Cleveland, TN [Metropolitan Statistical Area]

EXTRA STUFF:

If you happened to want state-level unemployment data, you could find it here:

https://www.bls.gov/web/laus/laumstch.htm

And here's the =importhtml function, set up with the arguments needed to retrieve the state-level unemployment data. You could copy it into Cell A1 of your Google Sheet, then press "Enter":

=importhtml("https://www.bls.gov/web/laus/laumstch.htm","table",2)

Here is a list of the states in what the Bureau of Labor Statistics considers the "Southeast Region" of the United States. You could filter for these states much like you filtered for the MSAs in Tennessee, as shown in the video.

Alabama Florida Georgia Kentucky Mississippi North Carolina South Carolina Tennessee

What about county-level unemployment rates? You can get them in "plain text" format here:

https://www.bls.gov/web/metro/laucntycur14.txt

... or in Microsoft Excel format (inside a .zip compressed file) here:

https://www.bls.gov/web/metro/laucntycur14.zip

... but getting data from either format into Google Sheets would require import techniques different from those shown in the lesson video. Not harder, really. Just different. If you're interested, let me know. I'd be happy to show you" (MTSU & BLAKE, 2021).

[meaningfuldatadesignluc]

Aantekeningen Processing

// AANTEKINGEN

// CMD + T voor het goed aanpassen van de uitlijning

Header bij data is ervoor om te zorgen dat het excel bestand vanaf rij 2 uitgelezen wordt.

// Les 2 // kwantitatieve data is getallen // kwallitatieve data is tekst // webscraping is een techniek om data uit websites te halen , structuur te geven en in een database te zetten. // API = application programming interface // API = altijd door de eigenaar gemaakt dus weet welke data er uit komt.

// vergeet niet de bilbliotheek te instaleren

// FF0000 - FF = rood, groen, blauw // Dit is 100% rod // 00FF00 = 100% groen

// section, header en footer zijn siblings (van de body)

// =IMPORTHTMl // =IMPORTPHP // =IMPORTXML // =IMPORTXML("http://shakespeare.mit.edu/romeo_juliet/full.html"; "//a") // //h3 = selecteer alle <h3> elementen // [@class='name'] = slecteer alle elementen met een class "name" // //h3[@class='name'] selecteer alle <h3> elementen met een class "name" // =IMPORTXML("http://shakespeare.mit.edu/romeo_juliet/full.html"; "//h3") // //p/i = selecteer alle <i> elementen met children van een <p> element // //a[contains(@name,'speech')] = selecteer alle <a> elementen met de tekst "speech" als name atribuut // =IMPORTXML("http://shakespeare.mit.edu/romeo_juliet/full.html"; "//a[contains(@name,'speech')]")

// Dit is om specifiek op een woord te zoeken // =IMPORTXML("http://shakespeare.mit.edu/romeo_juliet/full.html"; "//blockquote/a[text()[contains(.,'Romeo')]]")

// https://regexr.com/ // \s[a-z]{5}\s = voor alleen tekst te zoeken van 5 letters precies

//basis commnts // size() - groott van display // printIn()- schijf in de console // backgound() kleur // noStroke() // fill(color, alpha)    (alpha = transparant) // noFill() // frameRate() /* variablenen - kunnen niet beide cijfers en letters veranderen int    = alleen cijfers string = alleen tekst

RANDOM random(low,high) nois(x,y) Perlin Noise

positie  0    1    2    3    4  eerste element in een array staat op positie 0 int[]a={100, 200, 300, 400, 500} of int[] b = new int[3]; int[] = data type

int[] a = {500, 5, 30}; a = append(a, 400); println(a[4]);

de uitkomst hiervan zou 400 zijn omdat het de 4de plek is in de rij

sort doet eerst de cijfers en dan pas de letters (zet alles in alfabetische volgoorde).

TRANSFORMATIES

translate() scale(x) rotate() pushMatrix() popMatrix()

Interacrie

void keyPressed(){} void keyreleased(){}

void keyPressed(){  background(int(random(0, 255)));  if(key=="l"){    background(255, 0,0);  } }

mousX    = huidige positie van de muis mouseY   = huidige positie van de muis pmouseX  = laatste positie van de muis pmouseY  = laatste positie van de muis

[etauthenonprogrammercoder]

[GAS][ノンプロ研]郵便局の手紙（定形・定形外）の料金を求める便利なツール

概要と経緯

今週のお題についての取り組み。 できれば「国内の料金表（手紙・はがき） - 日本郵便」のサイトからの最新情報に対応させたかったけども、断念。 =IMPORTHTML("https://www.post.japanpost.jp/fee/simulator/kokunai/one_two.html","table",1) を使って、ぼんやりと値を取得することには成功しました。

まとめとコード

6 種類の形状から選ばせて、重さをグラムで入力してもらえると料金を返します。

function inputPrompt() { /* 入力 1 */ var ui = SpreadsheetApp.getUi(); var uiTitle = '以下の項目から、発送形状を選んで、その番号を入力してください。'; var uiPrompt = '1. 定形郵便\n'; uiPrompt += '2. 定形外郵便物 - 規格内\n'; uiPrompt += '3. 定形外郵便 - 規格外\n'; uiPrompt += '4. ミニレター\n'; uiPrompt += '5. レターパックライト\n'; uiPrompt += '6. レターパックプラス'; var form = ui.prompt(uiTitle, uiPrompt, ui.ButtonSet.YES_NO); if (form.getSelectedButton() !== ui.Button.YES) return; form = parseInt(form.getResponseText()); /* 入力 2 */ var gram = ui.prompt('重量を入力してください。', 'グラム単位', ui.ButtonSet.YES_NO); if (gram.getSelectedButton() !== ui.Button.YES) return; gram = parseInt(gram.getResponseText()); var response = letterDeliveryFee (form, gram); ui.alert(response); } /** * 形状と重さに対して、料金を返す * * @param {number} 形状 1-6 * @param {number} 重さ (g) * @return {string} 送料もしくは、該当なし */ function letterDeliveryFee (form, gram) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var tableSh = ss.getSheetByName('table'); var values = tableSh.getDataRange().getValues(); var ui = SpreadsheetApp.getUi(); for (var i = 0; i < values.length; i++) { if (values[i][0] !== form) continue; if (values[i][1] < gram) continue; if (values[i + 1][1] >= gram || values[i + 1][0] === form + 1) { // HACK: || の後半は上限値に対する処理 return '料金は ' + values[i][2].toString().replace(/(\d)(?=(\d{3})+$)/g, '$1,') + ' 円です。'; } } return '形状の上限重量を超えています。'; }

20 行目の値は、形状 6 の上限値を超える処理用として。

参照サイト

Google スプレッドシートの関数でWebからデータを取得する - プログラマでありたい

[zacdhaenkeau]

6 Things You Didn’t Know You Could Do In Google Sheets

Google Sheets is a versatile tool for PPC marketers. Whether you’re using it build reports for your clients, analyze data, or other miscellaneous PPC tasks, getting familiar with some of the lesser known functions can be beneficial.

  Here are the features we’ll cover in this post:

Import Sitemaps

Currency Conversions

Sparklines

Import from other workbooks

Check for 404s and other errors

Combine data from multiple sheets

1. Import Sitemaps

  Function Used

  =IMPORTHTML() or IMPORTXML()

  Syntax

IMPORTHTML(url, query, index)

url – The URL of the page to examine, including protocol (e.g. http://).

The value for url must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.

query – Either “list” or “table” depending on what type of structure contains the desired data.

index – The index, starting at 1, which identifies which table or list as defined in the HTML source should be returned.

The indices for lists and tables are maintained separately, so there may be both a list and a table with index 1 if both types of elements exist on the HTML page.

But Why?

  This function is useful if you’re building out new campaigns for an account.

  Examples

  For this example, the sitemap is a .xml page, so we’ll need to use the =IMPORTXML function.

    The import function returns the list of URLs on the left, then we use the SPLIT function to break apart the URLs into categories for our campaign structure plan.

    2. Currency Conversions

  Function Used

  =GoogleFinance()

  But Why?

  If you have a client that spends in an international currency, but needs to be converted to USD for billing or reporting purposes.

  Examples

    In this example the Google AdWords account is set to spend in GBP (£), but we need to report and bill in USD ($). We use the Google Finance function to get the current exchange rate. Then we simply multiply this exchange rate by the spend in GBP to get the equivalent in USD.

  3. Sparklines

  Function Used

  =SPARKLINE()

  Syntax

SPARKLINE(data, [options])

data – The range or array containing the data to plot.

options – [ OPTIONAL ] – A range or array of optional settings and associated values used to customize the chart.

But Why?

  Sometimes you want a small simple graph or bar chart, but don’t want to fuss with creating an actual graph in Google Sheets.

  Example

  Sheets also does not have some of the conditional formatting that Excel does, so this is a workaround if you want to show bar coloring.

      4. Import From Other Sheets

  Function Used

  =IMPORTRANGE()

  Syntax

IMPORTRANGE(spreadsheet_key, range_string)

spreadsheet_key – The URL of the spreadsheet from where data will be imported.

The value for spreadsheet_key must either be enclosed in quotation marks or be a reference to a cell containing the URL of a spreadsheet.

range_string – A string, of the format “[sheet_name!]range” (e.g. “Sheet1!A2:B6” or “A2:B6”) specifying the range to import.

The sheet_name component of range_string is optional; by default IMPORTRANGE will import from the given range of the first sheet.

The value for range_string must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.

But Why?

  If you’re working with multiple workbooks in Google Sheets, sometimes it is useful to have a sheet automatically import over to the other workbook.

  5. Check for 404s

  Function Used

  =HTTPResponse()

  We need a script to enable this function, see below for script details.

  But Why?

  If you need to check your landing pages or other URLs for 404s or other errors, this is a simple way to do it. While there are various scripts out there that do this, this allows you to check any URL, as well as other platform landing pages.

  Example

  For this function to work, you’ll need to implement the script below. To add a script to a Google Sheets file, navigate to Tools > Script editor in the top menu.

    Once you get the script set up, you can go ahead and use the formula. At Hanapin we like to pair this with Supermetrics to automatically insert all URLs in an account to check.

  Pair that with query functions (my favorite Google Sheet function), and you’ll get a nice report detailing where you’ve got landing pages with errors.

    6. Combine Data from Multiple Sheets

  Function Used

  =QUERY()

  But Why?

  If you need your data to be easily pivot-able, you’ll need it to be on one sheet. You might have data on separate sheets due to Supermetrics reports (different platforms).  At Hanapin, we report client monthly spends on a Google Sheet document. Each month gets its own sheet, but we need to be able to pivot and combine all the months together. The example below shows how we use a query function to do that.

  Example

    Simply wrap curly brackets { } around all of the sheets, each separated by a semicolon “ ; “.  Then proceed with your query as usual – making sure to include the criteria “Where Col1 is not Null” to leave out any empty rows.

  SUMMARY

  If you find yourself using Google Sheets frequently in your daily PPC reporting or analysis, it might be worthwhile to try out some of the above functions. I have found that learning and implementing these has helped me step my Sheets game up, even if it’s just for internal reporting.

from RSSMix.com Mix ID 8217493 https://www.ppchero.com/6-google-sheets-functions-useful-for-ppc-reporting-and-analysis/

[racheltgibsau]

6 Things You Didn’t Know You Could Do In Google Sheets

Google Sheets is a versatile tool for PPC marketers. Whether you’re using it build reports for your clients, analyze data, or other miscellaneous PPC tasks, getting familiar with some of the lesser known functions can be beneficial.

  Here are the features we’ll cover in this post:

Import Sitemaps

Currency Conversions

Sparklines

Import from other workbooks

Check for 404s and other errors

Combine data from multiple sheets

1. Import Sitemaps

  Function Used

  =IMPORTHTML() or IMPORTXML()

  Syntax

IMPORTHTML(url, query, index)

url – The URL of the page to examine, including protocol (e.g. http://).

The value for url must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.

query – Either “list” or “table” depending on what type of structure contains the desired data.

index – The index, starting at 1, which identifies which table or list as defined in the HTML source should be returned.

The indices for lists and tables are maintained separately, so there may be both a list and a table with index 1 if both types of elements exist on the HTML page.

But Why?

  This function is useful if you’re building out new campaigns for an account.

  Examples

  For this example, the sitemap is a .xml page, so we’ll need to use the =IMPORTXML function.

    The import function returns the list of URLs on the left, then we use the SPLIT function to break apart the URLs into categories for our campaign structure plan.

    2. Currency Conversions

  Function Used

  =GoogleFinance()

  But Why?

  If you have a client that spends in an international currency, but needs to be converted to USD for billing or reporting purposes.

  Examples

    In this example the Google AdWords account is set to spend in GBP (£), but we need to report and bill in USD ($). We use the Google Finance function to get the current exchange rate. Then we simply multiply this exchange rate by the spend in GBP to get the equivalent in USD.

  3. Sparklines

  Function Used

  =SPARKLINE()

  Syntax

SPARKLINE(data, [options])

data – The range or array containing the data to plot.

options – [ OPTIONAL ] – A range or array of optional settings and associated values used to customize the chart.

But Why?

  Sometimes you want a small simple graph or bar chart, but don’t want to fuss with creating an actual graph in Google Sheets.

  Example

  Sheets also does not have some of the conditional formatting that Excel does, so this is a workaround if you want to show bar coloring.

      4. Import From Other Sheets

  Function Used

  =IMPORTRANGE()

  Syntax

IMPORTRANGE(spreadsheet_key, range_string)

spreadsheet_key – The URL of the spreadsheet from where data will be imported.

The value for spreadsheet_key must either be enclosed in quotation marks or be a reference to a cell containing the URL of a spreadsheet.

range_string – A string, of the format “[sheet_name!]range” (e.g. “Sheet1!A2:B6” or “A2:B6”) specifying the range to import.

The sheet_name component of range_string is optional; by default IMPORTRANGE will import from the given range of the first sheet.

The value for range_string must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.

But Why?

  If you’re working with multiple workbooks in Google Sheets, sometimes it is useful to have a sheet automatically import over to the other workbook.

  5. Check for 404s

  Function Used

  =HTTPResponse()

  We need a script to enable this function, see below for script details.

  But Why?

  If you need to check your landing pages or other URLs for 404s or other errors, this is a simple way to do it. While there are various scripts out there that do this, this allows you to check any URL, as well as other platform landing pages.

  Example

  For this function to work, you’ll need to implement the script below. To add a script to a Google Sheets file, navigate to Tools > Script editor in the top menu.

    Once you get the script set up, you can go ahead and use the formula. At Hanapin we like to pair this with Supermetrics to automatically insert all URLs in an account to check.

  Pair that with query functions (my favorite Google Sheet function), and you’ll get a nice report detailing where you’ve got landing pages with errors.

    6. Combine Data from Multiple Sheets

  Function Used

  =QUERY()

  But Why?

  If you need your data to be easily pivot-able, you’ll need it to be on one sheet. You might have data on separate sheets due to Supermetrics reports (different platforms).  At Hanapin, we report client monthly spends on a Google Sheet document. Each month gets its own sheet, but we need to be able to pivot and combine all the months together. The example below shows how we use a query function to do that.

  Example

    Simply wrap curly brackets { } around all of the sheets, each separated by a semicolon “ ; “.  Then proceed with your query as usual – making sure to include the criteria “Where Col1 is not Null” to leave out any empty rows.

  SUMMARY

  If you find yourself using Google Sheets frequently in your daily PPC reporting or analysis, it might be worthwhile to try out some of the above functions. I have found that learning and implementing these has helped me step my Sheets game up, even if it’s just for internal reporting.

from RSSMix.com Mix ID 8217493 https://www.ppchero.com/6-google-sheets-functions-useful-for-ppc-reporting-and-analysis/