256 Kilobytes

Scripting and Automation

Type All Category Scripting and AutomationInformation and discussion of scripting of various types. Tag All

Profile Photo - Some Guy
Profile Photo - August R. Garcia

Last ReplyHere's an example that I used recently:=if(I2="-", "NULL", VLOOKUP(I2, {E$2:E41,B$2:B41}, 2,0))In Google Sheets, curly braces... August R. Garcia,

Here's an example th...

Sun, 02 Dec 2018 03:47:34 -08003 years ago1
🗨 1
🐏 0
👁 382
Profile Photo - Some Guy

How to access conditional formatting in Google Sheets?

Answers in Spreadsheets | By Some Guy

Published

Profile Photo - August R. Garcia

Last Reply1. Highlight the cells that you want to format2. Right Click --> Conditional Formatting August R. Garcia,

1. Highlight the cells th...

Sun, 02 Dec 2018 03:47:29 -08003 years ago1
🗨 1
🐏 0
👁 379
Profile Photo - Some Guy

Converting URLs to domains and vice-versa?

Answers in Spreadsheets | By Some Guy

Published | Last Update

Profile Photo - August R. Garcia

Last ReplySolutions Using Google Sheets Extract domain from subdomain Example input: howtogoaround.wordpress.com =RIGHT(REGEXEXTRACT(D25, "\..*... August R. Garcia,

Solutions Using Google Sh...

Sun, 02 Dec 2018 03:47:25 -08003 years ago1
🗨 1
🐏 0
👁 468
Profile Photo - Some Guy
Profile Photo - August R. Garcia

Last ReplyHave also run into this issue over the last few years. You'd think there'd be an easy way to do this. Messed around with a few options below... August R. Garcia,

Have also run into this i...

Sun, 02 Dec 2018 03:47:09 -08003 years ago1
🗨 1
🐏 0
👁 712
Profile Photo - Some Guy

[Google Sheets] How to get the first element of an array?

Answers in Spreadsheets | By Some Guy

Published

Profile Photo - August R. Garcia

Last Reply// Get first element of an array=INDEX(SPLIT(B2, " | ", FALSE);1;1) August R. Garcia,

// Get first element of a...

Sun, 02 Dec 2018 03:47:04 -08003 years ago1
🗨 1
🐏 0
👁 402
Profile Photo - Some Guy

What do curly braces do in Google Sheets?

Answers in Spreadsheets | By Some Guy

Published

Profile Photo - August R. Garcia

Last ReplyCurly braces are used to combine ranges into arrays. [] August R. Garcia,

Curly braces are used to...

Sun, 02 Dec 2018 03:46:59 -08003 years ago1
🗨 1
🐏 0
👁 420
Profile Photo - Some Guy

How can I monitor my GPU usage on Ubuntu?

Answers in Scripting and Automation | By Some Guy

Published

Profile Photo - August R. Garcia

Last ReplyIf there is no usable/relevant GUI, this can also be done from the terminal with a looping BASH script like this: # Monitor GPU stats and usage... August R. Garcia,

If there is no usable/rel...

Sun, 02 Dec 2018 03:46:56 -08003 years ago1
🗨 1
🐏 0
👁 445
Profile Photo - Some Guy

I downloaded a bunch of stock photos for testing. Is there a quick way to bulk rename in Bash (or something similar)?

Forum in Web Scraping, Data Analysis | By Some Guy

Published | Last Update

Profile Photo - August R. Garcia

Last ReplyA script like run from the terminal or a Bash script works for this type of file renaming: INDEX=1; for i in *.jpg; do mv $i ${INDEX}_cover.jpg;... August R. Garcia,

A script like run from th...

Sun, 02 Dec 2018 03:46:44 -08003 years ago1
🗨 1
🐏 0
👁 607
Profile Photo - Some Guy

I am new to Google Sheets. What functions should I know about?

Answers in Spreadsheets | By Some Guy

Published

Profile Photo - August R. Garcia

Last ReplyHere are examples of a few very commonly used formulas:Replace substrings in spreadsheets: =SUBSTITUTE("Today is Monday", "Monday"... August R. Garcia,

Here are examples of a fe...

Sun, 02 Dec 2018 03:46:43 -08003 years ago1
🗨 1
🐏 0
👁 378
Profile Photo - Some Guy
Profile Photo - August R. Garcia

Last ReplyTry this function:#Change 1.3k to 1300, etc.=((VALUE(substitute(A1, "k", "")))*1000)=((VALUE(substitute(I201, "k", &quot... August R. Garcia,

Try this function:#Change...

Sun, 02 Dec 2018 03:46:38 -08003 years ago1
🗨 1
🐏 0
👁 1,116
Profile Photo - Some Guy

[Google Sheets] Convert cell contents to (basic) HTML?

Answers in Spreadsheets | By Some Guy

Published

Profile Photo - August R. Garcia

Last ReplyThis seems to work:// 1. Wrap entire cell in a <p> tag, then use REGEXREPLACE to address newlines// 2. Replace newlines with the end of the prev... August R. Garcia,

This seems to work:// 1....

Sun, 02 Dec 2018 03:46:37 -08003 years ago1
🗨 1
🐏 0
👁 436
Profile Photo - Some Guy

Is there a good way to split/divide cells in Google Sheets?

Answers in Spreadsheets | By Some Guy

Published

Profile Photo - August R. Garcia

Last ReplyThere is a built-in split function here:https://support.google.com/docs/answer/3094136?hl=enFor your use case, this function should work:=SPLIT(A1, &q... August R. Garcia,

There is a built-in split...

Sun, 02 Dec 2018 03:46:34 -08003 years ago1
🗨 1
🐏 0
👁 351
Profile Photo - Some Guy
Profile Photo - August R. Garcia

Last ReplyOne potential approach is to use Levenshtein Distance. August R. Garcia,

One potential approach is...

Sun, 02 Dec 2018 03:46:24 -08003 years ago1
🗨 1
🐏 0
👁 388
Profile Photo - Some Guy

Renaming files from BASH?

Answers in Scripting and Automation | By Some Guy

Published

Profile Photo - August R. Garcia

Last Reply# Convert clocks subfolders from png to jpgconvert */*.png */*.jpgfor f in $(find */*.png); do echo $f to $f--.jpg convert $f $RANDOM-RANDOM-.jpg r... August R. Garcia,

# Convert clocks subfolde...

Sun, 02 Dec 2018 03:46:22 -08003 years ago1
🗨 1
🐏 0
👁 455
Profile Photo - Some Guy
Profile Photo - August R. Garcia

Last ReplyTo get a random number in a range of 2k to 3k rounded to the nearest interval of 100: =round(randbetween(2000, 3000)*0.01)*100 August R. Garcia,

To get a random number in...

Sun, 02 Dec 2018 03:46:19 -08003 years ago1
🗨 1
🐏 0
👁 385
Profile Photo - Some Guy

Generate insert statements from Google Sheets?

Forum in Spreadsheets | By Some Guy

Published | Last Update

Profile Photo - August R. Garcia

Last ReplySomething along these lines should work: ="INSERT INTO " & A2 &" ("&join(", ", $B$1:$H$1)&")" & " VALUES " & "("... August R. Garcia,

Something along these lin...

Sun, 02 Dec 2018 03:46:07 -08003 years ago1
🗨 1
🐏 0
👁 480
Profile Photo - Some Guy
Profile Photo - Some Guy

Specifically, is it possible to highligh...

MoreI have a spreadsheet full of client orders. I'd like to be able to highlight rows if the deadline for that order is within three days from the cu...
🗨 0
🐏 0
👁 381
Profile Photo - Some Guy

[Google Sheets] What are pivot tables?

Answers in Spreadsheets | By Some Guy

Published

Profile Photo - Some Guy

MoreEveryone talks about how pivot tables and VLOOKUP are the epitome of quality Google Sheets/spreadsheet features.But what exactly *is* a pivot table? W...
🗨 0
🐏 0
👁 412
Profile Photo - Some Guy

[Google Sheets] SUMIF cell is an even number?

Answers in Spreadsheets | By Some Guy

Published | Last Update

Profile Photo - Some Guy

MoreAfter reading the documentation (below), I'm still trying to figure out how to sum only if a number is even/odd. Is this possible? Or is it only p...
🗨 0
🐏 0
👁 416
Profile Photo - Some Guy

Excerpts from the full Google Sheets documentation

Forum in Spreadsheets | By Some Guy

Published

Profile Photo - Some Guy

MoreHere is a fun fact -- you can read the entire Google Sheets documentation:https://support.google.com/docs/table/25273?hl=enWent through the full list...
🗨 0
🐏 0
👁 504
Profile Photo - Some Guy

How to get a random character (alphanumeric plus basic symbols) in Google Sheets?

Answers in Spreadsheets | By Some Guy

Published | Last Update

Profile Photo - Some Guy

MoreSpecifically, A-Z, a-z, 0-9, and the basic punctuation. Can this be done? You can use this to get a random letter: =char(randbetween(65, 65+2...
🗨 0
🐏 0
👁 387
Profile Photo - Some Guy
Profile Photo - Some Guy

MoreMost specifically, what formula can be used to escape a single quote in Google Sheets?
🗨 0
🐏 0
👁 456
Profile Photo - Some Guy
Profile Photo - Some Guy

MoreFor example, is it possible to take in a table name, a range of fields to insert to, and a range of values and to then:1. Escape the values2. Convert...
🗨 0
🐏 0
👁 497