phpでspreadsheetに書き込む　その1

まずcomposerを入れます
[vagrant@localhost local]$ curl -sS https://getcomposer.org/installer | php
All settings correct for using Composer
Downloading…

Composer (version 1.9.0) successfully installed to: /home/vagrant/local/composer.phar
Use it: php composer.phar

[vagrant@localhost local]$ ls
backlog.php composer.phar heroku ipa.php

composerでgoogle api clientを落とします。
[vagrant@localhost local]$ php composer.phar require google/apiclient:”^2.0″
./composer.json has been created
Loading composer repositories with package information
Updating dependencies (including require-dev)
Package operations: 13 installs, 0 updates, 0 removals
– Installing ralouphie/getallheaders (3.0.3): Downloading (100%)
– Installing psr/http-message (1.0.1): Loading from cache
– Installing guzzlehttp/psr7 (1.6.1): Downloading (100%)
– Installing guzzlehttp/promises (v1.3.1): Downloading (100%)
– Installing guzzlehttp/guzzle (6.3.3): Downloading (100%)
– Installing phpseclib/phpseclib (2.0.21): Downloading (100%)
– Installing psr/log (1.1.0): Loading from cache
– Installing monolog/monolog (1.24.0): Downloading (100%)
– Installing firebase/php-jwt (v5.0.0): Downloading (100%)
– Installing google/apiclient-services (v0.109): Downloading (100%)
– Installing psr/cache (1.0.1): Loading from cache
– Installing google/auth (v1.5.1): Downloading (100%)
– Installing google/apiclient (v2.2.3): Downloading (100%)
guzzlehttp/psr7 suggests installing zendframework/zend-httphandlerrunner (Emit PSR-7 responses)
phpseclib/phpseclib suggests installing ext-libsodium (SSH2/SFTP can make use of some algorithms provided by the libsodium-php extension.)
phpseclib/phpseclib suggests installing ext-mcrypt (Install the Mcrypt extension in order to speed up a few other cryptographic operations.)
phpseclib/phpseclib suggests installing ext-gmp (Install the GMP (GNU Multiple Precision) extension in order to speed up arbitrary precision integer arithmetic operations.)
monolog/monolog suggests installing graylog2/gelf-php (Allow sending log messages to a GrayLog2 server)
monolog/monolog suggests installing sentry/sentry (Allow sending log messages to a Sentry server)
monolog/monolog suggests installing doctrine/couchdb (Allow sending log messages to a CouchDB server)
monolog/monolog suggests installing ruflin/elastica (Allow sending log messages to an Elastic Search server)
monolog/monolog suggests installing php-amqplib/php-amqplib (Allow sending log messages to an AMQP server using php-amqplib)
monolog/monolog suggests installing ext-amqp (Allow sending log messages to an AMQP server (1.0+ required))
monolog/monolog suggests installing ext-mongo (Allow sending log messages to a MongoDB server)
monolog/monolog suggests installing mongodb/mongodb (Allow sending log messages to a MongoDB server via PHP Driver)
monolog/monolog suggests installing aws/aws-sdk-php (Allow sending log messages to AWS services like DynamoDB)
monolog/monolog suggests installing rollbar/rollbar (Allow sending log messages to Rollbar)
monolog/monolog suggests installing php-console/php-console (Allow sending log messages to Google Chrome)
google/apiclient suggests installing cache/filesystem-adapter (For caching certs and tokens (using Google_Client::setCache))
Writing lock file
Generating autoload files

gasとは

Google Apps Script(GAS)

google spread sheetとの連携に使用する

google spread sheetのidは”/d/” と “/edit” の間にある乱数字のこと
https://docs.google.com/spreadsheets/d/***/edit#gid=0

続いてGoogle sheet apiを有効化

スプレッドシートをgoogle platform userと共有

それでは、プログラムを書いていきましょう♪

slackに脆弱性情報をpost

function send_to_slack($message){
	$webhook_url = 'https://hooks.slack.com/services/hogehoge';
	$options = array(
		'http' => array(
		'method' => 'POST',
		'header' => 'Content-Type: application/json',
		'content' => json_encode($message),
	  )
	);
	$response = file_get_contents($webhook_url, false, stream_context_create($options));
	return $response === 'ok';
}

$message = array(
	'channel' => '#general',
	'username' => '脆弱性情報',
	'icon_emoji' => ':warning:',
	'text' => $string,
);

send_to_slack($message);

おおおおおおお、できたー

とりあえず、リファクタリングしよう

ipa RSSを取得

<?php 
$target_day = date('Y/m/d', strtotime('-1 day'));
$xml = "https://jvndb.jvn.jp/ja/rss/jvndb_new.rdf";

$xmlData = simplexml_load_file($xml);
foreach ($xmlData->item as $entry){
  $dc = $entry->children('http://purl.org/dc/elements/1.1/');
  $day = date('Y/m/d', strtotime($dc->date));
  if($day == $target_day){
      $string.= date('Y/m/d h:i', strtotime($dc->date))."<br>";
      $string.= $entry->title."<br>";
      $string.= $entry->link."<br>";
  }
}
?>
<html>
<body>
	<?php echo $string; ?>
</body>
</html>

OK、次はslack webhook

Herokuの料金体系とは

さて、Herokuの料金体系はどうなっているのでしょうか？
Herokuというと、何となく「無料」のサーバーという概念が強かったのですが、Heroku Schedulerを使用しようとしたところ、クレジットカードの登録を要求されて、無料版と有料版があるということを理解。

Herokuの運営がSalesforceなので、やや不安が残るというか、変な課金のされ方をしないか心配ではある。

では早速見ていこう。以下のように○○ free dyno hours(○%) used this monthとあり、AWSのように容量課金ではなく、時間単位の課金のようだ。

ちなみに、このfree dynoは月550時間使えることが見て取れる。

そしてこのfree dynoはクレジットカードを登録すると、月1000時間使えるようになる。約2倍。何んかだいぶいい加減だな。

では早速クレジットカードを登録してみましょう。
登録後は、以下のように、free dyno houresが1000に増えていることがわかります。

で、Heroku Scheduler は　Web Dynoとは別のOne-Off Dynoという仮想環境で実行される。で、One-Off Dynoで使用された時間はfree dyno houresから引かれる。つまり、cronだけで使うなら、約1/2時間になるイメージだろう。

続いて、Heroku Schedulerを登録していこう。

Your PostgreSQL is too old

[vagrant@localhost ipa]$ yum list installed | grep postgres
postgresql.x86_64 8.4.20-8.el6_9 @base
postgresql-devel.x86_64 8.4.20-8.el6_9 @base
postgresql-libs.x86_64 8.4.20-8.el6_9 @base
postgresql96.x86_64 9.6.15-1PGDG.rhel6 @pgdg96
postgresql96-devel.x86_64 9.6.15-1PGDG.rhel6 @pgdg96
postgresql96-libs.x86_64 9.6.15-1PGDG.rhel6 @pgdg96
postgresql96-server.x86_64 9.6.15-1PGDG.rhel6 @pgdg96

psqlが8.4を読み込んでいるため、「Your PostgreSQL is too old」のエラーが出る

まず、psqlにログインして、実行ファイルを探します
[vagrant@localhost ipa]$ su – postgres
パスワード:
-bash-4.1$ which -a psql
/usr/bin/psql

Heroku scheduler

[vagrant@localhost heroku]$ heroku addons:create scheduler:standard
Creating scheduler:standard on ⬢ peaceful-garden-15590… !
▸ Please verify your account to install this add-on plan (please enter a credit card) For more information, see
▸ https://devcenter.heroku.com/categories/billing Verify now at https://heroku.com/verify
[vagrant@localhost heroku]$ heroku addons:open scheduler
▸ Couldn’t find that add on.

あれ、何で？
コンソールにログインして見ましょう。

Heroku schedulerって金かかるの？

Herokuにdeploy

[vagrant@localhost local]$ mkdir heroku
[vagrant@localhost local]$ cd heroku
[vagrant@localhost heroku]$ ls
[vagrant@localhost heroku]$ touch index.php
[vagrant@localhost heroku]$ touch composer.json
[vagrant@localhost heroku]$ ls
composer.json index.php
[vagrant@localhost heroku]$ git init
Initialized empty Git repository in /home/vagrant/local/heroku/.git/
[vagrant@localhost heroku]$ git add .
[vagrant@localhost heroku]$ git commit -m “Hello heroku commit”
[master (root-commit) 683c29e] Hello heroku commit
Committer: vagrant
Your name and email address were configured automatically based
on your username and hostname. Please check that they are accurate.
You can suppress this message by setting them explicitly:

git config –global user.name “Your Name”
git config –global user.email you@example.com

If the identity used for this commit is wrong, you can fix it with:

git commit –amend –author=’Your Name ‘

1 files changed, 3 insertions(+), 0 deletions(-)
create mode 100644 composer.json
create mode 100644 index.php
[vagrant@localhost heroku]$ heroku create
Creating app… done, ⬢ peaceful-garden-15590
https://peaceful-garden-15590.herokuapp.com/ | https://git.heroku.com/peaceful-garden-15590.git
[vagrant@localhost heroku]$ git push heroku master
Counting objects: 4, done.
Compressing objects: 100% (2/2), done.
Writing objects: 100% (4/4), 295 bytes, done.
Total 4 (delta 0), reused 0 (delta 0)
remote: Compressing source files… done.
remote: Building source:
remote:
remote: —–> PHP app detected
remote:
remote: NOTICE: Your ‘composer.json’ is completely empty!
remote:
remote: A completely empty file is not a valid JSON document.
remote:
remote: Heroku automatically corrected this problem, but it is strongly
remote: recommended you change the contents to at least ‘{}’.
remote:
remote: For documentation on Composer and dependency management, check
remote: out the introduction at https://getcomposer.org/doc/00-intro.md
remote:
remote: —–> Bootstrapping…
remote: —–> Installing platform packages…
remote: NOTICE: No runtime required in composer.lock; using PHP ^7.0.0
remote: – php (7.3.8)
remote: – apache (2.4.39)
remote: – nginx (1.16.0)
remote: —–> Installing dependencies…
remote: Composer version 1.9.0 2019-08-02 20:55:32
remote: —–> Preparing runtime environment…
remote: NOTICE: No Procfile, using ‘web: heroku-php-apache2’.
remote: —–> Checking for additional extensions to install…
remote: —–> Discovering process types
remote: Procfile declares types -> web
remote:
remote: —–> Compressing…
remote: Done: 15.7M
remote: —–> Launching…
remote: Released v3
remote: https://peaceful-garden-15590.herokuapp.com/ deployed to Heroku
remote:
remote: Verifying deploy… done.
To https://git.heroku.com/peaceful-garden-15590.git
* [new branch] master -> master

ほう、rubyじゃなくてもいけるやんか。
で、Herokuでcronってどうやるんだ?

あれ、Heroku Schedulerで行けそう？？　もしかして^^

.bash_profile

– 設定ファイル
– ログインした時に読み込まれる
– ログインシェルがbashの状態でログインした時に読み込まれる
– bash_profileはログインした直後のホームディレクトリにある