# Most recents (21)

Today I want to show you a quick way to add an ID column to your #GoogleSheets tables, using the SEQUENCE and COUNTA functions.

It's a quick way to see how many records you have in your table.

Let's go! 👇

(1/9)
We need a dataset for this example so let's grab an education dataset...

Here are some student test scores along with some variables that affect those scores.

(2/9)
Insert a new blank column on the left of this dataset. Add the title "ID" in cell A1 and this formula in cell A2:

=SEQUENCE(COUNTA(B2:B))

(3/9)

You can then share these web pages with the world, so people can see your Sheet as a distinct, lightweight webpage.

It's a good idea if you want to show the Sheet to a very large audience.

(1/3)
Here's an example:

(2/3)
And here's how to do it:

1️⃣ Go to File > Share > Publish to web.

2️⃣ On the popup, select the Sheet (or Sheets) you want to publish and hit Publish.

3️⃣ Share the URL wherever you want!

There's even an option to embed the Sheet within an existing web page. 😃

(3/3)
The QUERY function in #GoogleSheets is pretty much the most powerful function in the spreadsheet world.

It operates on your data and has the functionality of many other functions, like sorting, aggregation, filtering, etc.

It's like a pivot table in function form.

(1/4)
It's a tricky function to learn because it's so different from regular functions. You use query language to write a statement that operates on your data.

Here's an example:

=QUERY(A1:E100,"select B, D, E where D = 'Europe'",1)

(2/4)
The function operates on data in the range A1:E100.

It returns only columns B, D and, E from that data in columns A to E, and applies a filter so that only rows where column D is equal to "Europe" are in the results.

Cool, right?

(3/4)

To get started, you’ll need:

💸 Campaign budgets
📅 Campaign end dates

Here’s what you need to do...🧵👇
Using the Google Ads Add-on, create a report that pulls cost data per campaign, per account, for the current month.

Set it to automatically refresh daily.
Columns you need from the Add-on report:

- Customer ID
- Account
- Campaign
- Campaign ID
- Cost.

You'll need to manually add new columns at the end:

- Budget
- Budget Type
- Today’s Date
- End Date
- Total Campaign Budget
- Action
I've updated the #tether attestation #googlesheets analysis. A few changes including fixing a glaring error 😳 #commercialpaper analysis h/t to @accountantInc catching the mistake. Document consolidates all reported quarters to date >>> bit.ly/3Kzkjz2
The #tether #commercialpaper section has been expanded/updated to show not just the net changes but the new issues/rollovers etc....
The purpose of the #commercialpaper analysis was to highlight that the reported \$6.4B reduction in CP was only half the story....and it kinda shits me that the numbers are just trumpeted by most news as is...
Have been working through \$CEL #celsiusnetwork's \$750m Series B round. Will share #googlesheets file tomorrow. Here's a preview.
3 Dec 2021 #Celsius resolved to issue up-to 36,930 Series B shares at a price of US\$20,469 per share. As at 2 Feb 2022 total of 32,182 Series B shares had been issued. I'm presuming #bnktothefuture will have closed out the balance.
The #Celsius Cap table (as at 2 Feb) with pre/post money for each issue.

A navigational aid for the variety of projects featured in current @gitcoin #GR13:

A curated list of projects that are helping blockchains become an integral part of the world by empowering people with knowledge and education. 🤓🔖💪

1/N
🔎 @LearnWeb3DAO / @haardikkk
(education system)

For open-minded individuals, interested in everything they need to know in order to become a #Web3 native.

A very rich resource with a fast-growing userbase of Web3 students.

👉gitcoin.co/grants/5066/le…

2/N #GR13
🔎 Odyssey DAO / @odyssey_dao
(Educational DAO / resource)

For #Web3 newcomers interested in high-quality learning material with a variety of learning paths.

👉gitcoin.co/grants/5016/od…

3/N #GR13
❤️ Medio like y compartimos los 10 tips definitivos de las herramientas de Google.
Usá la búsqueda avanzada de @gmail para borrar archivos pesados que ya no necesites. 📪

🔎 En el cuadro de búsqueda, tipeá "has:attachment larger:10M". Si es necesario, podés sustituir el 10 por un número de peso más grande. 😎
En @YouTube podés compartir un momento específico de un video, siguiendo estos pasos:

👉 Desplazate hasta el momento que desees.
👉 Clic derecho y pulsá en "Copiar la URL del video a partir del minuto actual".
👉 Quien abra el link verá el video a partir de ese momento.
💜 Turn your GoogleSheets into a web scraping machine for SEO 💜

There is a lot that you can accomplish w/ GSheets. Let's start the new year with some super cool productivity hacks w/ the IMPORTXML function of it💡

It's a 🧵
1. Extract Title Tags

This IMPORTXML Formula extract the title tag from the URL that is on cell "A1"
2. Extract Meta Description

This IMPORTXML Formula extract the meta description tag from the URL that is on cell "A1"
A great way to be more efficient in #GoogleSheets is to learn basic shortcuts for selecting data.

That's what I'm going to show you today - how to select data for formulas without your hands leaving the keyboard. ⌨️

It's so much quicker than grabbing the mouse! 🖱️
I remember how awkward it felt when I first learned these shortcuts, and how it was initially slower than just using the mouse to highlight the data.

But after a few days, it was significantly quicker than using the mouse. And it will be for you too.

Here we go ⬇️
1️⃣

Move quickly to the last non-blank cell in a row or column:

PC/Chromebook: Ctrl + ⬆⬇⬅➡
Mac: ⌘ + ⬆⬇⬅➡
The OFFSET function is not a function you need particularly often, but it's worth knowing about because it allows you to move ranges around very easily.

The OFFSET function returns a reference to a range that is offset from a starting point in a worksheet.

For example, in the table shown in this image, imagine you want to align these columns at the top of the column.
The OFFSET function can do this with a single formula for each column, which is quicker and easier than copy-pasting or creating complex nested formulas.

The OFFSET formula in this example is:

=offset( B2:B6, 1, 0, count(B2:B6) )
Have you come across floating bar (or column) charts before?

They're a useful way to show data when you want to compare ranges or high and low values.

For example, you can use floating bar charts with salary data, weather data, stock prices, blood pressure readings, etc.
To create a floating bar (or column) chart in #GoogleSheets, you’ll need two series in your dataset, e.g. a set of low values and a set of high values, like this salary range dataset.

1. Highlight the data and Insert > Chart
2. In Setup, choose a Bar Chart (horizontal) or Column Chart (vertical)
3. Still in the chart setup menu, set the Stacking to be “Standard”
4. Go to the Customize menu

(continued in next tweet)
A tool to build any #GoogleDataStudio formula. Where possible it will suggest formulas for #GoogleSheets, #BigQuery, and #AppsScript.

Is it too ambitious? 🤔

🧪Work in progress. 👇
✅ All aggregation functions are working

⏭️ Next - arithmetic functions.
🧪 progress with arithmetic functions......
¿Sabías que Google tiene disponible un API que te permite consultar las SERP para un país e idioma específico y además, gratis? ¿Sabes que gracias a esta funcionalidad podrías crearte un rank tracker super sencillo para el Top 10 de las SERP? #SEO Te explico a continuación 👉
En primer lugar, tendrás que crear un motor de búsqueda personalizado de Google y puedes hacerlo desde aquí: programmablesearchengine.google.com/cse/all Esta herramienta tienen infinitas posibilidades ya que puedes crear desde un buscador de contenidos para tu web como para otroas webs. Sigamos… 👉
Añadimos nuestra primer búsqueda programable, incorporamos un dominio – no es importante para nuestro objetivo –, elegimos el idioma – tampoco es relevante –, le damos nombre y pulsamos en crear. 👉

Here's the output👇
Click on the button that says “Open in Colab"
github.com/ranksense/Twit…
Why learn this? 🧐

Skip the tedious work of updating 100s or 1,000s of meta descriptions and let this script do it for you! Speed up your processing time from 30 sec/URL to 3 sec/URL 🤯
For the next 30 days, we're going to be sharing quick simple videos to help you power up your Google Sheets knowledge. Along with each tip, we'll share an #SEO and #PPC use case to help identify the opportunities to use these formulas.
Day 1/30 - Merge 2 or more cells together using =CONCATENATE
1. Type =CONCATENATE
2. Select the first cell you'd like to merge
3. Select the second cell you'd like to merge
4. Hit enter!
Ideal for adding http:// or https:// to a list of URLs or merging domains with subfolders.
Day 2 of 30 #GoogleSheets for #SEO - Text to columns

Need to split a URL into domain, subfolder and slug? This is quick and simple with just a few clicks.

1. Select all cells in column A
2. Click data > Split text to columns
3. Change separator to Custom and set as /

Starting with this humdinger

Type “sheet.new” or "sheets.new" into your browser to instantly create a new Google Sheet

(1/n)
From inside a folder of @GoogleDrive press

Shift + S

To create a new Google Sheet in that folder
(2/n)
Quickly freeze panes in your #GoogleSheet by hovering over the border of the sheet (just under the column letters) until you see the hand icon.

Drag down as many rows as you want to freeze.

Works for columns too!
(3/n)
Hey all, I’m doing a weekly twitter standup, mostly for myself. But if you or anyone else would like to join, feel free to ping me. I’m thinking a weekly basis won’t be too time consuming. Pick a day of the week, and I’ll @ you to remind you. #weeklytwitterstandup
Did last week:
✅ finally built script to track usage of my servers. It’s a python script uploaded to AWS lambda. The script pings my half-dozen servers for data usage and writes the data onto 1) a MySQL server hosted on AWS RDS, 2) BigQuery database, and 3) google sheets
✅ built a dashboard on server usage data using Tableau public. I now have the dashboard scrolling on my Life Flips app.
Es hora de darle 😍 a #DataStudio. Os presento un modo de incrustar informes GDS inteligentes 🤖en sitios web:

🔄 Se actualizan sin recargar ‼️en navegador
⏯️ Transición CSS suave entre páginas con precarga
📺 Ideal modo kiosco
🔲 Responsive

⬇️⬇️⬇️
Aunque ya mencioné la posibilidad en mi taller #GDSatope de #GEGValencia2019, lo que en aquel momento mostré fugazmente era solo un pequeño JS de 🔄 automática sin paso de 📄.

Ahora podemos crear displays informativos GDS desatendidos autoactualizables 📺🔄👁️❌🤚.

⬇️⬇️⬇️
Aquí la "chicha" 🔎

Código👇
codepen.io/pfelipm/pen/EB…

Ejemplo👇