def setup_auth(args):
    """Set up and authentication httplib.

    Args:
        args: ArgumentParser with additional command-line flags to pass to
            the OAuth authentication flow.

    Returns:
        An http client library with authentication enabled.
    """
    # Perform OAuth 2.0 authorization.
    if args.service_account:
        # Service accounts will follow the following authenication.
        client_email = args.service_account
	secret_file = args.service_account_secrets_file
	if secret_file.endswith('json'):
	    credentials = ServiceAccountCredentials.from_json_keyfile_name(
		secret_file, SCOPES)
	elif secret_file.endswith('p12'):
	    credentials = ServiceAccountCredentials.from_p12_keyfile(
	        client_email, secret_file, SCOPES)

    else:
        flow = flow_from_clientsecrets(args.client_secrets_file, scope=SCOPES)
        storage = oauth_file.Storage(OAUTH2_STORAGE)
        credentials = storage.get()

        if credentials is None or credentials.invalid:
            credentials = tools.run_flow(flow, storage, args)

    http = httplib2.Http()
    return credentials.authorize(http)
예제 #2
0
    def __init__(self, service_email=None, private_key=None, user_email=None):
        """
        Handles credentials and builds the google service.

        :param service_email: String
        :param private_key: Path
        :param user_email: String
        :raise ValueError:
        """
        self._service_email = service_email or settings.GOOGLE_DRIVE_STORAGE_SERVICE_EMAIL
        self._key = private_key or settings.GOOGLE_DRIVE_STORAGE_KEY

        kwargs = {}
        if user_email or settings.GOOGLE_DRIVE_STORAGE_USER_EMAIL:
            self._user_email = kwargs['sub'] = user_email or settings.GOOGLE_DRIVE_STORAGE_USER_EMAIL
        credentials = ServiceAccountCredentials(
            self._service_email,
            self._key,
            scope="https://www.googleapis.com/auth/drive",
            **kwargs
        )
        http = httplib2.Http()
        http = credentials.authorize(http)

        self._drive_service = build('drive', 'v2', http=http)
예제 #3
0
def get_service_acct_creds(key_file, verbose=False):
  '''Generate service account credentials using the given key file.
    key_file: path to file containing private key.
  '''
  ### backcompatability for .p12 keyfiles
  if key_file.endswith('.p12') or key_file.endswith('.pem'):
    from edx2bigquery_config import auth_service_acct as SERVICE_ACCT
    if verbose:
      print "using key file"
      print "service_acct=%s, key_file=%s" % (SERVICE_ACCT, KEY_FILE)
    try:
      creds = ServiceAccountCredentials.from_p12_keyfile(
        SERVICE_ACCT,
        key_file,
        scopes=BIGQUERY_SCOPE)
    except Exception as err:			# fallback to old google SignedJwtAssertionCredentials call
      with open (key_file, 'rb') as f:
        key = f.read();
        creds = SignedJwtAssertionCredentials(
          SERVICE_ACCT, 
          key,
          BIGQUERY_SCOPE)
    return creds
  ###
  creds = ServiceAccountCredentials.from_json_keyfile_name(
    key_file,
    BIGQUERY_SCOPE)
  return creds
예제 #4
0
    def __init__(self, config):
        self.api_key = config["apiKey"]
        self.auth_domain = config["authDomain"]
        self.database_url = config["databaseURL"]
        self.storage_bucket = config["storageBucket"]
        self.credentials = None
        self.requests = requests.Session()
        if config.get("serviceAccount"):
            scopes = [
                'https://www.googleapis.com/auth/firebase.database',
                'https://www.googleapis.com/auth/userinfo.email',
                "https://www.googleapis.com/auth/cloud-platform"
            ]
            service_account_type = type(config["serviceAccount"])
            if service_account_type is str:
                self.credentials = ServiceAccountCredentials.from_json_keyfile_name(config["serviceAccount"], scopes)
            if service_account_type is dict:
                self.credentials = ServiceAccountCredentials.from_json_keyfile_dict(config["serviceAccount"], scopes)
        if is_appengine_sandbox():
            # Fix error in standard GAE environment
            # is releated to https://github.com/kennethreitz/requests/issues/3187
            # ProtocolError('Connection aborted.', error(13, 'Permission denied'))
            adapter = appengine.AppEngineAdapter(max_retries=3)
        else:
            adapter = requests.adapters.HTTPAdapter(max_retries=3)

        for scheme in ('http://', 'https://'):
            self.requests.mount(scheme, adapter)
예제 #5
0
def get_credentials():
    if os.path.isfile(CLIENT_SECRET_FILE):
        credentials = ServiceAccountCredentials.from_json_keyfile_name(CLIENT_SECRET_FILE, scopes=API_SCOPES)
    else:
        keyfile_data = json.loads(os.environ.get('KEYFILE'))
        credentials = ServiceAccountCredentials.from_json_keyfile_dict(keyfile_dict=keyfile_data, scopes=API_SCOPES)
    return credentials
 def _from_p12_keyfile_helper(self, private_key_password=None, scopes='',
                              token_uri=None, revoke_uri=None):
     service_account_email = '*****@*****.**'
     filename = data_filename('privatekey.p12')
     with open(filename, 'rb') as file_obj:
         key_contents = file_obj.read()
     creds_from_filename = ServiceAccountCredentials.from_p12_keyfile(
         service_account_email, filename,
         private_key_password=private_key_password,
         scopes=scopes, token_uri=token_uri, revoke_uri=revoke_uri)
     creds_from_file_contents = (
         ServiceAccountCredentials.from_p12_keyfile_buffer(
             service_account_email, BytesIO(key_contents),
             private_key_password=private_key_password,
             scopes=scopes, token_uri=token_uri, revoke_uri=revoke_uri))
     for creds in (creds_from_filename, creds_from_file_contents):
         self.assertIsInstance(creds, ServiceAccountCredentials)
         self.assertIsNone(creds.client_id)
         self.assertEqual(creds._service_account_email, service_account_email)
         self.assertIsNone(creds._private_key_id)
         self.assertIsNone(creds._private_key_pkcs8_pem)
         self.assertEqual(creds._private_key_pkcs12, key_contents)
         if private_key_password is not None:
             self.assertEqual(creds._private_key_password, private_key_password)
         self.assertEqual(creds._scopes, ' '.join(scopes))
         self.assertEqual(creds.token_uri, token_uri)
         self.assertEqual(creds.revoke_uri, revoke_uri)
예제 #7
0
    def _build_service(self, api_name, api_version, scopes):

        if self._keyfile_path is None:
            credentials = GoogleCredentials.get_application_default()
            service = build(api_name, api_version, credentials=credentials)
            return credentials, service
        else:
            if self._keyfile_path.lower().endswith(".json"):
                credentials = ServiceAccountCredentials.from_json_keyfile_name(
                    self._keyfile_path,
                    scopes=scopes)
            elif self._keyfile_path.lower().endswith(".p12"):
                if self._account_email is None:
                    raise Exception("Input account email.")
                credentials = ServiceAccountCredentials.from_p12_keyfile(
                    self._account_email,
                    self._keyfile_path,
                    scopes=scopes)
            else:
                error_message = """
                    Key file format [{0}] is illegal.
                    Key file must be .json or .p12.
                """.format(self._keyfile_path)
                raise Exception(error_message)

            #http = httplib2.Http()
            #auth_http = credentials.authorize(http)
            #service = build(api_name, api_version, http=auth_http)
            service = build(api_name, api_version, credentials=credentials)
            return credentials, service
def _GetOauth2ServiceAccountCredentials():
  """Retrieves OAuth2 service account credentials for a private key file."""
  if not _HasOauth2ServiceAccountCreds():
    return

  provider_token_uri = _GetProviderTokenUri()
  service_client_id = config.get('Credentials', 'gs_service_client_id', '')
  private_key_filename = config.get('Credentials', 'gs_service_key_file', '')

  with io.open(private_key_filename, 'rb') as private_key_file:
    private_key = private_key_file.read()

  keyfile_is_utf8 = False
  try:
    private_key = private_key.decode(UTF8)
    # P12 keys won't be encoded as UTF8 bytes.
    keyfile_is_utf8 = True
  except UnicodeDecodeError:
    pass

  if keyfile_is_utf8:
    try:
      json_key_dict = json.loads(private_key)
    except ValueError:
      raise Exception('Could not parse JSON keyfile "%s" as valid JSON' %
                      private_key_filename)
    # Key file is in JSON format.
    for json_entry in ('client_id', 'client_email', 'private_key_id',
                       'private_key'):
      if json_entry not in json_key_dict:
        raise Exception('The JSON private key file at %s '
                        'did not contain the required entry: %s' %
                        (private_key_filename, json_entry))
    return ServiceAccountCredentials.from_json_keyfile_dict(
        json_key_dict, scopes=DEFAULT_SCOPES, token_uri=provider_token_uri)
  else:
    # Key file is in P12 format.
    if HAS_CRYPTO:
      if not service_client_id:
        raise Exception('gs_service_client_id must be set if '
                        'gs_service_key_file is set to a .p12 key file')
      key_file_pass = config.get('Credentials', 'gs_service_key_file_password',
                                 GOOGLE_OAUTH2_DEFAULT_FILE_PASSWORD)
      # We use _from_p12_keyfile_contents to avoid reading the key file
      # again unnecessarily.
      try:
        return ServiceAccountCredentials.from_p12_keyfile_buffer(
            service_client_id,
            BytesIO(private_key),
            private_key_password=key_file_pass,
            scopes=DEFAULT_SCOPES,
            token_uri=provider_token_uri)
      except Exception as e:
        raise Exception(
            'OpenSSL unable to parse PKCS 12 key {}.'
            'Please verify key integrity. Error message:\n{}'.format(
                private_key_filename, str(e)))
 def test_create_delegated(self):
     signer = object()
     sub = '*****@*****.**'
     creds = ServiceAccountCredentials('*****@*****.**', signer)
     self.assertNotIn('sub', creds._kwargs)
     delegated_creds = creds.create_delegated(sub)
     self.assertEqual(delegated_creds._kwargs['sub'], sub)
     # Make sure the original is unchanged.
     self.assertNotIn('sub', creds._kwargs)
 def test_create_delegated_existing_sub(self):
     signer = object()
     sub1 = '*****@*****.**'
     sub2 = '*****@*****.**'
     creds = ServiceAccountCredentials('*****@*****.**', signer, sub=sub1)
     self.assertEqual(creds._kwargs['sub'], sub1)
     delegated_creds = creds.create_delegated(sub2)
     self.assertEqual(delegated_creds._kwargs['sub'], sub2)
     # Make sure the original is unchanged.
     self.assertEqual(creds._kwargs['sub'], sub1)
예제 #11
0
 def test_p12_type_non_bytes_to_sign(self):
     from oauth2client.service_account import ServiceAccountCredentials
     ACCOUNT_NAME = 'dummy_service_account_name'
     PRIVATE_KEY_TEXT = b'dummy_private_key_text'
     STRING_TO_SIGN = u'dummy_signature'
     SIGNER = object()
     CREDENTIALS = ServiceAccountCredentials(
         ACCOUNT_NAME, SIGNER)
     CREDENTIALS._private_key_pkcs12 = PRIVATE_KEY_TEXT
     CREDENTIALS._private_key_password = '******'
     self._run_with_fake_crypto(CREDENTIALS, PRIVATE_KEY_TEXT,
                                STRING_TO_SIGN)
def setup_credentials():
    scope = ['https://spreadsheets.google.com/feeds']
    if on_heroku:
        keyfile_dict = setup_keyfile_dict()
        credentials = ServiceAccountCredentials.from_json_keyfile_dict(keyfile_dict, scope)
    else:
        credentials = ServiceAccountCredentials.from_json_keyfile_name('My Project-3b0bc29d35d3.json', scope)

    gc = gspread.authorize(credentials)

    wks = gc.open_by_key("1GnVhFp0s28HxAEOP6v7kmfmt3yPL_TGJSV2mcn1RPMY").sheet1
    return wks
    def _create_drive_service(creds_file=None, creds_json=None):
        SCOPES = ['https://www.googleapis.com/auth/drive.readonly']

        if creds_json is not None:
            creds = json.loads(creds_json)
            credentials = ServiceAccountCredentials.from_json_keyfile_dict(
                    creds, SCOPES)
        elif creds_file is not None:
            credentials = ServiceAccountCredentials.from_json_keyfile_name(
                    creds_file, SCOPES)
        else:
            raise ValueError("Either creds_file or creds_json must be defined")
        http_auth = credentials.authorize(httplib2.Http())

        return build('drive', 'v3', http=http_auth)
예제 #14
0
 def _make_credentials(self):
     private_key = datafile('privatekey.' + self.format_)
     signer = crypt.Signer.from_string(private_key)
     credentials = ServiceAccountCredentials(
         '*****@*****.**', signer,
         scopes='read+write',
         sub='*****@*****.**')
     if self.format_ == 'pem':
         credentials._private_key_pkcs8_pem = private_key
     elif self.format_ == 'p12':
         credentials._private_key_pkcs12 = private_key
         credentials._private_key_password = _PASSWORD_DEFAULT
     else:  # pragma: NO COVER
         raise ValueError('Unexpected format.')
     return credentials
def setup_credentials():
    scope = ['https://spreadsheets.google.com/feeds']
    if on_heroku:
        keyfile_dict = setup_keyfile_dict()
        credentials = ServiceAccountCredentials.from_json_keyfile_dict(keyfile_dict, scope)
    else:
        credentials = ServiceAccountCredentials.from_json_keyfile_name('My Project-3b0bc29d35d3.json', scope)

    gc = gspread.authorize(credentials)

    if on_heroku:
        sps = gc.open_by_key("1DdmBaOlGGdgQRaaI3tQCxj3BEd8kPwaGIHVfMpIoH8I")
    else:
        sps = gc.open_by_key("1DdmBaOlGGdgQRaaI3tQCxj3BEd8kPwaGIHVfMpIoH8I")
    return sps
예제 #16
0
 def http(self, sub=None):
     credentials = ServiceAccountCredentials.from_p12_keyfile(
         'faf-server',
         'faf-server.pem',
         scopes='write_achievements write_events'
     )
     return credentials.authorize(Http())
def get_repo_request_rows():
    from oauth2client.service_account import ServiceAccountCredentials

    # this file inspired by https://www.twilio.com/blog/2017/02/an-easy-way-to-read-and-write-to-a-google-spreadsheet-in-python.html

    # use creds to create a client to interact with the Google Drive API
    scopes = ['https://spreadsheets.google.com/feeds']
    json_creds = os.getenv("GOOGLE_SHEETS_CREDS_JSON")

    creds_dict = json.loads(json_creds)

    # hack to get around ugly new line escaping issues
    # this works for me, but later found links to what might be cleaner solutions:
    # use ast.literal_eval?  https://github.com/googleapis/google-api-go-client/issues/185#issuecomment-422732250
    # or maybe dumping like this might fix it? https://coreyward.svbtle.com/how-to-send-a-multiline-file-to-heroku-config

    creds_dict["private_key"] = creds_dict["private_key"].replace("\\\\n", "\n")

    # now continue
    creds = ServiceAccountCredentials.from_json_keyfile_dict(creds_dict, scopes)
    client = gspread.authorize(creds)

    # Find a workbook by url
    spreadsheet = client.open_by_url("https://docs.google.com/spreadsheets/d/1RcQuetbKVYRRf0GhGZQi38okY8gT1cPUs6l3RM94yQo/edit#gid=704459328")
    sheet = spreadsheet.sheet1

    # Extract and print all of the values
    rows = sheet.get_all_values()
    print(rows[0:1])
    return rows
예제 #18
0
    def from_service_account_json(cls, json_credentials_path, *args, **kwargs):
        """Factory to retrieve JSON credentials while creating client.

        :type json_credentials_path: string
        :param json_credentials_path: The path to a private key file (this file
                                      was given to you when you created the
                                      service account). This file must contain
                                      a JSON object with a private key and
                                      other credentials information (downloaded
                                      from the Google APIs console).

        :type args: tuple
        :param args: Remaining positional arguments to pass to constructor.

        :type kwargs: dict
        :param kwargs: Remaining keyword arguments to pass to constructor.

        :rtype: :class:`gcloud.pubsub.client.Client`
        :returns: The client created with the retrieved JSON credentials.
        :raises: :class:`TypeError` if there is a conflict with the kwargs
                 and the credentials created by the factory.
        """
        if "credentials" in kwargs:
            raise TypeError("credentials must not be in keyword arguments")
        credentials = ServiceAccountCredentials.from_json_keyfile_name(json_credentials_path)
        kwargs["credentials"] = credentials
        return cls(*args, **kwargs)
예제 #19
0
    def __init__(self, creds_file):

        credentials = ServiceAccountCredentials.from_json_keyfile_name(
            creds_file, SCOPES)
        self.gc = gspread.authorize(credentials)
        self.storage_service = discovery.build('storage', 'v1',
                                               credentials=credentials)
예제 #20
0
    def from_service_account_p12(cls, client_email, private_key_path, *args, **kwargs):
        """Factory to retrieve P12 credentials while creating client.

        .. note::
          Unless you have an explicit reason to use a PKCS12 key for your
          service account, we recommend using a JSON key.

        :type client_email: string
        :param client_email: The e-mail attached to the service account.

        :type private_key_path: string
        :param private_key_path: The path to a private key file (this file was
                                 given to you when you created the service
                                 account). This file must be in P12 format.

        :type args: tuple
        :param args: Remaining positional arguments to pass to constructor.

        :type kwargs: dict
        :param kwargs: Remaining keyword arguments to pass to constructor.

        :rtype: :class:`gcloud.client.Client`
        :returns: The client created with the retrieved P12 credentials.
        :raises: :class:`TypeError` if there is a conflict with the kwargs
                 and the credentials created by the factory.
        """
        if "credentials" in kwargs:
            raise TypeError("credentials must not be in keyword arguments")
        credentials = ServiceAccountCredentials.from_p12_keyfile(client_email, private_key_path)
        kwargs["credentials"] = credentials
        return cls(*args, **kwargs)
예제 #21
0
def get_worksheet():
    scope = ['https://spreadsheets.google.com/feeds']
    credentials = ServiceAccountCredentials.from_json_keyfile_name(app.config['JSON_KEYFILE_NAME'],scope);
    gc = gspread.authorize(credentials)
    sheet = gc.open_by_key(app.config['GOOGLE_SHEET_KEY'])
    worksheet = sheet.get_worksheet(0)
    return worksheet
 def test__to_json_override(self):
     signer = object()
     creds = ServiceAccountCredentials('*****@*****.**',
                                       signer)
     self.assertEqual(creds._signer, signer)
     # Serialize over-ridden data (unrelated to ``creds``).
     to_serialize = {'unrelated': 'data'}
     serialized_str = creds._to_json([], to_serialize.copy())
     serialized_data = json.loads(serialized_str)
     expected_serialized = {
         '_class': 'ServiceAccountCredentials',
         '_module': 'oauth2client.service_account',
         'token_expiry': None,
     }
     expected_serialized.update(to_serialize)
     self.assertEqual(serialized_data, expected_serialized)
예제 #23
0
def get_for_service_account_p12(client_email, private_key_path, scope=None):
    """Gets the credentials for a service account with PKCS12 / p12 key.

    .. note::
      This method is not used by default, instead :func:`get_credentials`
      is used. This method is intended to be used when the environment is
      known explicitly and detecting the environment implicitly would be
      superfluous.

    :type client_email: string
    :param client_email: The e-mail attached to the service account.

    :type private_key_path: string
    :param private_key_path: The path to a private key file (this file was
                             given to you when you created the service
                             account). This file must be in P12 format.

    :type scope: string or tuple of string
    :param scope: The scope against which to authenticate. (Different services
                  require different scopes, check the documentation for which
                  scope is required for the different levels of access to any
                  particular API.)

    :rtype: :class:`oauth2client.service_account.ServiceAccountCredentials`
    :returns: A new ``ServiceAccountCredentials`` instance with the
              needed service account settings.
    """
    return ServiceAccountCredentials.from_p12_keyfile(
        client_email, private_key_path, scopes=scope)
예제 #24
0
    def __init__(self, account_json):

        scopes = ['https://www.googleapis.com/auth/ndev.clouddns.readwrite']
        credentials = ServiceAccountCredentials.from_json_keyfile_name(account_json, scopes)
        self.dns = discovery.build('dns', 'v1', credentials=credentials, cache_discovery=False)
        with open(account_json) as account:
            self.project_id = json.load(account)['project_id']
예제 #25
0
파일: utils.py 프로젝트: OpenCanada/website
def get_service(api_name, api_version, scopes, key_file_location, service_account_email):
    """Get a service that communicates to a Google API.

    Args:
    api_name: The name of the api to connect to.
    api_version: The api version to connect to.
    scope: A list auth scopes to authorize for the application.
    key_file_location: The path to a valid service account p12 key file.
    service_account_email: The service account email address.

    Returns:
    A service that is connected to the specified API.
    """

    credentials = ServiceAccountCredentials.from_p12_keyfile(
        service_account_email,
        key_file_location,
        scopes=scopes
    )

    http = credentials.authorize(httplib2.Http())

    # Build the service object.
    service = build(api_name, api_version, http=http)

    return service
예제 #26
0
파일: rank2sheet.py 프로젝트: sunyo2k/MyDev
def write_sheet(keyword, rows):
  credentials = ServiceAccountCredentials.from_json_keyfile_name(G_SERCRET, G_SCOPES)
  service = discovery.build('sheets', 'v4', credentials=credentials)
  spreadsheet_id = G_SHEET_ID

  rangeName = keyword + '!A1:A'
  result = service.spreadsheets().values().get(spreadsheetId=spreadsheet_id, range=rangeName).execute()
  values = result.get('values', [])
  curRowsLen =len(values)

  if not values:
    print('sheet has no data')

  batch_update_values_request_body = {
    "valueInputOption": "USER_ENTERED",
    "data": [
      {
        "range": keyword + "!A" + str(curRowsLen+1) + ":E" + str(curRowsLen + len(rows)),
        "majorDimension": "ROWS",
        "values": rows
      }
    ]
  }

  #pprint(batch_update_values_request_body)
  request = service.spreadsheets().values().batchUpdate(spreadsheetId=spreadsheet_id,body=batch_update_values_request_body)
  response = request.execute()

  # TODO: Change code below to process the `response` dict:
  pprint(response)
  return response
def get_sheet_data_from_url(sheet_url):
    "get the data for the sheet URL as CSV"
    #TODO: copy code name version? maybe just toss the name version
    import gspread
    from oauth2client.service_account import ServiceAccountCredentials

    # see https://www.twilio.com/blog/2017/02/an-easy-way-to-read-and-write-to-a-google-spreadsheet-in-python.html

    # use creds to create a client to interact with the Google Drive API
    scope = ['https://spreadsheets.google.com/feeds']

    # follow the instructions in the blog above carefully to get this.
    creds = ServiceAccountCredentials.from_json_keyfile_name('/home/matz/DCSFetchMarks-3cf40810a20f.json', scope)

    client = gspread.authorize(creds)
    # Find the workbook by URL and open the first sheet
    try:
        work_book = client.open_by_url(sheet_url)
        assert work_book
        sheet = work_book.sheet1
        assert sheet
        csv_file_data = sheet.export(format='csv')
        assert csv_file_data
        return csv_file_data
    except:
        import traceback,sys
        print("failed to open sheet", sheet_url)
        traceback.print_exc(file=sys.stdout)
        exit(2)
def lambda_handler(event, context):
    global config

    # Load the configuration (s3_bucket, s3_key, sheet_id)
    with open('config.json') as data_file:
        config = json.load(data_file)

    # Connect to Google Sheets and open the sheet
    # Ensure the sheet is shared with the service
    # account email address ([email protected])
    scopes = ['https://spreadsheets.google.com/feeds']
    credentials = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scopes=scopes)
    gc = gspread.authorize(credentials)
    sheet = gc.open_by_key(config['sheet_id']).worksheet(config['worksheet_name'])

    # Get the values
    gval = sheet.range("A1:B" + str(sheet.row_count))
    data = {}

    # Get every key => value for A => B (If A is not blank)
    for i in range(sheet.row_count):
        if i % 2 == 0 and gval[i].value != '':
            data[gval[i].value] = str(gval[i + 1].value)

    # Encode into JSON
    jsonstr = json.dumps(data)

    # Print or upload to S3
    if debug:
        print jsonstr
    else:
        return upload_to_s3(jsonstr)

    return
예제 #29
0
 def __init__(self, credential_path, spreadsheet_name):
     scope = ['https://spreadsheets.google.com/feeds']
     credentials = ServiceAccountCredentials.from_json_keyfile_name(credential_path, scope)
     self.gc = gspread.authorize(credentials)
     logging.info('Sheet service client authorized, credential path: %s' % credential_path)
     self.spreadsheet = self.gc.open(spreadsheet_name)
     pass
def go(startdate, enddate):
    credentials = ServiceAccountCredentials.from_json_keyfile_name('daily_goals.json', ['https://spreadsheets.google.com/feeds'])
    gc = gspread.authorize(credentials)
    wks = gc.open("Daily Goals").worksheet("Sheet1")
    rows = wks.get_all_values()

    startdate = datetime.datetime.strptime(startdate, '%Y-%m-%d')
    enddate = datetime.datetime.strptime(enddate, '%Y-%m-%d')

    results = {}
    results['rows'] = []

    def str2num(val):
        if not val:
            return 0
        else:
            return float(val)

    for row in rows:
        date_str, wateroz, vitamins, scripture_study, exercised, pullups, divebombpushups, calories, sevenminuteworkout, weight, sat_fat_grams, sol_fiber_grams, hours_slept, servings_fruit_veg = row
        try:
            dateobj = datetime.datetime.strptime(date_str.split(' ')[0], '%Y-%m-%d')
        except ValueError:
            continue
        dateobj = dateobj - datetime.timedelta(days=1)
        if (startdate <= dateobj <= enddate) and (dateobj.weekday() != 6):
            results['rows'].append({'date_str': dateobj.date().strftime('%Y-%m-%d'),
                'physical_activity_description': 'walking',
                'activity_minutes': exercised,
                'water_5_or_more_cups': (str2num(wateroz)/8) >= 5,
                'fruit_veg_4_or_more_servings': str2num(servings_fruit_veg) >= 4,
                'sleep_7_or_more_hours': str2num(hours_slept) >= 7})
    print(json.dumps(results))
예제 #31
0
scopes = ['https://www.googleapis.com/auth/analytics.readonly']

json_data = {
    "type": "service_account",
    "project_id": "ga-reporting-",
    "private_key_id": "",
    "private_key": "",
    "client_email": "",
    "client_id": "",
    "auth_uri": "https://accounts.google.com/o/oauth2/auth",
    "token_uri": "https://accounts.google.com/o/oauth2/token",
    "auth_provider_x509_cert_url": "",
    "client_x509_cert_url": ""
}

credentials = ServiceAccountCredentials._from_parsed_json_keyfile(
    keyfile_dict=json_data, scopes=scopes)


class SampledDataError(Exception):
    pass


date = (datetime.datetime.now() -
        datetime.timedelta(days=1)).strftime('20%y-%m-%d')
date_ranges = [(date, date)]


def main(argv, metrics, dimensions, sort, profileId, profileName, writer,
         logger):
    service, flags = sample_tools.init(
        argv,
예제 #32
0
import datetime
import requests, gspread, string
from oauth2client.service_account import ServiceAccountCredentials
import readKeysLib

# CE SCRIPT RECOPIE LES TARGETS EXPORTEES PAR YATA AU FORMAT .json DANS la spreadsheet TornStats (sheet Targets)
# Ce fichier targets.json doit être placé dans le dossier ..../torn/files !

# APIKeys and sheetKeys are saved in files in an external repertory see the module readKeysLib
APIKey_dict, sheetKey_dict = readKeysLib.getDicts()
repertory = sheetKey_dict['rep']
# Get authorization for gspread and open worksheet
scope = ['https://spreadsheets.google.com/feeds']
json_keyfile = repertory + sheetKey_dict['jsonKey']
credentials = ServiceAccountCredentials.from_json_keyfile_name(
    json_keyfile, scope)
gc = gspread.authorize(credentials)
sheetKey = sheetKey_dict['TornStats']

print('-> opening sheet')
ws = gc.open_by_key(sheetKey).worksheet('Targets')
print('-> sheet opened')
# ws.update_cell(10,1,currentDateGspread)
# ws.update_cell(11,1,currentDateGspread+0.5)
# previousDateGspreadStr = ws.cell(12,1).value

# Lecture du fichier
targets_dict = readKeysLib.getYATAtargets()
print(f'-> {len(targets_dict)} targets found')
print(targets_dict)
#print(f'total_ws : {total_ws:.2f}, model: {total_ws_model:.2f}, delta: {delta:.2f} %')
예제 #33
0
    search(textbox, 'Отметки:', 'Отметки:')
    search(textbox, 'Отсутствующие:', 'Отсутствующие:')
    search(textbox, 'Другое:', 'Другое:')
    search(textbox, 'Передан', 'Передан')
    search(textbox, 'через:', 'через:')
    search(textbox, 'в:', 'в:')
    search(textbox, 'Кабинет:', 'Кабинет:')
    table = Table(root, headings=('Класс', 'Кабинет'), rows=out)
    table.pack(expand=tk.YES, fill=tk.BOTH)


scope = [
    'https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive'
]
credentials = ServiceAccountCredentials.from_json_keyfile_name(
    'C:/key.json', scope)
gc = gspread.authorize(credentials)
wks = gc.open_by_key('1PmA0BSTfaHZIBoDC7G2kmed5nEheBIo4NNdBp1LJ410').sheet1
#add_one()
#get_all()
root = tk.Tk()
root.title("Где журнал? - Сервер")
textbox = Text(root)

logb = Button(root, text="Перемещение журнала")
textbox.pack()
logb.pack()
logb.bind('<Button-1>', get_all)
textbox.tag_config('False', background='red')
textbox.tag_config('True', background='blue')
textbox.tag_config('Имя:', background='yellow')
예제 #34
0
class TaiwanBotSheet:

    scope = [
        'https://spreadsheets.google.com/feeds',
        'https://www.googleapis.com/auth/drive'
    ]
    # creds = ServiceAccountCredentials.from_json_keyfile_name('creds.json', scope)
    service_account_info_dict = json.loads(CONFIG.GOOGLE_SERVICE_ACCOUNT,
                                           strict=False)
    creds = ServiceAccountCredentials.from_json_keyfile_dict(
        service_account_info_dict, scope)
    client = gspread.authorize(creds)
    context = SpreadsheetContext.GENERAL

    def __init__(self, context=SpreadsheetContext.GENERAL):
        _logger.info('Initiating TaiwanBotSheet')
        self.context = context

    def get_questions_answers(self, context=None):
        if context is None:
            context = self.context

        spreadsheet = self.client.open(SPREADSHEET_FAQ_FILE)
        sheet = spreadsheet.worksheet(CONTEXTS[context]["sheet"])
        # Each question can potentially be multiple lines
        question_multiples = list(
            map(str.splitlines, list(map(str.strip,
                                         sheet.col_values(1)[1:]))))
        non_duplicated_answers = list(map(str.strip, sheet.col_values(2)[1:]))

        # The end result is duplicate answers for each row in the spreadsheet
        # that has multiple questions
        questions = []
        answers = []
        for index in range(len(question_multiples)):
            question_multiple = question_multiples[index]
            answer = non_duplicated_answers[index]
            for question in question_multiple:
                questions.append(question)
                answers.append(answer)

        return [questions, answers]

    def log_answers(self, user_question, similar_question, answer, score,
                    state):
        sheet = self.client.open(SPREADSHEET_LOG_FILE).worksheet(
            CONTEXTS[self.context]["sheet"])
        next_row = len(sheet.get_all_values()) + 1
        sheet.update('A' + str(next_row),
                     datetime.now().strftime("%d/%m/%Y %H:%M:%S"))
        sheet.update('B' + str(next_row), user_question)
        sheet.update('C' + str(next_row), similar_question)
        sheet.update('D' + str(next_row), answer)
        sheet.update('E' + str(next_row), score)
        sheet.update('F' + str(next_row), state)

    def get_context(self):
        return CONTEXTS[self.context]

    def set_context(self, context):
        if context in CONTEXTS:
            self.context = context
        else:
            _logger.error(
                "This context type does not exist. Setting the GENERAL context instead..."
            )
            self.context = SpreadsheetContext.GENERAL
예제 #35
0
#------------------
import mongo_classifier  # this step is necessary to update the classifier data

load_file = 'trained_classifier.sav'
loaded_classifier = pickle.load(open(load_file, "rb"))

level = 1  # 1: H/nH   2: H=2/ N=1/ F=3
eval_sheet = "Check evaluation_test"

head_ind = 8

scope = [
    'https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive'
]
creds = ServiceAccountCredentials.from_json_keyfile_name(
    'C:/Keys/mongoDB_secret.json', scope)
client = gspread.authorize(creds)
sheet = client.open("Checks list").sheet1

sheet_g = Spread(eval_sheet)
sheet_g.open_sheet("Sheet1", create=False)
sheet_g.sheet.resize(rows=head_ind)

headers = sheet.row_values(head_ind)
all_values = sheet.get_all_values()
check_SQL = pd.DataFrame(all_values, columns=headers)

all_checks = list(check_SQL.loc[range(head_ind, len(check_SQL)),
                                'description'].unique())

#====== validation loop
예제 #36
0
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd
from time import sleep

# use creds to create a client to interact with the Google Drive API
scope = [
    'https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive'
]
creds = ServiceAccountCredentials.from_json_keyfile_name(
    'Skills-Comparison.json', scope)
client = gspread.authorize(creds)

# Find a workbook by name and open the first sheet
sheet = client.open("Skills-Comparison-Spreadsheet").sheet1

statNames = sheet.col_values(1)[1:]
names = sheet.row_values(1)[1:]
stats = []
# Gets stats from each row
i = 2
while i <= 55:
    stats.append(sheet.row_values(i)[1:])
    # Sleeps to stop google from s******g itself over to many queries per second
    sleep(.15)
    i += 1

# Make sure you use the right name here.
sheet = client.open("Skills-Comparison-Spreadsheet").sheet1
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from pprint import pprint

scope = [
    "https://spreadsheets.google.com/feeds",
    'https://www.googleapis.com/auth/spreadsheets',
    "https://www.googleapis.com/auth/drive.file",
    "https://www.googleapis.com/auth/drive"
]

creds = ServiceAccountCredentials.from_json_keyfile_name(
    "credentials.json", scope)

client = gspread.authorize(creds)

sheet = client.open("TrackRev").sheet1  # Open the spreadhseet

sheet.clear()  # Clear existing data


# Helper function for writing data based on header flag or not
def writeData(theData, headerFlag):
    if (headerFlag == 0):
        sheet.append_row(theData)
    else:
        sheet.append_row(['.'] + theData)
예제 #38
0
 def __init__(self):
     self.key = '/Users/enrique/Desktop/key.json'
     self.scopes = 'https://www.googleapis.com/auth/androidpublisher'
     self.credentials = credentials = ServiceAccountCredentials.from_json_keyfile_name(self.key, scopes=self.scopes)
예제 #39
0
파일: app.py 프로젝트: chungoppa/007
# import locale
from werkzeug.middleware.proxy_fix import ProxyFix
from linebot import (
    LineBotApi, WebhookHandler
)
from linebot.exceptions import (
    InvalidSignatureError,
    LineBotApiError)

app = Flask(__name__)
app.wsgi_app = ProxyFix(app.wsgi_app, x_for=1, x_host=1, x_proto=1)

# spreadsheet
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('dumbbot-8fad35afb0c6.json', scope)
client = gspread.authorize(creds)
mainsheet = client.open('SGVN')
sheet = client.open('SGVN').sheet1
tmpordersheet = mainsheet.worksheet('tmporder')
tmpuserinfo = mainsheet.worksheet(('tmpuserinfo'))
ordersheet = mainsheet.worksheet('orders')
userinfosheet = mainsheet.worksheet('userinfo')
reportReceiver = mainsheet.worksheet('reportReceiver')

# urllib3 -
http = urllib3.PoolManager()
# token
# line_bot_api = LineBotApi('bOiXla2lbcGsYnZkXnhxOAkyAzuGTSDrGVZGF/hrMjlws0+DhIoFq8i9f3xjR8DHmR6KqVpU/UW+SR8yAKDyt/PEecZg5jU9AjAIPQBvYpZRrQPrzWVQCmd10l8q4E0q17mtskg/bljPsPxPFSUj9QdB04t89/1O/w1cDnyilFU=')
line_bot_api = LineBotApi('cX51Ve+hutrgp3yj8vU0+HzTgfDT3v5vJm8Z8ZswRLI09+tqBp3KzUA+wXyOiR3GovF0UEd5yip6Jfjw5gdUPv4jYWIjsvJNxifxwuM/S9LhVSbZcZCW7lREgwXT3/Zt9KNENifbpWQ8qCKRW+txiAdB04t89/1O/w1cDnyilFU=')
예제 #40
0
import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Configurando a Integração
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('Control/static/credential/entregatecnica.json', scope)
gc = gspread.authorize(credentials)
wks = gc.open_by_key('1yIwqAc7yEedIvqYRaFrjv8p4IcJb4OVez4xOPOubqxg')
worksheet = wks.get_worksheet(0)

# Pega todos os dados da tabela:
def Todos():
    list_of_lists = worksheet.get_all_values()
    return list_of_lists
예제 #41
0
from django.shortcuts import render
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from django.contrib.auth.models import User
from ast import literal_eval
from .models import Dues
import os

# Create your views here.

scope = [
    'https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive'
]

credentials = ServiceAccountCredentials.from_json_keyfile_name(
    os.environ.get('CLIENT_SECRET'), scope)
# os.environ['CLIENT_SECRET']

gc = gspread.authorize(credentials)
# wks = gc.open((os.environ.get('WKS_NAME'))).sheet1
wks = gc.open((os.environ.get('WKS_NAME'))).get_worksheet(0)

sheet1 = wks.get_all_records()
# wks.find(user_email)                       find a string
# values_list = worksheet.col_values(1)      all values from column 1
EmailColumn = wks.col_values(1)


def index(request):
    email = request.user.email
    announcement = Dues.objects.all()
예제 #42
0
def auth_gss_client(path, scopes):
    credentials = ServiceAccountCredentials.from_json_keyfile_name(
        path, scopes)
    return gspread.authorize(credentials)
import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Open a worksheet from spreadsheet with one shot
#(temp) wks = gc.open("Where is the money Lebowski?").sheet1

#(temp) wks.update_acell('B2', "it's down there somewhere, let me take another look.")

# Fetch a cell range
#(temp) cell_list = wks.range('A1:B7')
# use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)
gc = gspread.authorize(creds)

# Find a workbook by name and open the first sheet
# Make sure you use the right name here.
#(temp) sheet = client.open("Survey_Project_Responses").sheet1

sh = gc.create('This is just a test')

# But that new spreadsheet will be visible only to your script's account.
# To be able to access newly created spreadsheet you *must* share it
# with your email. Which brings us to sharing a spreadsheet 

sh.share('*****@*****.**', perm_type='user', role='writer')
예제 #44
0
port = skt.bind_to_random_port("tcp://*")

input="tcp://%s:%d" % (fqdn, port)

ctrl = os.fdopen(3, 'w')
ctrl.write("INIT\n")
ctrl.write("INPUT:input:%s\n" % input)
ctrl.write("RUNNING\n")
ctrl.flush()

############################################################################

# Creds
scopes = ['https://www.googleapis.com/auth/bigquery']

credentials = ServiceAccountCredentials.from_json_keyfile_name(private,
                                                               scopes=scopes)

if project == None:
    project = json.loads(open(private,'r').read())['project_id']

http = Http()
http_auth = credentials.authorize(http)
service = build('bigquery', 'v2', http=http_auth)
tables = service.tables()
tabledata = service.tabledata()
jobs = service.jobs()

############################################################################

# Create table if not exists.
def update_sheet(source_link, sheet_name):
    today = datetime.datetime.now()
    yesterday_full_Date = (today - datetime.timedelta(1)).strftime("%d %B, %Y")
    today_time_format = today.strftime("%b/%d/%Y %H:%M")
    yesterday_dayno = (today - datetime.timedelta(1)).strftime("%d")


    print("Today is ", today_time_format)

    scrapper_end_time = (today - datetime.timedelta(1)).strftime("%b/%d/%Y 21:00")
    scrapper_start_time = (today - datetime.timedelta(2)).strftime("%b/%d/%Y 21:00")
    #
    # print("Start Time : ", scrapper_start_time)
    # print("End Time : ", scrapper_end_time)

    print()
    print("----- Start -----")
    print()
    # Test
    # todays_month = 'Sep'
    # todays_day = '15'
    # Test


    scope = ["https://spreadsheets.google.com/feeds", 'https://www.googleapis.com/auth/spreadsheets',
             "https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive"]
    creds = ServiceAccountCredentials.from_json_keyfile_name("CodeforcesAutoTracker-b2030a7afa6c.json", scope);
    client = gspread.authorize(creds)

    sheet = client.open("Codeforces Auto Tracker - Akif Islam").worksheet(sheet_name)
    data = sheet.get_all_records()
    # pprint(data)
    date_column = sheet.col_values(1)
    no_of_total_submission_column = sheet.col_values(2)
    no_of_total_accepted_column = sheet.col_values(3)

    source = requests.get(source_link).text
    soup = BeautifulSoup(source, "lxml").find('table', class_="status-frame-datatable")

    submission_time = []

    # 1. Collecting all dates from 50 submission of First Page of Codeforces Submission
    for data in soup.findAll('span', class_="format-time"):
        datetime_object = datetime.datetime.strptime(data.text, "%b/%d/%Y %H:%M")
        submission_time.append(datetime_object.strftime("%b/%d/%Y %H:%M"))


    print(" ALL OK !")

    print()

    # Execution
    submission_count = int(0)
    total_accepted = []
    accepted_count = int(0)
    accpeted_nonduplicate_set = []

    # Total Accepted Count from 50s :
    for data in soup.findAll('span', class_="submissionVerdictWrapper"):
        total_accepted.append(data.text)

    # print(total_accepted)
    # print("Found: ",len(total_accepted))
    # print("Found : ",len(submission_time))
    # print(total_accepted)

    #Total Submission Count
    for i in range(0,len(submission_time),1):
        if submission_time[i]>scrapper_start_time and submission_time[i]<scrapper_end_time:
            submission_count += 1

            if(total_accepted[i]== "Accepted" or total_accepted[i].__contains__("Pretest Passed")):
                str = get_problemlist(source_link)[i] + "  Accepted"
                accpeted_nonduplicate_set.append(str)



        # Total Submission Count
    accpeted_nonduplicate_set = set(accpeted_nonduplicate_set)
    # print("Accepted List : ",accpeted_nonduplicate_set)

    accepted_count = len(accpeted_nonduplicate_set)
    print("Total Submission : ", submission_count)
    print("Total Accepted : ", accepted_count)
    insert_list = [yesterday_full_Date, submission_count, accepted_count]
    # print(insert_list)
    previous_date = sheet.cell(len(date_column), 1).value[0:2]

    if previous_date != yesterday_dayno:
        sheet.insert_row(insert_list, (len(date_column) + 1))

    else:
        print("Duplicate Date Found ! ")


    print(sheet_name + " data checked successfully !")
    print("----- Finished !-----")
    print()
예제 #46
0
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from json import load


def getTokens():
    return load(open('res/TOKENS.json', 'r'))


def slack_tokens():
    return load(open('res/slackTokens.json', 'r'))


scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name(
    'res/credentials.json', scope)  # hidden, it's a secret

client = gspread.authorize(creds)

sheet = client.open_by_url(getTokens()["sheet_url"]).worksheet(
    'Sheet1')  # till here all gspread API stuff


def updateSheet(updateList):
    """
		I must say this, append_row saved about 40 lines of code, and is more efficient.
		Should have found about this when Sauron was made. Meh, better late than never.
	"""
    sheet.append_row(updateList)

예제 #47
0
# -*- coding: utf-8 -*-
from __future__ import unicode_literals
# @Author: chengaoxing
# @Date:   2017-08-14 10:06:29
# @Last Modified by:   chengaoxing
# @Last Modified time: 2017-08-14 11:36:24
# Import the Earth Engine Python Packages
import ee

from oauth2client.service_account import ServiceAccountCredentials

client_email = '*****@*****.**'
scopes = ['https://www.googleapis.com/auth/sqlservice.admin']

credentials = ServiceAccountCredentials.from_p12_keyfile(
    client_email, '/Users/chengaoxing/chen-pku-app-dbdcb868ef05.p12', scopes=scopes)

# import ee.mapclient

# Initialize the Earth Engine object, using the authentication credentials.
ee.Initialize()

# Print the information for an image asset.
image = ee.Image('srtm90_v4')
path = image.getDownloadURL()
# create the vizualization parameters
# viz = {'min':0.0, 'max':4000, 'palette':"000000,0000FF,FDFF92,FF2700,FF00E7"};

# # display the map
# ee.mapclient.addToMap(image,viz, "mymap")
예제 #48
0
class GSdata():
    scope = ["https://spreadsheets.google.com/feeds",
    'https://www.googleapis.com/auth/spreadsheets',
    "https://www.googleapis.com/auth/drive.file",
    "https://www.googleapis.com/auth/drive"]

    creds = ServiceAccountCredentials.from_json_keyfile_name("GoodsManager.json", scope)


    def __init__(self,product_name = 'Sheet1'):

        self.ManagerName_cell = 'C4'
        self.place_cell = 'C6'
        self.product_name_cell = 'C8'
        self.tax_indentity_cell = 'F5'
        self.Datetime = datetime.now().strftime("%Y-%m-%d")
        self.gc = gspread.authorize(GSdata.creds)
        self.sheet = self.gc.open('GOODS2')

        ### loop to find desire worksheet
        self.worksheet = self.sheet.worksheets()

        self.product_name = product_name

        if self.__Check_existing():
            Feedback = 'ไม่มี Product {} อยู่ขณะนี้ ทำการสร้างตารางใหม่ ?(y/n)'.format(self.product_name)
            Input = input(Feedback)
            if Input == 'y':
                self.create_stock()
            else :
                print('Good Luck')


        ## check if product exist or not
    def __Check_existing(self):
        for i in self.worksheet:
            print(i.title)
            if i.title == self.product_name:
                self.worksheet = self.sheet.worksheet(i.title)
                return False
        return True


        ## create new worksheet
    def create_stock(self,Manager_name = 'Pybott',address_name= 'bangkok',tax_iden= '10010'):

        self.sheet.worksheet('Sheet1').duplicate(new_sheet_name = self.product_name)
        ## set current worksheet
        self.worksheet = self.sheet.worksheet(self.product_name)

        self.worksheet.update_acell(self.ManagerName_cell,Manager_name)

        self.worksheet.update_acell(self.place_cell,address_name)

        self.worksheet.update_acell(self.product_name_cell,self.product_name)

        self.worksheet.update_acell(self.tax_indentity_cell,tax_iden)


        ## add new incoming data
    def add_product_data(self,number_of_item ,product_number , method = 'รับ',note = None):
        values_list = self.worksheet.col_values(2)
        current_row = len(values_list) + 1

        current_row_num = 'B'+str(current_row)
        self.worksheet.update_acell(current_row_num,product_number)

        current_row_date = 'C'+str(current_row)
        self.worksheet.update_acell(current_row_date,self.Datetime)


        if method == 'รับ':
            current_row_receive = 'D'+str(current_row)
            self.worksheet.update_acell(current_row_receive,number_of_item)

        else :
            current_row_dis = 'E'+str(current_row)
            self.worksheet.update_acell(current_row_dis,number_of_item)
        
        if note is not None:
            current_row_note = 'G'+str(current_row)
            self.worksheet.update_acell(current_row_note,note)

        return 'You have updated your products amount'
    

    # ลบสินค้า 
    def delete_product_data(self,product_number):
        cell = self.worksheet.find(str(product_number))
        current_row_num = 'B'+str(cell.row)
        self.worksheet.update_acell(current_row_num,'')

        current_row_date = 'C'+str(cell.row)
        self.worksheet.update_acell(current_row_date,'')

        current_row_receive = 'D'+str(cell.row)
        self.worksheet.update_acell(current_row_receive,'')

        current_row_dis = 'E'+str(cell.row)
        self.worksheet.update_acell(current_row_dis,'')

        current_row_note = 'G'+str(cell.row)
        self.worksheet.update_acell(current_row_note,'')


        return 'You have delete your products amount'
예제 #49
0
def run(job=None, logger=None, **kwargs):
    """
    """
    db_name = DB_NAME
    instance_name = db_name

    environment = Environment.objects.get(id=ENVIRONMENT)
    rh = environment.resource_handler.cast()
    assert isinstance(rh, GCPHandler)
    project = environment.gcp_project
    region = GCP_REGION
    set_progress("REGION: %s" % GCP_REGION)

    try:
        CustomField.objects.get_or_create(
            name="gcp_sql_rh_id",
            label="Google RH ID",
            type="STR",
            description="Used by the Google SQL blueprint",
            show_as_attribute=True,
        )
    except IntegrityError:
        # IntegrityError: (1062, "Duplicate entry 'google_rh_id' for key 'name'")
        pass

    try:
        CustomField.objects.get_or_create(
            name="gcp_sql_instance_name",
            label="Google instance identifier",
            type="STR",
            description="Used by the Google Cloud SQL blueprint",
            show_as_attribute=True,
        )
    except IntegrityError:
        # IntegrityError: (1062, "Duplicate entry 'db_identifier' for key 'name'")
        pass

    try:
        CustomField.objects.get_or_create(
            name="gcp_sql_project",
            label="Google project",
            type="STR",
            description="Used by the Google Cloud SQL blueprint",
            show_as_attribute=True,
        )
    except IntegrityError:
        # IntegrityError: (1062, "Duplicate entry 'db_identifier' for key 'name'")
        pass

    resource = kwargs.get("resource")
    resource.name = "Google SQL - " + instance_name
    resource.gcp_sql_instance_name = instance_name
    # Store the resource handler's ID on this resource so the teardown action
    # knows which credentials to use.
    resource.gcp_sql_rh_id = rh.id
    resource.gcp_sql_project = project
    resource.save()

    try:
        account_info = json.loads(
            rh.gcp_projects.get(id=project).service_account_info)
    except Exception:
        account_info = json.loads(
            rh.gcp_projects.get(id=project).service_account_key)

    credentials = ServiceAccountCredentials.from_json_keyfile_dict(
        account_info)

    job.set_progress(
        "Connecting to Google Cloud through service account email {}".format(
            account_info["client_email"]))
    set_progress("RH: %s" % rh)

    service_name = "sqladmin"
    version = "v1beta4"
    client = build(service_name, version, credentials=credentials)

    set_progress("Connection established")

    try:
        inst_data = client.instances().list(project=project).execute()

        if "items" in inst_data:
            instance_names = [inst["name"] for inst in inst_data["items"]]
            if instance_name in instance_names:
                return (
                    "ERROR",
                    'Server instance "%s" already exists' % instance_name,
                    "",
                )
    except HttpError as e:
        client_username = account_info["client_email"].split("@")[0]
        return (
            "ERROR",
            "Server instance {instance_name} could not be created ({reason}), make sure that this ResourceHandler's service account ({service_account_name}) is given the Cloud SQL Admin Permission"
            .format(
                instance_name=instance_name,
                reason=str(e),
                service_account_name=client_username,
            ),
            e,
        )

    set_progress("\nCreating instance...")

    body = {
        "kind": "sql#instance",
        "name": instance_name,
        "project": project,
        "region": region,
        "databaseVersion": DB_VERSION,
        "settings": {
            "tier": "db-n1-standard-1"
        },
    }
    result = client.instances().insert(project=project, body=body).execute()

    # Wait the server instance to be created:
    while True:
        inst_data = client.instances().list(project=project).execute()
        status = None
        for inst in inst_data["items"]:
            if inst["name"] == instance_name:
                status = inst["state"]
                break
        set_progress("Status of the server instance is: %s" % status)
        if status == "RUNNABLE":
            break
        time.sleep(2)

    set_progress("\nNow attempting to create a new database...")

    body = {
        "kind": "sql#database",
        "name": db_name,
        "project": project,
        "instance": instance_name,
    }

    result = (client.databases().insert(project=project,
                                        instance=instance_name,
                                        body=body).execute())
    assert result["status"] == "DONE"

    set_progress("Database %s is now available on instance: %s" %
                 (db_name, instance_name))
예제 #50
0
def load_credentials():
    scope = ['https://spreadsheets.google.com/feeds']
    credentials = ServiceAccountCredentials.from_json_keyfile_name(
        'files/service-account-key.json', scope)
    return credentials
예제 #51
0
    def load(self):
        """
        Load table data from a Google Spreadsheet.

        This method consider :py:attr:`.source` as a path to the
        credential JSON file to access Google Sheets API.

        The method automatically search the header row start from
        :py:attr:`.start_row`. The condition of the header row is that
        all of the columns have value (except empty columns).

        :return:
            Loaded table data. Return one |TableData| for each sheet in
            the workbook. The table name for data will be determined by
            :py:meth:`~.GoogleSheetsTableLoader.make_table_name`.
        :rtype: iterator of |TableData|
        :raises pytablereader.InvalidDataError:
            If the header row is not found.
        :raises pytablereader.OpenError:
            If the spread sheet not found.
        """

        import gspread
        from oauth2client.service_account import ServiceAccountCredentials

        self._validate_table_name()
        self._validate_title()

        scope = ['https://spreadsheets.google.com/feeds']
        credentials = ServiceAccountCredentials.from_json_keyfile_name(
            self.source, scope)

        gc = gspread.authorize(credentials)
        try:
            for worksheet in gc.open(self.title).worksheets():
                self._worksheet = worksheet
                self.__all_values = [row for row in worksheet.get_all_values()]

                if self._is_empty_sheet():
                    continue

                try:
                    self.__strip_empty_col()
                except ValueError:
                    continue

                value_matrix = self.__all_values[self._get_start_row_idx():]
                try:
                    header_list = value_matrix[0]
                    record_list = value_matrix[1:]
                except IndexError:
                    continue

                self.inc_table_count()

                yield TableData(self.make_table_name(),
                                header_list,
                                record_list,
                                quoting_flags=self.quoting_flags)
        except gspread.exceptions.SpreadsheetNotFound:
            raise OpenError("spreadsheet '{}' not found".format(self.title))
예제 #52
0
#log part
try:
    logging.basicConfig(filename='/home/pi/Documents/logs/' + sys.argv[1] +
                        '.log',
                        format='%(asctime)s %(message)s',
                        datefmt='%d/%m/%Y %I:%M:%S %p',
                        level=logging.INFO)
except:
    print "The name of the log file! pass it as an argument"
#debug / info / warning
logging.info('New beginning')

#gspread part
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name(
    '/home/pi/techlab-tag-nfc-b3f2a2929d98.json', scope)
gc = gspread.authorize(credentials)
logging.info(gc)
sh = gc.open_by_url(
    'https://docs.google.com/spreadsheets/d/1KWxCi7tny8uxo4TmzjNnVuNj5eGRVngwFD2gxIX5qfw/edit?usp=sharing'
)
worksheet = sh.worksheet("soci")
worksheet_log = sh.worksheet("log_laser")
cellTag = ""
Cr = 0
Sk = 0

#serial part
ser = serial.Serial('/dev/ttyAMA0', 115200, timeout=1)
logging.info(ser)
예제 #53
0
def initialize_analyticsreporting() -> Resource:
    credentials = ServiceAccountCredentials.from_json_keyfile_name(
        os.environ['KEY_FILE_LOCATION'],
        scopes='https://www.googleapis.com/auth/analytics.readonly')
    analytics = build('analyticsreporting', 'v4', credentials=credentials)
    return analytics
import time

import gspread
import matplotlib.pyplot as plt
from oauth2client.service_account import ServiceAccountCredentials

scope = [
    'https://www.googleapis.com/auth/devstorage.read_write',
    'https://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive'
]

credentials = ServiceAccountCredentials.from_json_keyfile_name(
    'Google Sheet-80a12669b8a0.json', scope)

if __name__ == '__main__':
    while True:
        try:
            auth = gspread.authorize(credentials)

            google_sheet = auth.open(
                "WorldCup2015_IndiaIndividualScores").sheet1

            data = google_sheet.get_all_values()

            matches = []
            scores = []

            for match in data:
                bat_first = match[1].split()[1]
예제 #55
0
 def __init__(self, spreadsheet, worksheet):
     scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
     self.credentials = ServiceAccountCredentials.from_json_keyfile_name('google_client_secret.json', scope)
     self.client = gspread.authorize(self.credentials)
     self.spreadsheet = self.client.open(spreadsheet)
     self.worksheet = self.spreadsheet.worksheet(worksheet)
예제 #56
0
bat_usd105 = "Ticker (BAT/USD)"
bat = (myapi.ticker("BAT/USD")['last'])
#----------LTC/USD-----------#
ltc_usd105 = "Ticker (LTC/USD)"
lt1 = (myapi.ticker("LTC/USD")['last'])
#----------BCH/USD-----------#
bch_usd105 = "Ticker (BCH/USD)"
bc1 = (myapi.ticker("BCH/USD")['last'])

#print ("CEX:" + " "+ G + btc_usd659, O + last_price, G + etc_usd103, O + et1, G + xlm_usd315, O + xl1, G + dash_usd886, O + da1, G + xrp_usd237, O + xr1, G + ltc_usd105, O + lt1)

##########################################################################################################>
### Start of the Google Sheets Api Script  | Documentation https://gspread.readthedocs.io/en/latest/
## https://www.techwithtim.net/tutorials/google-sheets-python-api-tutorial/  |  https://www.youtube.com/watch?v=cnPlKLEGR7E
scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("/home/kali/Desktop/Projects/everything_python/Cryptozoom/creds.json", scope)
client = gspread.authorize(creds)

#------------------------------------------------------------------ Google Sheets API Primer --------------------------------------------------------------------------------------------#
                                                          # How to use the sheets api will be placed here
#----------------------------------------------------------------------------------------------------------------------------------------------------------------------#
                                    ## Get a specific row, column, and the value of a specific cell examples
#----------------------------------------------------------------------------------------------------------------------------------------------------------------------#
#row23 = sheet.row_values(23)
#colA = sheet.col_values(1)


#----------------------------------------------------------------------------------------------------------------------------------------------------------------------#
                                             ## Insert the row and  the list as a row at index 4 Example:
#----------------------------------------------------------------------------------------------------------------------------------------------------------------------#
#--insertRow = ["hello", 5, "red", "blue"]
예제 #57
0
                #copy cells to the output_sheet
                new_cell_list= output.range(new_range)
                i=0
                for cell in cell_list:
                    if (i<max_col):
                        new_cell_list[i].value=cell_list[i].value
                    i+=1
                output.update_cells(new_cell_list)
        except gspread.exceptions.HTTPError:
                print " output.update_cells resulted in error try again"
                copy_row(input,output,input_row,output_row)

# main

scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name('GetBGGInfo-a1f867326e07.json', scope)
gc = gspread.authorize(credentials)
sh = gc.open("Essen 2016")
wks_input = sh.sheet1
sheetName="data_from_BGG"
try:
    sh.add_worksheet(title=sheetName,rows="100", cols="25")
except gspread.exceptions.RequestError:
    print sheetName, " sheet already exist"
wks_output=sh.worksheet(sheetName)
print "output worksheet title=",wks_output.title
Fields=["Name","Id","Publisher","Designers","Year","Artists", "Ranks", "Min_players",
        "Max_players","Min_age","Mechanics","Plying_time","Families",
        "Rating_average","Alternative_names","Expands","Price","Booth"]
col=1
for field in Fields:
예제 #58
0
'''
Ref: https://console.developers.google.com/apis/credentials?project=sheets-test-219809
    
   + Download credentials and store in same folder
   + Open credentials and get client_email
   + Share it with the spreadsheet file you want to access
'''

# which APIs are used
scope = [
    'http://spreadsheets.google.com/feeds',
    'https://www.googleapis.com/auth/drive'
]

# keep secret
credentials = ServiceAccountCredentials.from_json_keyfile_name(
    'Sheets Test-1b7944f419c8.json', scope)

# authorize
gc = gspread.authorize(credentials)

# open ss
worksheet = gc.open('test')
wks_1 = worksheet.get_worksheet(0)
wks_2 = worksheet.get_worksheet(1)

# read, write, update, append, delete


def read_data():
    data = wks_2.get_all_records()
    print(data)
예제 #59
0
def get_credentials(scope):
    service_account_json = os.getenv(
        'GOOGLE_SERVICE_ACCOUNT') or SERVICE_ACCOUNT_PATH.read_text()
    service_account = json.loads(service_account_json)
    return ServiceAccountCredentials.from_json_keyfile_dict(
        service_account, scope)
예제 #60
0
 def __init__(self):
     self.scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]
     self.creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", self.scope)
     self.client = gspread.authorize(self.creds)
     self.spreadsheet = self.client.open("Acoes")