Linux HowTo: List of Subfolder names and file links in google sheets – script

I’m trying to get a Google Sheet list of file links in folders and subfolders which I’m keeping in Google Drive. It should look something like this:

I was googling and reading forums for a quite a time now but I managed to find only a few script examples and not all of them are working. I found one script which is not too bad as it brings a list of files with links in one folder. but I would like to get a list of files with links in subfolders. Ideally all folder links and item names would be listed in one cell with a separator “|”. one of the best script examples I found so far is:

``````function listFilesInFolder(folderName) {

sheet.appendRow(["Name", "File-Id"]);

//change the folder ID below to reflect your folder's ID (look in the            URL when you're in your folder)
var folder = DriveApp.getFolderById("0ByQ9Zp2ge-pzSjhKdHVWWk0wNnc");
var contents = folder.getFiles();

var cnt = 0;
var file;

while (contents.hasNext()) {
var file = contents.next();
cnt++;

data = [
file.getName(),
file.getId(),
];

sheet.appendRow(data);
};
};
``````

and it brings results in a sheet

It would be much appreciated if anyone could help to get a better version of this code.

p.s.There’s also a few bigger scripts I will need in a future which I’m willing to pay for. please message me if you’re interested.

``````// replace Guitar_Score below with the folder for which you want a listing with all subfolders
function listFolderContents() {
var foldername = 'Guitar_Score';
// file with this name will be saved in your goolge files
var folderlisting = 'listing of folder ' + foldername;
var sheet = ss.getActiveSheet();
var folders = DriveApp.getFoldersByName(foldername)
var folder = folders.next();
//listFilesInFolder(folder, sheet);
traverseFolder(folder, sheet);
};

function traverseFolder(folder, sheet) {
listFilesInFolder(folder, sheet);
var subFolders = folder.getFolders();
while (subFolders.hasNext()) {
traverseFolder(subFolders.next(), sheet);
}
}

function listFilesInFolder(folder, sheet) {
var foldername = folder.getName();
var contents = folder.getFiles();
sheet.appendRow( ['...', foldername] );
while(contents.hasNext()) {
var file = contents.next();
var name = file.getName();
}
}
``````

Math Genius: Why do you have to refer back to the equation before the transformation?

E.g Find the rule for the image of the graph y=f(x) under the translation:

f(x) =1/x under a translation 4 units in the positive direction of the x-axis and 1 unit in the negative direction of the y-axis.

Let (x’,y’) be the image of (x, y)
y=f(x)=1/x

x’= x+2
y’= y-1

Therefore, x = x’-2
y = y’+1

The new equation of the image is:
y’+1 = f(x’-2)

y’+1 = 1/(x’-2)

g(x) = 1/(x’-2)-1

Now, my question is why do you have to refer back to the original equation,’in terms’ of the new coordinates. Why don’t u just put back in the new coordinates, the new equation without referring back to the old equation (y=f(x)=1/x).

The first reason i could think of is that with the new coordinates if you just put them back in, for example x’=x+2, it wouldn’t work because it is in x’ and there is no x’ in the rule. Is there any other reasons?

If you move horizontally 2 to the right (+) direction, why won’t the equation be x+2? i know that x+2 is wrong because the solution would be -2. Is it x-2 because of the fact that it is referred back to the original equation?

Code Bug Fix: SQL’s INNER JOIN resulting in duplicate entries even though there are no duplicate column values in all 3 tables

With regards to the first question on the URL: https://www.w3resource.com/sql-exercises/movie-database-exercise/subqueries-exercises-on-movie-database.php#SQLEDITOR, “Write a query in SQL to list all the information of the actors who played a role in the movie ‘Annie Hall’.”, can anyone explain why the query below results in a duplicate entry even though there are no duplicate id column values in each of the 3 tables: `actor`, `movie_cast`, and `movie`?

SQL query:

``````SELECT actor.act_id, actor.act_fname, actor.act_lname, actor.act_gender
FROM actor
INNER JOIN movie_cast ON actor.act_id = movie_cast.act_id
INNER JOIN movie ON movie_cast.mov_id = movie.mov_id
WHERE movie.mov_title = 'Annie Hall'
``````

The output of the above query on w3resource is:

``````act_id  act_fname   act_lname   act_gender
111 Woody   Allen   M
111 Woody   Allen   M
``````

Samples of the 3 tables:

actor:

``````act_id |      act_fname       |      act_lname       | act_gender
--------+----------------------+----------------------+------------
101 | James                | Stewart              | M
102 | Deborah              | Kerr                 | F
103 | Peter                | OToole               | M
104 | Robert               | De Niro              | M
105 | F. Murray            | Abraham              | M
106 | Harrison             | Ford                 | M
107 | Nicole               | Kidman               | F
108 | Stephen              | Baldwin              | M
109 | Jack                 | Nicholson            | M
110 | Mark                 | Wahlberg             | M
111 | Woody                | Allen                | M
112 | Claire               | Danes                | F
113 | Tim                  | Robbins              | M
114 | Kevin                | Spacey               | M
115 | Kate                 | Winslet              | F
116 | Robin                | Williams             | M
117 | Jon                  | Voight               | M
118 | Ewan                 | McGregor             | M
119 | Christian            | Bale                 | M
120 | Maggie               | Gyllenhaal           | F
121 | Dev                  | Patel                | M
122 | Sigourney            | Weaver               | F
123 | David                | Aston                | M
124 | Ali                  | Astin                | F
``````

movie_cast:

``````act_id | mov_id |              role
--------+--------+--------------------------------
101 |    901 | John Scottie Ferguson
102 |    902 | Miss Giddens
103 |    903 | T.E. Lawrence
104 |    904 | Michael
105 |    905 | Antonio Salieri
106 |    906 | Rick Deckard
107 |    907 | Alice Harford
108 |    908 | McManus
110 |    910 | Eddie Adams
111 |    911 | Alvy Singer
112 |    912 | San
113 |    913 | Andy Dufresne
114 |    914 | Lester Burnham
115 |    915 | Rose DeWitt Bukater
116 |    916 | Sean Maguire
117 |    917 | Ed
118 |    918 | Renton
120 |    920 | Elizabeth Darko
121 |    921 | Older Jamal
122 |    922 | Ripley
114 |    923 | Bobby Darin
109 |    909 | J.J. Gittes
119 |    919 | Alfred Borden
``````

movie:

`````` mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
--------+----------------------------------------------------+----------+----------+-----------------+------------+-----------------
901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
914 | American Beauty                                    |     1999 |      122 | English         |            | UK
915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK

``````

Your `movie_cast` table has duplicate entry for actor `Woody Allen`. You can check with below query. You can see the output in image below, so you are getting two records in your query. If you want to eliminate duplicate entry then try `DISTINCT` in your query.

``````SELECT *
FROM movie_cast
WHERE movie_cast.act_id = 111
``````

Query with distinct records.

``````SELECT DISTINCT actor.act_id, actor.act_fname, actor.act_lname, actor.act_gender
FROM actor
INNER JOIN movie_cast ON actor.act_id = movie_cast.act_id
INNER JOIN movie ON movie_cast.mov_id = movie.mov_id
WHERE movie.mov_title = 'Annie Hall'
``````

OP’s question from comment.

Why does the proposed solution not result in any duplicates though? Proposed Solution: SELECT * FROM actor WHERE act_id IN( SELECT act_id FROM movie_cast WHERE mov_id IN ( SELECT mov_id FROM movie WHERE mov_title=’Annie Hall’ ));

Because `inner query` will have list which will consist of duplicate `mov_id`. But `outer query` will only check once for each record whether `inner query` result consists `current` row’s `mov_id` or not.

Tagged : /

Server Bug Fix: Locked out of Amzon EC2 Ubuntu instance

i am trying to login to my amazon instance using putty. I had logged in a few hours back, but now when i am trying to log back in, it says authentication using the key has failed. Anyway i can fix this?

Now putty is not able to connect to the server. I tried with the public dns and elastic ip. neither one was able to connect. But the server is still running.

First, I would try manually rebooting the instance from the AWS Management console. If that doesn’t do it, you’ll need to shut down the instance (but save the EBS volume first by taking a snapshot if you have it set to delete on instance termination). You can then start a new instance and attach the volume to it recover your data.

I wrote this article to help start tracking down these types of problems. It also has useful tips on what information you should provide when you ask for help:

Solving: “I can’t connect to my server on Amazon EC2”
http://alestic.com/2009/08/ec2-connectivity

I wrote this article to help explain how to look at and fix files on the root disk of an instance when you can’t connect to it:

Fixing Files on the Root EBS Volume of an EC2 Instance
http://alestic.com/2011/02/ec2-fix-ebs-root

Tagged : /

Math Genius: Bounding the difference between moments of random variables

Suppose $$X$$ and $$Y$$ are two random variables whose distributions are close in some sense. I am interested in bounding the diffrence between the $$k$$th moments of $$X$$ and $$Y$$ (under certain conditions). A few possible approaches I can think of are:

1. Bounding the Wasserstein distance between $$X$$ and $$Y$$ and then use its equivalent definition to bound $$|mathbb{E}f(X)-mathbb{E}f(Y)|$$ for all 1-Lipschitz functions $$f$$. The Problem is that power functions are not 1-Lipschitz.
2. Bounding the generating funcitons of $$X$$ and $$Y$$ and go from there.
3. Directly using definition.

I think this problem must have been considered. So to avoid reinventing the wheel, I’ll really appreciate if you could point me to the right directions and references (ideally, I also want to bound $$|mathbb{E}f(X)-mathbb{E}f(Y)|$$ for more complicated functions $$f$$ than just power functions). Thanks!

Tagged :

Server Bug Fix: OpenVPN and drop for IP address for as0t0 /1/2/3/4

From time to time, the server wih openvon has no as0t0/1/2/3/4 addresses, which is why I cannot connect to the VPN. Then I have to reboot instance and everything works
What’s the matter with this and how do you fix it so that i don’t have to manually reboot it from time to time?

Tagged : /

Linux HowTo: OSX duplicated and re-numbered my Drive in /Volumes/

In a 2011 Macbook Pro, I have replaced the SuperDrive with an SSD. Now, I have an OSX partition for the OS in the SSD, and most of my data is on another HDD, connected by symlinks (i.e. Documents, Downloads, Desktop, Dropbox, etc.). For the following, the name of the HDD volume is “NAME”.

When I just restarted my computer, I got an error from Dropbox saying that my folder could not be found. I navigated to “NAME” using Finder and everything seemed to be in order. In Disk Utility, everything appears to be correctly in order and mounted. I looked at my symlinks (in my user folder) and they all appear to be broken.

I looked in /Volumes/ and it appears that my HDD partition has been renamed to “NAME 1”, so that /Volumes/ contains both “NAME” and “NAME 1”. The system requires me to use sudo to ls in “NAME”, and it only appears to contain “sleepimage” (size of 8 GB, my normal amount of memory). I also noticed in Disk Utility that the Mount Point for “NAME” is given as “/Volumes/NAME 1”

I have no idea where this new “/Volumes/NAME” is physically, if it actually exists. It does not appear in Disk Utility. If I execute a “df” command, it does not appear in the listing. I am tempted to delete it in /Volumes/ and mv “NAME 1” to “NAME”

EDIT: Noticed this in the Console right before boot:

``````10-10-13 7:38:29.667 PM com.apple.mtmd[90]: low disk space thinning needed for volume NAME (/Volumes/NAME 1) with 8.2 <= 10.0 pct free space , 1 snapshot can be thinned

10-10-13 7:38:29.668 PM com.apple.mtmd[90]: attempting to thin because of low free space on NAME (/Volumes/NAME 1) by removing 2013-10-10 19:06:28 -0400, estimate 0 bytes to recover, 1 snapshot can be thinned
``````

And this just after boot:

``````10-10-13 7:41:45.723 PM com.apple.mtmd[90]: attempting to thin because of low free space on NAME (/Volumes/NAME 1) by removing 2013-10-10 19:34:46 -0400, estimate 0 bytes to recover, 1 snapshot can be thinned
``````

EDIT 2:

sudo rm -rf “NAME”

did the trick!

The new /Volumes/NAME is on the SSD. Mac OS created the directory when you logged in because the HDD hadn’t been mounted yet and some critical files were missing as a result. The HDD volume was missing because Mac OS normally waits to mount additional volumes until after you log in. To change this behavior you need to modify a system-wide preference. Run this command once from Terminal:

sudo defaults write /Library/Preferences/SystemConfiguration/autodiskmount AutomountDisksWithoutUserLogin -bool true

Put the /Volumes/NAME that the system created in the Trash and restart the system. The real /Volumes/NAME should be mounted immediately after booting and when you login your symlinks to the HDD will work as you expect.

Tagged : / / /

Code Bug Fix: Choice between groupby and filter with infinite streams in reactor

We are using spring webflux in our project. In our project we receive messages over websocket and then we choose a message processing pipleline based on type of the message. As it is with reactor there are multiple operators which can be used to accomplish a task and we are faced with choice of using either groupby or combination of filter and ConnectableFlux to achieve the same. Below is an elaboration of both choices

• Filter

We use Flux.publish() to broadcast the incoming stream then put multiple subscribers on the ConnectableFlux. Each subscriber first filters to check if it needs to process the stream further. After adding all the subscribers we call connect on ConnectableFlux. Here is the sample code
`````` public Mono<Void> handle(WebSocketSession session) {
.subscribe((s) -> System.out.println("Subscriber1"));
.subscribe((s) -> System.out.println("Subscriber1"));
connectable.connect();
return connectable.then();
}
``````
• groupby

The other choice is to split the incoming stream by applying the groupby operator and then filtering and subscribing inside a flatmap. Here is the sample code
``````public Mono<Void> handle(WebSocketSession session) {
return 1;
return 2;
}
}).flatMap(groupedFlux -> {
if (groupedFlux.key() == 1) {
groupedFlux.subscribe((s) -> System.out.println("Subscriber1"));
} else if (groupedFlux.key() == 2) {
groupedFlux.subscribe((s) -> System.out.println("Subscriber1"));
}
return groupedFlux;
}).then();
}
``````

Our dilemma is the which operator is best suited for our purpose. Which operator will be more performant and more importantly amongst give choices which one is correct usage. Please provide your opinions.

Tagged : / / /

Ubuntu HowTo: Headphones microphone is not working

I’m trying to use my headphones with my Ubuntu 13.10. My headphones are Nokia BH-905i, which can be connected via Bluetooth or green+pink wires.

If I use bluetooth, voice quality is terrible or microphone doesn’t work. If I try to choose “Headset” option from In and Out tabs in Sound Settings, they’re disabling each together.

In Out tab, I’m using “High sound quality A2DP”, because it betters voice quality.

How could I get my microphone working? Sound out works fine.

By design, the Bluetooth A2DP profile is unidirectional. We can not use a microphone unless our headset and adapter were capable to be paired simultaneously on both HSP, and A2DP protocol, or the application switches from A2DP to HSP on demand.

As it was already pointed out that A2DP is unidirectional, you have to switch to HSP profile every time you need to use the microphone. But doing so manually is not ideal, and unless application sets `media.role=phone` for the stream (most non-voip apps don’t), pulseaudio won’t switch it automatically by default.

But starting from Pulseaudio v. 11.0, it’s possible to automatically switch the profile whenever microphone access is requested by the application, but it’s disabled by default.

1. Find `load-module module-bluetooth-policy` line in `/etc/pulse/default.pa`
2. Change it to `load-module module-bluetooth-policy auto_switch=2`

Now pulseaudio will switch the device profile to HSP whenever microphone access is requested and change it back to A2DP after stream is closed.

You need to reload pulseaudio module after this for the changes to take effect:

1. `pulseaudio -k`
2. `pulseaudio -D`

I’ve just installed a solution for Bluetooth not selecting the mic on my Bose QC35 II on Ubuntu 16.04 LTS.

Since the internal mic on my Samsung laptop is too close to the fan audio sounds like inside an airplane for all participants in a videocall.

The solution is Blueman that adds functionality for Bluetooth.

Install it: `sudo apt-get install blueman`

I had to restart network-manager (`sudo service network-manager restart`) after WiFi stopped working (could be unrelated, but hey better have it just in case).

I can now switch to Headeset Head Unit (HSP/HFP) and choose the headset mic in Sound settings

Still a bit of a hassle when it does not switch automatically (like Android does) but it’s still better than being in a Jumbojet storage!

This –
https://www.freedesktop.org/wiki/Software/PulseAudio/Documentation/User/Bluetooth/#index5h2
helped for me.
Don’t forget to install ofono and ofono-phonesim and start service.

The necessity to provide a modem to oFono makes the headset setup
rather cumbersome. If you do not have a GSM/UMTS modem connected to
your system (like in most desktop environments), you need a modem
emulator. Unfortunately oFono does not allow to use a mobile (hfp
modem, see below) as a modem to support headsets.

The best emulator choice is “phonesim” because there is a version that
is maintained together with ofono. Most distributions contain a
package ofono-phonesim or similar while the git version can be found
at git://git.kernel.org/pub/scm/network/ofono/phonesim.git.

To set up phonesim, first create or edit the file phonesim.conf in
/etc/ofono. It should contain the following lines:

``````[phonesim]
Driver=phonesim
Port=12345
``````

Restart oFono and check with list-modems if the phonesim modem was
found. Start phonesim with

``````ofono-phonesim -p 12345 /usr/share/phonesim/default.xml&
``````

The paths to phonesim.conf and default.xml might be different if you
compiled ofono and/or phonesim from source. Now you can enable the
modem either by using the enable-modem script or by issuing the
following command:

``````dbus-send --print-reply --system --dest=org.ofono /phonesim org.ofono.Modem.SetProperty string:"Powered" variant:boolean:"true"
``````

list-modems should show that the modem is powered and online. If you
want to play with the modem emulator, you can add the -gui option to
the phonesim command. Then a GUI will pop up as soon as you enable the
modem.

Once the modem is set up properly, you can connect your headset and
pulseaudio.

Building on IvanTheFirst’s answer, I created a script that will connect to my Bluetooth headphones with mic enabled, I also had to compile Phonesim as I could not find a package with it. and to save you some time these are the packages I had to install `qtdeclarative5-dev,qt5-default`.

``````#!/bin/bash

# https://www.freedesktop.org/wiki/Software/PulseAudio/Documentation/User/Bluetooth/#index5h2
# https://git.kernel.org/pub/scm/network/ofono/phonesim.git
# to compile phonesim project, install these packages 'qtdeclarative5-dev,qt5-default'
# enable job control
set -m

# retrieve process in the form "7260/phonesim"
PHONESIM=\$(netstat -antp 2>/dev/null | grep phonesim | awk '{print \$7}')

# extract process id
PHONESIM="\${PHONESIM//phonesim/}"
if [ -n "\$PHONESIM" ]; then
echo killing old phonesim process [\$PHONESIM]
kill -9 \$PHONESIM
fi

echo "starting phonesim on port 12345"
phonesim -p 12345 /usr/share/phonesim/default.xml&

echo "enable modem"
exit_status=1
while [ \$exit_status -ne 0 ]; do
sudo dbus-send --print-reply --system --dest=org.ofono /phonesim org.ofono.Modem.SetProperty string:"Powered" variant:boolean:"true"
exit_status=\$?
done

echo "restart bluetooth"
sudo service bluetooth restart

# MAC_ADDRESS=\$(hcitool scan | grep "Galaxy Buds" | awk '{print \$1}')

while [ -z \$MAC_ADDRESS ]; do
MAC_ADDRESS=\$(hcitool scan | grep "Galaxy Buds" | awk '{print \$1}')
done

sleep 5
echo "connect to Galaxy Buds [\$MAC_ADDRESS]"
quit"

echo "reattach to phonesim process"
fg
``````

Already commented above that HFP is two-way and A2DP is one way.

But they also use different links:
(or updated versions of those)

Tagged : / / / /

Making Game: text formula =TEXT(A1,“mm/dd”) results to 00/dd

I am trying to convert the date to text.
First, I tried =TEXT(A1,”mm/dd/yyyy”) and the result is 00/dd/yyyy. From what I read in some forums, I could replace ‘yyyy’ with ‘e’ and that solved half of the problem. Still, I can’t seem to find a fix for the date and month though.
My Windows language is the same as the preferred language in my Excel application.

I have long assumed that `mm` is for minutes, and `MM` for months, unless your chosen LANGUAGE defines it in any other way. (e.g. a Swedish Excel has ÅÅÅÅ where the English has YYYY).

Now this is WRONG, it is always “mm” and APPARENTLY the CONTEXT determines which to print; e.g. “dd” close before or behind => mm is months, hh just before => mm is minutes.

To determine what your Excel requires:
Search for “format codes” in the help,
there might be a link to them in the help text for the `TEXT()`-function.

`=TEXT(A1,"mm")` or `=TEXT(A1,"MM")` will always(?) print a double digit MONTH, i.e. it is not POSSIBLE to single out the MM portion of `=TEXT(A1,"hh:MM")` unless you do `=RIGHT(TEXT(A1,"hh:MM"),2)`