Mungeol Heo: 2023-01-01

Regexp

Find the shortest matches between two strings

(from ((?!from).)*? on)

Extract table names from SQL

(from|join)\s+(\S+)

Google Sheets

Add missing dates to the list of dates with values

=ARRAYFORMULA({TO_DATE(ROW( INDIRECT("A"&DATEVALUE(MIN(A:A))&":B"&DATEVALUE(MAX(A:A))))), IFERROR(VLOOKUP(TO_DATE(ROW( INDIRECT("A"&DATEVALUE(MIN(A:A))&":B"&DATEVALUE(MAX(A:A))))), A:B, 2, 0))})

Automatic timestamp when a cell on the same row gets updated

function onEdit(e) {

var ss = SpreadsheetApp.getActiveSheet();

var r = ss.getActiveCell();

//1.Change 'Sheet1' to be matching your sheet name

if (r.getColumn() > 1 && ss.getName()=='Sheet1') { // 2. If Edit is done in any column after Column (A) And sheet name is Sheet1 then:

var celladdress ='A'+ r.getRowIndex()

ss.getRange(celladdress).setValue(new Date()).setNumberFormat("yyyy-MM-dd hh:mm:ss");

}

};

Automatic sorting when a cell gets updated

function onEdit(event){

var sheet = event.source.getActiveSheet();

var editedCell = sheet.getActiveCell();

var columnToSortBy = 4;

var tableRange = "B3:E9";

if(editedCell.getColumn() == columnToSortBy){

var range = sheet.getRange(tableRange);

range.sort( { column : columnToSortBy } );

}

Python

Connect to BigQuery without credential files like JSON

from pydata_google_auth import get_user_credentials

scopes = ["https://www.googleapis.com/auth/bigquery"]

credentials = get_user_credentials(scopes)

// notebooks running on servers or cloud resources

credentials = get_user_credentials(scopes, use_local_webserver=False)

Increase the density of x-ticks

test = sns.distplot(df.BattlePower_diff, 100, kde=False)

plt.xticks(rotation=90)

test.xaxis.set_major_locator(plt.MaxNLocator(200))

MongoDB

filter between two dates with time zone

{

$or:[

{createdAt: {$gte: ISODate("2023-07-26T00:00:00.000+09:00"), $lt: ISODate("2023-07-27T00:00:00.000+09:00")}},

{updatedAt: {$gte: ISODate("2023-07-26T00:00:00.000+09:00"), $lt: ISODate("2023-07-27T00:00:00.000+09:00")}},

]

}

{