spreadsheet2sjtubbs

README

spreadsheet2sjtubbs script

This script updates the contents of a worksheet in Google Spreadsheet into a post at SJTUBBS.

Setup instructions:

1. Download a copy of config.inc.php and spreadsheet2sjtubbs.cli.php
2. Open the Spreadsheet, click [File - Publish to the Web] menu item,
   click [Start Publishing]
3. In [Get a link to the published data],
   select [TXT (Plain Text)] in the first dropdown,
   select the worksheet name in the second dropdown,
   copy the URL displayed in the fourth box
4. Open config.inc.php, paste the URL into SPREADSHEET_PUBLISH_URL
5. Make a post at SJTUBBS, note its URL
6. Open config.inc.php,
   set SJTUBBS_USER and SJTUBBS_PASSWD to the username and password of the poster,
   set SJTUBBS_BOARD to the board name (part of post URL after 'board,'),
   set SJTUBBS_FILE to the file name (part of post URL between 'M.' and '.A'),
   set SJTUBBS_TITLE to the post title
7. Execute `crontab -e` and enter this entry:
   28 * * * * php5 /path/to/spreadsheet2sjtubbs.cli.php

config.inc.php

<?php
define('SPREADSHEET_PUBLISH_URL', 'https://docs.google.com/spreadsheet/pub?key=0As8cYqq8fm-ydGpGRzNtbkV1TFJCZEhyQzhCY2JKaEE&single=true&gid=0&output=txt');

define('SJTUBBS_USER', 'yoursunny');
define('SJTUBBS_PASSWD', '');
define('SJTUBBS_BOARD', 'test');
define('SJTUBBS_FILE', 'M.1388866289.A');
define('SJTUBBS_TITLE', 'spreadsheet2sjtubbs script test page');
?>

spreadsheet2sjtubbs.cli.php

<?php
require_once 'config.inc.php';

// retrieve spreadsheet
$text = file_get_contents(SPREADSHEET_PUBLISH_URL);

// prepare sjtubbs session
$ch = curl_init();
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
//curl_setopt($ch, CURLOPT_PROXY, '10.0.2.2:8888');
curl_setopt($ch, CURLOPT_USERAGENT, 'yoursunny-spreadsheet2sjtubbs/20140104');
curl_setopt($ch, CURLOPT_COOKIEFILE, '');

// sign in sjtubbs
curl_setopt($ch, CURLOPT_URL, 'https://bbs.sjtu.edu.cn/bbslogin');
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, sprintf('id=%s&pw=%s&submit=login', urlencode(SJTUBBS_USER), urlencode(SJTUBBS_PASSWD)));
curl_exec($ch);

// edit post
curl_setopt($ch, CURLOPT_URL, 'https://bbs.sjtu.edu.cn/bbsedit');
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, sprintf('title=%s&text=%s&type=1&board=%s&file=%s', urlencode(iconv('utf8','gb2312//TRANSLIT//IGNORE',SJTUBBS_TITLE)), urlencode(iconv('utf8','gb2312//TRANSLIT//IGNORE',$text)), urlencode(SJTUBBS_BOARD), urlencode(SJTUBBS_FILE)));
curl_exec($ch);

curl_close($ch);
?>