<commit_before>""" Pipulate lets you collect data straight off of the Web into spreadsheets.

        _____ _             _       _
       |  __ (_)           | |     | |
       | |__) | _ __  _   _| | __ _| |_ ___     ___ ___  _ __ ___
       |  ___/ | '_ \| | | | |/ _` | __/ _ \   / __/ _ \| '_ ` _ \
       | |   | | |_) | |_| | | (_| | ||  __/  | (_| (_) | | | | | |
       |_|   |_| .__/ \__,_|_|\__,_|\__\___| (_)___\___/|_| |_| |_|
               | |
               |_|

Got a list that you need to look up something for every item - like grabbing
title tags for a list of URLs? Well, then Pipulate is your answer! For example,
for any given URL, you will be able to:

  1. Record the number of times that URL has appeared in a Tweet
  2. Record the number of times that URL was liked or shared in Facebook
  3. Record the number of times that URL was +1'd in Google Plus
  4. Figure out what keyword the page is trying to target for search traffic
  5. See how well that URL is doing for that keyword (position tracking)

And it all accumulates on a spreadsheet where you can do trending and keep
track of progress - all for free! But Pipulate is so much more, able to do such
lookups using any source data (addresses, keywords, etc.) against any Web or
data API source (you have access to), allowing you to write your own Python
functions to extend the system. And what system is that? You can learn it well,
because almost every line of code accompanied by a YouTube video. Playlist:
https://www.youtube.com/watch?v=SdzDaohx-GA&list=PLy-AlqZFg6G8tBTB6FFN68mryG4JlCaf-

REQUIREMENTS:
easy_install flask
easy_install flask_wtf
easy_install requests
easy_install gspread
"""

import globs #Create objects that don't have to be passed as arguments.
from flask import Flask, request, render_template, session, flash, redirect, url_for
from flask_wtf import Form
from flask_wtf.file import FileField
from wtforms import validators, StringField
from wtforms.validators import DataRequired, Optional, Required

app = Flask(__name__, static_folder='../uploads', static_url_path='/files')
app.secret_key = '<KEY>'

@app.context_processor
def templateglobals():
    return dict(loginlink=getLoginlink(), bookmarklet=getBookmarklet())

class PipForm(Form):
  pipurl = StringField('Paste a Google Spreadsheet URL:')

@app.route("/", methods=['GET', 'POST'])
def main():
  form = PipForm(csrf_enabled=False)
  if session:
    if 'oa2' in session:
      import gspread
      credentials = Credentials(access_token=session['oa2'])
      try:
        gc = gspread.authorize(credentials)
        gc.openall()
        session['loggedin'] = "1"
      except:
        session.clear()
  if request.method == 'POST':
    if form.pipurl.data:
      globs.PIPURL = form.pipurl.data
      pipulate('gdocs')
    else:
      flash('Nothing to Pipulate. Enter a Google Spreadsheet URL and try again.')
    return render_template('pipulate.html', form=form)
  else:
    if request.args:
      if 'logout' in request.args:
        if session:
          if 'oa2' in session:
            import urllib2
            revokeurl = 'https://accounts.google.com/o/oauth2/revoke?token=' + session['oa2']
            urllib2.urlopen(revokeurl)
          session.clear()
          flash('Logged out from Google.')
        return render_template('pipulate.html', form=form)
      if "access_token" in request.args:
        session['oa2'] = request.args.get("access_token")
        session['loggedin'] = "1"
        return redirect(url_for('main'))
      if 'u' in request.args:
        session['u'] = request.args.get('u')
      if session:
        if 'u' in session:
          form.pipurl.data = session['u']
      if request.url_root == url_root(form.pipurl.data):
        form.pipurl.data = ''
    return render_template('pipulate.html', form=form)

def url_root(url):
  from urlparse import urlparse
  parsed = urlparse(url)
  return "%s://%s%s" % (parsed[0], parsed[1], parsed[2])

def getLoginlink():
  baseurl = "https://accounts.google.com/o/oauth2/auth"
  qsdict = {  'scope': 'https://docs.google.com/feeds/ https://docs.googleusercontent.com/ https://spreadsheets.google.com/feeds/',
              'response_type': 'token',
              'redirect_uri': 'http://'+request.headers['Host'],
              'approval_prompt': 'force',
              'client_id': '394883714902-h3fjk3u6rb4jr4ntpeft41kov6et2nve.apps.googleusercontent.com'
            }
  from urllib import urlencode
  return "%s?%s" % (baseurl, urlencode(qsdict))

def getBookmarklet():
  return '''javascript:(function(){window.open('http://%s/?u='+encodeURIComponent(document.location.href), 'Pipulate', 'toolbar=0,resizable=1,scrollbars=1,status=1,width=640,height=367');})();''' % (request.headers['Host'])

class Credentials (object):
  def __init__ (self, access_token=None):
    self.access_token = access_token

def pipulate(dbsource):
  """Allows processing of multiple worksheets."""

  funcs = [x for x in globals().keys() if x[:2] != '__'] #List all functions
  globs.funcslc = [x.lower() for x in funcs] #Lower-case all function names
  globs.transfunc = dict(zip(globs.funcslc, funcs)) #Keep translation table
  import pickle, gspread
  if session:
    if 'oa2' in session:
      credentials = Credentials(access_token=session['oa2'])
    else:
      flash('Not logged into Google. Please Login.')
      return
    try:
      gc = gspread.authorize(credentials)
      wks = gc.open_by_url(globs.PIPURL).sheet1 #HTTP connection errors happen here.
      # https://docs.google.com/spreadsheets/d/182yAd0VYBhY30IW1sGXUg110aWh0pMaQa4nVtWXgNBo/edit?usp=sharing
    except:
      flash("Couldn't reach Google Docs. Try logging in again.")
      return
    for rowdex in range(1, wks.row_count): #Start stepping through every row.
      arow = wks.row_values(rowdex)
      if arow: #But only process it if it does not come back as empty list.
        newrow = processrow(str(rowdex), arow) #Replace question marks in row
        for coldex, acell in enumerate(newrow): #Then step through new row
          if questionmark(arow, rowdex, coldex): #And update Google worksheet
            wks.update_cell(rowdex, coldex+1, acell) #Gspread has no "0" column
      else:
        break #Stop grabbing new rows at the first empty one encountered.
    flash('Processed Google Spreadsheet')
  else:
    flash('Please Login to Google')

def questionmark(oldrow, rowdex, coldex):
  """Returns true if a question mark is supposed to be replaced in cell.
  
  This is called for every cell on every row processed and checks whether
  question mark replacemnt should actually occur."""
  if rowdex != 1:
    if globs.row1[coldex] in globs.funcslc:
      if oldrow[coldex] == '?':
        return(True)
  return(False)

def processrow(rowdex, arow):
  """Separates row-1 handling from question mark detection on all other rows.<commit_msg>Adding Pipulate configuration tab<commit_after>""" Pipulate lets you collect data straight off of the Web into spreadsheets.

        _____ _             _       _
       |  __ (_)           | |     | |
       | |__) | _ __  _   _| | __ _| |_ ___     ___ ___  _ __ ___
       |  ___/ | '_ \| | | | |/ _` | __/ _ \   / __/ _ \| '_ ` _ \
       | |   | | |_) | |_| | | (_| | ||  __/  | (_| (_) | | | | | |
       |_|   |_| .__/ \__,_|_|\__,_|\__\___| (_)___\___/|_| |_| |_|
               | |
               |_|

Got a list that you need to look up something for every item - like grabbing
title tags for a list of URLs? Well, then Pipulate is your answer! For example,
for any given URL, you will be able to:

  1. Record the number of times that URL has appeared in a Tweet
  2. Record the number of times that URL was liked or shared in Facebook
  3. Record the number of times that URL was +1'd in Google Plus
  4. Figure out what keyword the page is trying to target for search traffic
  5. See how well that URL is doing for that keyword (position tracking)

And it all accumulates on a spreadsheet where you can do trending and keep
track of progress - all for free! But Pipulate is so much more, able to do such
lookups using any source data (addresses, keywords, etc.) against any Web or
data API source (you have access to), allowing you to write your own Python
functions to extend the system. And what system is that? You can learn it well,
because almost every line of code accompanied by a YouTube video. Playlist:
https://www.youtube.com/watch?v=SdzDaohx-GA&list=PLy-AlqZFg6G8tBTB6FFN68mryG4JlCaf-

REQUIREMENTS:
easy_install flask
easy_install flask_wtf
easy_install requests
easy_install gspread
"""

import globs #Create objects that don't have to be passed as arguments.
from flask import Flask, request, render_template, session, flash, redirect, url_for
from flask_wtf import Form
from flask_wtf.file import FileField
from wtforms import validators, StringField
from wtforms.validators import DataRequired, Optional, Required

app = Flask(__name__, static_folder='../uploads', static_url_path='/files')
app.secret_key = '<KEY>'

@app.context_processor
def templateglobals():
    return dict(loginlink=getLoginlink(), bookmarklet=getBookmarklet())

class PipForm(Form):
  pipurl = StringField('Paste a Google Spreadsheet URL:')

@app.route("/", methods=['GET', 'POST'])
def main():
  form = PipForm(csrf_enabled=False)
  if session:
    if 'oa2' in session:
      import gspread
      credentials = Credentials(access_token=session['oa2'])
      try:
        gsp = gspread.authorize(credentials)
        gsp.openall()
        session['loggedin'] = "1"
      except:
        session.clear()
  if request.method == 'POST':
    if form.pipurl.data:
      globs.PIPURL = form.pipurl.data
      pipulate('gdocs')
    else:
      flash('Nothing to Pipulate. Enter a Google Spreadsheet URL and try again.')
    return render_template('pipulate.html', form=form)
  else:
    if request.args:
      if 'logout' in request.args:
        if session:
          if 'oa2' in session:
            import urllib2
            revokeurl = 'https://accounts.google.com/o/oauth2/revoke?token=' + session['oa2']
            urllib2.urlopen(revokeurl)
          session.clear()
          flash('Logged out from Google.')
        return render_template('pipulate.html', form=form)
      if "access_token" in request.args:
        session['oa2'] = request.args.get("access_token")
        session['loggedin'] = "1"
        return redirect(url_for('main'))
      if 'u' in request.args:
        session['u'] = request.args.get('u')
      if session:
        if 'u' in session:
          form.pipurl.data = session['u']
      if request.url_root == url_root(form.pipurl.data):
        form.pipurl.data = ''
    return render_template('pipulate.html', form=form)

def url_root(url):
  from urlparse import urlparse
  parsed = urlparse(url)
  return "%s://%s%s" % (parsed[0], parsed[1], parsed[2])

def getLoginlink():
  baseurl = "https://accounts.google.com/o/oauth2/auth"
  qsdict = {  'scope': 'https://docs.google.com/feeds/ https://docs.googleusercontent.com/ https://spreadsheets.google.com/feeds/',
              'response_type': 'token',
              'redirect_uri': 'http://'+request.headers['Host'],
              'approval_prompt': 'force',
              'client_id': '394883714902-h3fjk3u6rb4jr4ntpeft41kov6et2nve.apps.googleusercontent.com'
            }
  from urllib import urlencode
  return "%s?%s" % (baseurl, urlencode(qsdict))

def getBookmarklet():
  return '''javascript:(function(){window.open('http://%s/?u='+encodeURIComponent(document.location.href), 'Pipulate', 'toolbar=0,resizable=1,scrollbars=1,status=1,width=640,height=367');})();''' % (request.headers['Host'])

class Credentials (object):
  def __init__ (self, access_token=None):
    self.access_token = access_token

def pipulate(dbsource):
  """Allows processing of multiple worksheets."""

  funcs = [x for x in globals().keys() if x[:2] != '__'] #List all functions
  globs.funcslc = [x.lower() for x in funcs] #Lower-case all function names
  globs.transfunc = dict(zip(globs.funcslc, funcs)) #Keep translation table
  import gspread
  if session:
    if 'oa2' in session:
      credentials = Credentials(access_token=session['oa2'])
    else:
      flash('Not logged into Google. Please Login.')
      return
    try:
      gsp = gspread.authorize(credentials)
      pipdoc = gsp.open_by_url(globs.PIPURL) #HTTP connection errors happen here.
      pipsheet = pipdoc.sheet1
    except:
      flash("Couldn't reach Google Docs. Try logging in again.")
      return
    try:
      config = pipdoc.worksheet("Pipulate")
    except:
      config = pipdoc.add_worksheet(title="Pipulate", rows="1", cols="2")
      flash("Created Pipulate tab.")
    for rowdex in range(1, pipsheet.row_count): #Start stepping through every row.
      arow = pipsheet.row_values(rowdex)
      if arow: #But only process it if it does not come back as empty list.
        newrow = processrow(str(rowdex), arow) #Replace question marks in row
        for coldex, acell in enumerate(newrow): #Then step through new row
          if questionmark(arow, rowdex, coldex): #And update Google worksheet
            pipsheet.update_cell(rowdex, coldex+1, acell) #Gspread has no "0" column
      else:
        break #Stop grabbing new rows at the first empty one encountered.
    flash('Replated question marks.")
  else:
    flash('Please Login to Google')

def questionmark(oldrow, rowdex, coldex):
  """Returns true if a question mark is supposed to be replaced in cell.
  
  This is called for every cell on every row processed and checks whether
  question mark replacemnt should actually occur."""
  if rowdex != 1:
    if globs.row1[coldex] in globs.funcslc:
      if oldrow[coldex] == '?':
        return(True)
  return(False)

def processrow(rowdex, arow):
  """Separates row-1 handling from question mark detection on all other rows.
{'fraction_non_alphanumeric': 0.1157758061025752, 'fraction_numerical': 0.010675899877371421, 'mean_word_length': 3.1546299071021875, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 13, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2789133', 'n_tokens_mistral': 4487, 'n_tokens_neox': 4359, 'n_words': 1311}