Python sheets_clear Examples

Example #1

File: segment.py Project: hgrias/starthinker

def segment_clear():
  sheets_clear(
    project.task["auth_sheets"],
    project.task["sheet"],
    "Segments",
    "A2:Z"
  )

Example #2

def create_cm_site_segmentation(config, task):
  # Read sheet to bq table
  sheet_rows = sheets_read(config, task['auth_sheets'], task['sheet'], 'CM_Site_Segments', 'A:C', retries=10)
  if not sheet_rows:
    sheet_rows = []

  schema = [
    { "type": "STRING", "name": "Site_Dcm", "mode": "NULLABLE" },
    { "type": "INTEGER", "name": "Impressions", "mode": "NULLABLE" },
    { "type": "STRING", "name": "Site_Type", "mode": "NULLABLE" }
  ]

  rows_to_table(
    config,
    auth=task['auth_bq'],
    project_id=config.project,
    dataset_id=task['dataset'],
    table_id=CM_SITE_SEGMENTATION_SHEET_TABLE,
    rows=sheet_rows,
    schema=schema,
    skip_rows=1,
    disposition='WRITE_TRUNCATE'
  )

  # Get Site_Type from the sheet
  run_query_from_file(config, task, Queries.cm_site_segmentation.replace('{{dataset}}', task['dataset']), CM_SITE_SEGMENTATION_TABLE)

  # Move Table back to sheets
  query = 'SELECT * from `' + config.project + '.' + task['dataset'] + '.' + CM_SITE_SEGMENTATION_TABLE + '`'
  rows = query_to_rows(config, task['auth_bq'], config.project, task['dataset'], query, legacy=False)

  # makes sure types are correct in sheet
  rows = rows_to_type(rows)
  sheets_clear(config, task['auth_sheets'], task['sheet'], 'CM_Site_Segments', 'A2:C')
  sheets_write(config, task['auth_sheets'], task['sheet'], 'CM_Site_Segments', 'A2:C', rows)

Example #3

File: partner_cost.py Project: google/starthinker

def partner_cost_clear(config, task):
  sheets_clear(
    config,
    task["auth_sheets"],
    task["sheet"],
    "Partner Costs",
    "A2:Z"
  )

Example #4

File: audit.py Project: google/starthinker

def audit_clear(config, task):
  sheets_clear(
    config,
    task['auth_sheets'],
    task['sheet'],
    'Audit',
    'A2:Z'
  )

Example #5

def dv_advertiser_clear(config, task):
    table_create(
        config, task['auth_bigquery'], config.project, task['dataset'],
        'DV_Advertisers',
        Discovery_To_BigQuery('displayvideo',
                              'v1').method_schema('advertisers.list'))

    sheets_clear(config, task['auth_sheets'], task['sheet'], 'DV Advertisers',
                 'B2:D')

Example #6

File: store.py Project: quesadillasraclette/starthinker

    def clear(self):
        """Clears the store in the Bulkdozer feed.

    """
        if self.trix_id:
            sheets_clear(self.auth, self.trix_id, 'Store', 'A1:Z1')

        self._store = {}
        self._id_map = {}

Example #7

File: line_item.py Project: google/starthinker

def line_item_clear(config, task):
    table_create(
        config, task["auth_bigquery"], config.project, task["dataset"],
        "DV_LineItems",
        Discovery_To_BigQuery(
            "displayvideo", "v1").method_schema("advertisers.lineItems.list"))

    sheets_clear(config, task["auth_sheets"], task["sheet"], "Line Items",
                 "B2:H")

Example #8

File: advertiser.py Project: wunderkennd/starthinker

def advertiser_clear():
    table_create(
        project.task['auth_bigquery'], project.id, project.task['dataset'],
        'DV_Advertisers',
        Discovery_To_BigQuery('displayvideo',
                              'v1').method_schema('advertisers.list'))

    sheets_clear(project.task['auth_sheets'], project.task['sheet'],
                 'Advertisers', 'B2:D')

Example #9

File: line_item.py Project: wunderkennd/starthinker

def line_item_clear():
    table_create(
        project.task["auth_bigquery"], project.id, project.task["dataset"],
        "DV_LineItems",
        Discovery_To_BigQuery(
            "displayvideo", "v1").method_schema("advertisers.lineItems.list"))

    sheets_clear(project.task["auth_sheets"], project.task["sheet"],
                 "Line Items", "A2:AI")

Example #10

def insertion_order_clear():
    table_create(
        project.task["auth_bigquery"], project.id, project.task["dataset"],
        "DV_InsertionOrders",
        Discovery_To_BigQuery(
            "displayvideo",
            "v1").method_schema("advertisers.insertionOrders.list"))

    sheets_clear(project.task["auth_sheets"], project.task["sheet"],
                 "Insertion Orders", "A2:Z")

Example #11

File: inventory_source.py Project: google/starthinker

def inventory_source_clear(config, task):

    table_create(
        config, task['auth_bigquery'], config.project, task['dataset'],
        'DV_Inventory_Sources',
        Discovery_To_BigQuery('displayvideo',
                              'v1').method_schema('inventorySources.list'))

    sheets_clear(config, task['auth_sheets'], task['sheet'],
                 'Inventory Sources', 'A2:Z')

Example #12

File: dv_algorithm.py Project: google/starthinker

def dv_algorithm_clear(config, task):
    table_create(
        config, task['auth_bigquery'], config.project, task['dataset'],
        'DV_Algorithms',
        Discovery_To_BigQuery(
            'displayvideo',
            'v1').method_schema('customBiddingAlgorithms.list'))

    sheets_clear(config, task['auth_sheets'], task['sheet'], 'DV Algorithms',
                 'B2:D')

Example #13

def cm_profile_clear(config, task):
    table_create(
        config, task['auth_bigquery'], config.project, task['dataset'],
        'CM_Profiles',
        Discovery_To_BigQuery('dfareporting',
                              'v3.4').method_schema('userProfiles.list',
                                                    iterate=True))

    sheets_clear(config, task['auth_sheets'], task['sheet'], 'CM Profiles',
                 'B2:E')

Example #14

def partner_clear():
  table_create(
      project.task['auth_bigquery'],
      project.id,
      project.task['dataset'],
      'DV_Partners',
      Discovery_To_BigQuery('displayvideo',
                            'v1').method_schema('partners.list'),
  )

  sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Partners', 'B2:Z')

Example #15

File: campaign.py Project: hgrias/starthinker

def campaign_clear():
  table_create(
      project.task['auth_bigquery'],
      project.id,
      project.task['dataset'],
      'DV_Campaigns',
      Discovery_To_BigQuery('displayvideo',
                            'v1').method_schema('advertisers.campaigns.list'),
  )

  sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Campaigns', 'B2:Z')

Example #16

File: creative.py Project: hgrias/starthinker

def creative_clear():
    table_create(
        project.task["auth_bigquery"],
        project.id,
        project.task["dataset"],
        "DV_Creatives",
        Discovery_To_BigQuery(
            "displayvideo", "v1").method_schema("advertisers.creatives.list"),
    )

    sheets_clear(project.task["auth_sheets"], project.task["sheet"],
                 "Creatives", "B2:Z")

Example #17

File: run.py Project: hernanperalta/starthinker

def create_cm_site_segmentation(project):
    # Read sheet to bq table
    sheet_rows = sheets_read('user',
                             project.task['sheet'],
                             'CM_Site_Segments',
                             'A:C',
                             retries=10)
    if not sheet_rows:
        sheet_rows = []

    schema = [{
        "type": "STRING",
        "name": "Site_Dcm",
        "mode": "NULLABLE"
    }, {
        "type": "INTEGER",
        "name": "Impressions",
        "mode": "NULLABLE"
    }, {
        "type": "STRING",
        "name": "Site_Type",
        "mode": "NULLABLE"
    }]

    rows_to_table(auth='service',
                  project_id=project.id,
                  dataset_id=project.task['dataset'],
                  table_id=CM_SITE_SEGMENTATION_SHEET_TABLE,
                  rows=sheet_rows,
                  schema=schema,
                  skip_rows=1,
                  disposition='WRITE_TRUNCATE')

    # Get Site_Type from the sheet
    run_query_from_file(
        os.path.join(os.path.dirname(__file__),
                     SQL_DIRECTORY + CM_SITE_SEGMENTATION_FILENAME),
        project.id, project.task['dataset'], CM_SITE_SEGMENTATION_TABLE)

    # Move Table back to sheets
    query = 'SELECT * from `' + project.id + '.' + project.task[
        'dataset'] + '.' + CM_SITE_SEGMENTATION_TABLE + '`'
    rows = query_to_rows('service',
                         project.id,
                         project.task['dataset'],
                         query,
                         legacy=False)

    # makes sure types are correct in sheet
    rows = rows_to_type(rows)
    sheets_clear('user', project.task['sheet'], 'CM_Site_Segments', 'A2:C')
    sheets_write('user', project.task['sheet'], 'CM_Site_Segments', 'A2:C',
                 rows)

Example #18

def create_dv360_segments(config, task):
  a1_notation = 'A:N'
  schema = [
      { "type": "STRING", "name": "Advertiser", "mode": "NULLABLE" },
      { "type": "INTEGER", "name": "Advertiser_Id", "mode": "NULLABLE" },
      { "type": "STRING", "name": "Campaign", "mode": "NULLABLE" },
      { "type": "INTEGER", "name": "Campaign_Id", "mode": "NULLABLE" },
      { "type": "STRING", "name": "Insertion_Order", "mode": "NULLABLE" },
      { "type": "INTEGER", "name": "Insertion_Order_Id", "mode": "NULLABLE" },
      { "type": "STRING", "name": "Line_Item", "mode": "NULLABLE" },
      { "type": "INTEGER", "name": "Line_Item_Id", "mode": "NULLABLE" },
      { "type": "STRING", "name": "Line_Item_Type", "mode": "NULLABLE" },
      { "type": "INTEGER", "name": "Impressions", "mode": "NULLABLE" },
      { "type": "STRING", "name": "SegmentAutoGen", "mode": "NULLABLE" },
      { "type": "STRING", "name": "Segment1", "mode": "NULLABLE" },
      { "type": "STRING", "name": "Segment2", "mode": "NULLABLE" },
      { "type": "STRING", "name": "Segment3", "mode": "NULLABLE" }
    ]

  sheet_rows = sheets_read(config, task['auth_sheets'], task['sheet'], 'DV3 Segments', a1_notation, retries=10)

  if not sheet_rows:
    sheet_rows = []

  print('DV360 SEGMENT SHEET TABLE WRITE')

  rows_to_table(
    config,
    auth=task['auth_bq'],
    project_id=config.project,
    dataset_id=task['dataset'],
    table_id=DV360_CUSTOM_SEGMENTS_SHEET_TABLE,
    rows=sheet_rows,
    schema=schema,
    skip_rows=1,
    disposition='WRITE_TRUNCATE'
    )

  # Run Query
  if config.verbose:
    print('RUN DV360 Custom Segments Query')
  run_query_from_file(config, task, Queries.dv360_custom_segments.replace('{{dataset}}', task['dataset']), DV360_CUSTOM_SEGMENTS_TABLE)

  # Move Table back to sheets
  query = 'SELECT * from `' + config.project + '.' + task['dataset'] + '.' + DV360_CUSTOM_SEGMENTS_TABLE + '`'
  rows = query_to_rows(config, task['auth_bq'], config.project, task['dataset'], query, legacy=False)

  # makes sure types are correct in sheet
  a1_notation = a1_notation[:1] + '2' + a1_notation[1:]
  rows = rows_to_type(rows)
  sheets_clear(config, task['auth_sheets'], task['sheet'], 'DV3 Segments', a1_notation)
  sheets_write(config, task['auth_sheets'], task['sheet'], 'DV3 Segments', a1_notation, rows)

Example #19

File: targeting.py Project: fivestones-apac/starthinker

def targeting_clear():
    table_create(
        project.task['auth_bigquery'], project.id, project.task['dataset'],
        'DV_Targeting_Options',
        Discovery_To_BigQuery('displayvideo',
                              'v1').resource_schema('TargetingOption'))

    sheets_clear(project.task['auth_sheets'], project.task['sheet'],
                 'Targeting Options', 'A2:Z')

    table_create(
        project.task['auth_bigquery'], project.id, project.task['dataset'],
        'DV_Targeting_Assigned',
        Discovery_To_BigQuery('displayvideo',
                              'v1').resource_schema('AssignedTargetingOption'))

Example #20

File: run.py Project: fivestones-apac/starthinker

def create_cm_site_segmentation(project):
  # Read sheet to bq table
  sheet_rows = sheets_read(
      'user', project.task['sheet'], 'CM_Site_Segments', 'A:C', retries=10)
  if not sheet_rows:
    sheet_rows = []

  schema = [{
      'type': 'STRING',
      'name': 'Site_Dcm',
      'mode': 'NULLABLE'
  }, {
      'type': 'INTEGER',
      'name': 'Impressions',
      'mode': 'NULLABLE'
  }, {
      'type': 'STRING',
      'name': 'Site_Type',
      'mode': 'NULLABLE'
  }]

  rows_to_table(
      auth='service',
      project_id=project.id,
      dataset_id=project.task['dataset'],
      table_id=CM_SITE_SEGMENTATION_SHEET_TABLE,
      rows=sheet_rows,
      schema=schema,
      skip_rows=1,
      disposition='WRITE_TRUNCATE')

  # Get Site_Type from the sheet
  run_query_from_file(Queries.cm_site_segmentation, CM_SITE_SEGMENTATION_TABLE)

  # Move Table back to sheets
  query = 'SELECT * from `' + project.id + '.' + project.task[
      'dataset'] + '.' + CM_SITE_SEGMENTATION_TABLE + '`'
  rows = query_to_rows(
      'service', project.id, project.task['dataset'], query, legacy=False)

  # makes sure types are correct in sheet
  rows = rows_to_type(rows)
  sheets_clear('user', project.task['sheet'], 'CM_Site_Segments', 'A2:C')
  sheets_write('user', project.task['sheet'], 'CM_Site_Segments', 'A2:C', rows)

Example #21

File: edit.py Project: fivestones-apac/starthinker

def edit_clear():

  table_create(
    project.task['auth_bigquery'],
    project.id,
    project.task['dataset'],
    'EDIT_Preview',
    SCHEMA_PREVIEW
  )

  table_create(project.task['auth_bigquery'],
    project.id,
    project.task['dataset'],
    'EDIT_Log',
    SCHEMA_LOG
  )

  sheets_clear(project.task['auth_sheets'],
    project.task['sheet'],
    'Preview',
    'A2:Z'
  )

  sheets_clear(project.task['auth_sheets'],
    project.task['sheet'],
    'Error',
    'A2:Z'
  )

  sheets_clear(project.task['auth_sheets'],
    project.task['sheet'],
    'Success',
    'A2:Z'
  )

Example #22

def edit_clear(config, task):

    table_create(config, task['auth_bigquery'], config.project,
                 task['dataset'], 'EDIT_Preview', SCHEMA_PREVIEW)

    table_create(config, task['auth_bigquery'], config.project,
                 task['dataset'], 'EDIT_Log', SCHEMA_LOG)

    sheets_clear(config, task['auth_sheets'], task['sheet'], 'Preview', 'A2:Z')

    sheets_clear(config, task['auth_sheets'], task['sheet'], 'Warning', 'A2:Z')

    sheets_clear(config, task['auth_sheets'], task['sheet'], 'Error', 'A2:Z')

    sheets_clear(config, task['auth_sheets'], task['sheet'], 'Success', 'A2:Z')

Example #23

File: cm_campaign.py Project: google/starthinker

def cm_campaign_clear(config, task):
  table_create(
    config,
    task['auth_bigquery'],
    config.project,
    task['dataset'],
    'CM_Campaigns',
    Discovery_To_BigQuery(
      'dfareporting',
      'v3.4'
    ).method_schema(
      'campaigns.list',
      iterate=True
    )
  )

  sheets_clear(
    config,
    task['auth_sheets'],
    task['sheet'],
    'CM Campaigns',
    'B2:G'
  )

Example #24

File: cm_account.py Project: google/starthinker

def cm_account_clear(config, task):
  table_create(
    config,
    task['auth_bigquery'],
    config.project,
    task['dataset'],
    'CM_Accounts',
    Discovery_To_BigQuery(
      'dfareporting',
      'v3.4'
    ).method_schema(
      'accounts.list',
      iterate=True
    )
  )

  sheets_clear(
    config,
    task['auth_sheets'],
    task['sheet'],
    'CM Accounts',
    'B2:D'
  )

Example #25

File: partner_cost.py Project: fivestones-apac/starthinker

def partner_cost_clear():
    sheets_clear(project.task["auth_sheets"], project.task["sheet"],
                 "Partner Costs", "A2:Z")

Example #26

File: logger.py Project: wunderkennd/starthinker

 def clear(self):
     """Clears the log tab in the Bulkdozer feed, useful when a new execution is starting."""
     sheets_clear(self.auth, self.trix_id, 'Log', 'A1:B')
     self._row = 1

Example #27

File: frequency_cap.py Project: NgoTheHieu/starthinker

def frequency_cap_clear():
    sheets_clear(project.task["auth_sheets"], project.task["sheet"],
                 "Frequency Caps", "A2:Z")

Example #28

File: pacing.py Project: fivestones-apac/starthinker

def pacing_clear():
  sheets_clear(project.task["auth_sheets"], project.task["sheet"], "Pacing", "A2:Z")

Example #29

File: run.py Project: fivestones-apac/starthinker

def create_dv360_segments(project):
  a1_notation = 'A:M'
  schema = [{
      'type': 'STRING',
      'name': 'Advertiser',
      'mode': 'NULLABLE'
  }, {
      'type': 'INTEGER',
      'name': 'Advertiser_Id',
      'mode': 'NULLABLE'
  }, {
      'type': 'STRING',
      'name': 'Campaign',
      'mode': 'NULLABLE'
  }, {
      'type': 'INTEGER',
      'name': 'Campaign_Id',
      'mode': 'NULLABLE'
  }, {
      'type': 'STRING',
      'name': 'Insertion_Order',
      'mode': 'NULLABLE'
  }, {
      'type': 'INTEGER',
      'name': 'Insertion_Order_Id',
      'mode': 'NULLABLE'
  }, {
      'type': 'STRING',
      'name': 'Line_Item',
      'mode': 'NULLABLE'
  }, {
      'type': 'INTEGER',
      'name': 'Line_Item_Id',
      'mode': 'NULLABLE'
  }, {
      'type': 'STRING',
      'name': 'Line_Item_Type',
      'mode': 'NULLABLE'
  }, {
      'type': 'INTEGER',
      'name': 'Impressions',
      'mode': 'NULLABLE'
  }, {
      'type': 'STRING',
      'name': 'Segment1',
      'mode': 'NULLABLE'
  }, {
      'type': 'STRING',
      'name': 'Segment2',
      'mode': 'NULLABLE'
  }, {
      'type': 'STRING',
      'name': 'Segment3',
      'mode': 'NULLABLE'
  }]

  sheet_rows = sheets_read(
      'user', project.task['sheet'], 'DV3 Segments', a1_notation, retries=10)

  if not sheet_rows:
    sheet_rows = []

  print('DV360 SEGMENT SHEET TABLE WRITE')

  rows_to_table(
      auth='service',
      project_id=project.id,
      dataset_id=project.task['dataset'],
      table_id=DV360_CUSTOM_SEGMENTS_SHEET_TABLE,
      rows=sheet_rows,
      schema=schema,
      skip_rows=1,
      disposition='WRITE_TRUNCATE')

  # Run Query
  if project.verbose:
    print('RUN DV360 Custom Segments Query')
  run_query_from_file(Queries.dv360_custom_segments, DV360_CUSTOM_SEGMENTS_TABLE)

  # Move Table back to sheets
  query = 'SELECT * from `' + project.id + '.' + project.task[
      'dataset'] + '.' + DV360_CUSTOM_SEGMENTS_TABLE + '`'
  rows = query_to_rows(
      'service', project.id, project.task['dataset'], query, legacy=False)

  # makes sure types are correct in sheet
  a1_notation = a1_notation[:1] + '2' + a1_notation[1:]
  rows = rows_to_type(rows)
  sheets_clear('user', project.task['sheet'], 'DV3 Segments', a1_notation)
  sheets_write('user', project.task['sheet'], 'DV3 Segments', a1_notation, rows)

Example #30

File: audit.py Project: fivestones-apac/starthinker

def audit_load():

    bid_strategy_audit()
    integration_detail_audit()
    frequency_cap_audit()
    line_item_map_audit()
    pacing_audit()
    partner_cost_audit()
    segment_audit()
    insertion_order_audit()
    line_item_audit()

    # write audit to sheet
    sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Audit',
                 'A2')

    # write audits to sheet
    rows = get_rows(
        project.task['auth_bigquery'], {
            'bigquery': {
                'dataset':
                project.task['dataset'],
                'query':
                """SELECT Operation, Severity, Id, Error
        FROM `{dataset}.AUDIT_InsertionOrders`
      UNION ALL
        SELECT Operation, Severity, Id, Error
        FROM `{dataset}.AUDIT_Segments`
      UNION ALL
        SELECT Operation, Severity, Id, Error
        FROM `{dataset}.AUDIT_LineItems`
      UNION ALL
        SELECT Operation, Severity, Id, Error
        FROM `{dataset}.AUDIT_LineItemMaps`
      UNION ALL
        SELECT Operation, Severity, Id, Error
        FROM `{dataset}.AUDIT_Pacing`
      UNION ALL
        SELECT Operation, Severity, Id, Error
        FROM `{dataset}.AUDIT_BidStrategy`
      UNION ALL
        SELECT Operation, Severity, Id, Error
        FROM `{dataset}.AUDIT_FrequencyCaps`
      UNION ALL
        SELECT Operation, Severity, Id, Error
        FROM `{dataset}.AUDIT_PartnerCosts`
      UNION ALL
        SELECT Operation, Severity, Id, Error
        FROM `{dataset}.AUDIT_IntegrationDetails`
      """.format(**project.task),
                'legacy':
                False
            }
        })

    put_rows(project.task['auth_sheets'], {
        'sheets': {
            'sheet': project.task['sheet'],
            'tab': 'Audit',
            'range': 'A2'
        }
    }, rows)