How To Create User-Generated Content? [A Simple Guide To Grow Your Brand]

This is image title

In this digital world, online businesses aspire to catch the attention of users in a modern and smarter way. To achieve it, they need to traverse through new approaches. Here comes to spotlight is the user-generated content or UGC.

What is user-generated content?
“ It is the content by users for users.”

Generally, the UGC is the unbiased content created and published by the brand users, social media followers, fans, and influencers that highlight their experiences with the products or services. User-generated content has superseded other marketing trends and fallen into the advertising feeds of brands. Today, more than 86 percent of companies use user-generated content as part of their marketing strategy.

In this article, we have explained the ten best ideas to create wonderful user-generated content for your brand. Let’s start without any further ado.

  1. Content From Social Media Platforms
    In the year 2020, there are 3.81 million people actively using social media around the globe. That is the reason social media content matters. Whenever users look at the content on social media that is posted by an individual, then they may be influenced by their content. Perhaps, it can be used to gain more customers or followers on your social media platforms.

This is image title

Generally, social media platforms help the brand to generate content for your users. Any user content that promotes your brand on the social media platform is the user-generated content for your business. When users create and share content on social media, they get 28% higher engagement than a standard company post.

Furthermore, you can embed your social media feed on your website also. you can use the Social Stream Designer WordPress plugin that will integrate various social media feeds from different social media platforms like Facebook, Twitter, Instagram, and many more. With this plugin, you can create a responsive wall on your WordPress website or blog in a few minutes. In addition to this, the plugin also provides more than 40 customization options to make your social stream feeds more attractive.

  1. Consumer Survey
    The customer survey provides powerful insights you need to make a better decision for your business. Moreover, it is great user-generated content that is useful for identifying unhappy consumers and those who like your product or service.

In general, surveys can be used to figure out attitudes, reactions, to evaluate customer satisfaction, estimate their opinions about different problems. Another benefit of customer surveys is that collecting outcomes can be quick. Within a few minutes, you can design and load a customer feedback survey and send it to your customers for their response. From the customer survey data, you can find your strengths, weaknesses, and get the right way to improve them to gain more customers.

  1. Run Contests
    A contest is a wonderful way to increase awareness about a product or service. Contest not just helps you to enhance the volume of user-generated content submissions, but they also help increase their quality. However, when you create a contest, it is important to keep things as simple as possible.

Additionally, it is the best way to convert your brand leads to valuable customers. The key to running a successful contest is to make sure that the reward is fair enough to motivate your participation. If the product is relevant to your participant, then chances are they were looking for it in the first place, and giving it to them for free just made you move forward ahead of your competitors. They will most likely purchase more if your product or service satisfies them.

Furthermore, running contests also improve the customer-brand relationship and allows more people to participate in it. It will drive a real result for your online business. If your WordPress website has Google Analytics, then track contest page visits, referral traffic, other website traffic, and many more.

  1. Review And Testimonials
    Customer reviews are a popular user-generated content strategy. One research found that around 68% of customers must see at least four reviews before trusting a brand. And, approximately 40 percent of consumers will stop using a business after they read negative reviews.

The business reviews help your consumers to make a buying decision without any hurdle. While you may decide to remove all the negative reviews about your business, those are still valuable user-generated content that provides honest opinions from real users. Customer feedback can help you with what needs to be improved with your products or services. This thing is not only beneficial to the next customer but your business as a whole.

This is image title

Reviews are powerful as the platform they are built upon. That is the reason it is important to gather reviews from third-party review websites like Google review, Facebook review, and many more, or direct reviews on a website. It is the most vital form of feedback that can help brands grow globally and motivate audience interactions.

However, you can also invite your customers to share their unique or successful testimonials. It is a great way to display your products while inspiring others to purchase from your website.

  1. Video Content
    A great video is a video that is enjoyed by visitors. These different types of videos, such as 360-degree product videos, product demo videos, animated videos, and corporate videos. The Facebook study has demonstrated that users spend 3x more time watching live videos than normal videos. With the live video, you can get more user-created content.

Moreover, Instagram videos create around 3x more comments rather than Instagram photo posts. Instagram videos generally include short videos posted by real customers on Instagram with the tag of a particular brand. Brands can repost the stories as user-generated content to engage more audiences and create valid promotions on social media.

Similarly, imagine you are browsing a YouTube channel, and you look at a brand being supported by some authentic customers through a small video. So, it will catch your attention. With the videos, they can tell you about the branded products, especially the unboxing videos displaying all the inside products and how well it works for them. That type of video is enough to create a sense of desire in the consumers.

Continue Reading

#how to get more user generated content #importance of user generated content #user generated content #user generated content advantages #user generated content best practices #user generated content pros and cons

What is GEEK

Buddha Community

How To Create User-Generated Content? [A Simple Guide To Grow Your Brand]

This is image title

In this digital world, online businesses aspire to catch the attention of users in a modern and smarter way. To achieve it, they need to traverse through new approaches. Here comes to spotlight is the user-generated content or UGC.

What is user-generated content?
“ It is the content by users for users.”

Generally, the UGC is the unbiased content created and published by the brand users, social media followers, fans, and influencers that highlight their experiences with the products or services. User-generated content has superseded other marketing trends and fallen into the advertising feeds of brands. Today, more than 86 percent of companies use user-generated content as part of their marketing strategy.

In this article, we have explained the ten best ideas to create wonderful user-generated content for your brand. Let’s start without any further ado.

  1. Content From Social Media Platforms
    In the year 2020, there are 3.81 million people actively using social media around the globe. That is the reason social media content matters. Whenever users look at the content on social media that is posted by an individual, then they may be influenced by their content. Perhaps, it can be used to gain more customers or followers on your social media platforms.

This is image title

Generally, social media platforms help the brand to generate content for your users. Any user content that promotes your brand on the social media platform is the user-generated content for your business. When users create and share content on social media, they get 28% higher engagement than a standard company post.

Furthermore, you can embed your social media feed on your website also. you can use the Social Stream Designer WordPress plugin that will integrate various social media feeds from different social media platforms like Facebook, Twitter, Instagram, and many more. With this plugin, you can create a responsive wall on your WordPress website or blog in a few minutes. In addition to this, the plugin also provides more than 40 customization options to make your social stream feeds more attractive.

  1. Consumer Survey
    The customer survey provides powerful insights you need to make a better decision for your business. Moreover, it is great user-generated content that is useful for identifying unhappy consumers and those who like your product or service.

In general, surveys can be used to figure out attitudes, reactions, to evaluate customer satisfaction, estimate their opinions about different problems. Another benefit of customer surveys is that collecting outcomes can be quick. Within a few minutes, you can design and load a customer feedback survey and send it to your customers for their response. From the customer survey data, you can find your strengths, weaknesses, and get the right way to improve them to gain more customers.

  1. Run Contests
    A contest is a wonderful way to increase awareness about a product or service. Contest not just helps you to enhance the volume of user-generated content submissions, but they also help increase their quality. However, when you create a contest, it is important to keep things as simple as possible.

Additionally, it is the best way to convert your brand leads to valuable customers. The key to running a successful contest is to make sure that the reward is fair enough to motivate your participation. If the product is relevant to your participant, then chances are they were looking for it in the first place, and giving it to them for free just made you move forward ahead of your competitors. They will most likely purchase more if your product or service satisfies them.

Furthermore, running contests also improve the customer-brand relationship and allows more people to participate in it. It will drive a real result for your online business. If your WordPress website has Google Analytics, then track contest page visits, referral traffic, other website traffic, and many more.

  1. Review And Testimonials
    Customer reviews are a popular user-generated content strategy. One research found that around 68% of customers must see at least four reviews before trusting a brand. And, approximately 40 percent of consumers will stop using a business after they read negative reviews.

The business reviews help your consumers to make a buying decision without any hurdle. While you may decide to remove all the negative reviews about your business, those are still valuable user-generated content that provides honest opinions from real users. Customer feedback can help you with what needs to be improved with your products or services. This thing is not only beneficial to the next customer but your business as a whole.

This is image title

Reviews are powerful as the platform they are built upon. That is the reason it is important to gather reviews from third-party review websites like Google review, Facebook review, and many more, or direct reviews on a website. It is the most vital form of feedback that can help brands grow globally and motivate audience interactions.

However, you can also invite your customers to share their unique or successful testimonials. It is a great way to display your products while inspiring others to purchase from your website.

  1. Video Content
    A great video is a video that is enjoyed by visitors. These different types of videos, such as 360-degree product videos, product demo videos, animated videos, and corporate videos. The Facebook study has demonstrated that users spend 3x more time watching live videos than normal videos. With the live video, you can get more user-created content.

Moreover, Instagram videos create around 3x more comments rather than Instagram photo posts. Instagram videos generally include short videos posted by real customers on Instagram with the tag of a particular brand. Brands can repost the stories as user-generated content to engage more audiences and create valid promotions on social media.

Similarly, imagine you are browsing a YouTube channel, and you look at a brand being supported by some authentic customers through a small video. So, it will catch your attention. With the videos, they can tell you about the branded products, especially the unboxing videos displaying all the inside products and how well it works for them. That type of video is enough to create a sense of desire in the consumers.

Continue Reading

#how to get more user generated content #importance of user generated content #user generated content #user generated content advantages #user generated content best practices #user generated content pros and cons

Easter  Deckow

Easter Deckow

1655630160

PyTumblr: A Python Tumblr API v2 Client

PyTumblr

Installation

Install via pip:

$ pip install pytumblr

Install from source:

$ git clone https://github.com/tumblr/pytumblr.git
$ cd pytumblr
$ python setup.py install

Usage

Create a client

A pytumblr.TumblrRestClient is the object you'll make all of your calls to the Tumblr API through. Creating one is this easy:

client = pytumblr.TumblrRestClient(
    '<consumer_key>',
    '<consumer_secret>',
    '<oauth_token>',
    '<oauth_secret>',
)

client.info() # Grabs the current user information

Two easy ways to get your credentials to are:

  1. The built-in interactive_console.py tool (if you already have a consumer key & secret)
  2. The Tumblr API console at https://api.tumblr.com/console
  3. Get sample login code at https://api.tumblr.com/console/calls/user/info

Supported Methods

User Methods

client.info() # get information about the authenticating user
client.dashboard() # get the dashboard for the authenticating user
client.likes() # get the likes for the authenticating user
client.following() # get the blogs followed by the authenticating user

client.follow('codingjester.tumblr.com') # follow a blog
client.unfollow('codingjester.tumblr.com') # unfollow a blog

client.like(id, reblogkey) # like a post
client.unlike(id, reblogkey) # unlike a post

Blog Methods

client.blog_info(blogName) # get information about a blog
client.posts(blogName, **params) # get posts for a blog
client.avatar(blogName) # get the avatar for a blog
client.blog_likes(blogName) # get the likes on a blog
client.followers(blogName) # get the followers of a blog
client.blog_following(blogName) # get the publicly exposed blogs that [blogName] follows
client.queue(blogName) # get the queue for a given blog
client.submission(blogName) # get the submissions for a given blog

Post Methods

Creating posts

PyTumblr lets you create all of the various types that Tumblr supports. When using these types there are a few defaults that are able to be used with any post type.

The default supported types are described below.

  • state - a string, the state of the post. Supported types are published, draft, queue, private
  • tags - a list, a list of strings that you want tagged on the post. eg: ["testing", "magic", "1"]
  • tweet - a string, the string of the customized tweet you want. eg: "Man I love my mega awesome post!"
  • date - a string, the customized GMT that you want
  • format - a string, the format that your post is in. Support types are html or markdown
  • slug - a string, the slug for the url of the post you want

We'll show examples throughout of these default examples while showcasing all the specific post types.

Creating a photo post

Creating a photo post supports a bunch of different options plus the described default options * caption - a string, the user supplied caption * link - a string, the "click-through" url for the photo * source - a string, the url for the photo you want to use (use this or the data parameter) * data - a list or string, a list of filepaths or a single file path for multipart file upload

#Creates a photo post using a source URL
client.create_photo(blogName, state="published", tags=["testing", "ok"],
                    source="https://68.media.tumblr.com/b965fbb2e501610a29d80ffb6fb3e1ad/tumblr_n55vdeTse11rn1906o1_500.jpg")

#Creates a photo post using a local filepath
client.create_photo(blogName, state="queue", tags=["testing", "ok"],
                    tweet="Woah this is an incredible sweet post [URL]",
                    data="/Users/johnb/path/to/my/image.jpg")

#Creates a photoset post using several local filepaths
client.create_photo(blogName, state="draft", tags=["jb is cool"], format="markdown",
                    data=["/Users/johnb/path/to/my/image.jpg", "/Users/johnb/Pictures/kittens.jpg"],
                    caption="## Mega sweet kittens")

Creating a text post

Creating a text post supports the same options as default and just a two other parameters * title - a string, the optional title for the post. Supports markdown or html * body - a string, the body of the of the post. Supports markdown or html

#Creating a text post
client.create_text(blogName, state="published", slug="testing-text-posts", title="Testing", body="testing1 2 3 4")

Creating a quote post

Creating a quote post supports the same options as default and two other parameter * quote - a string, the full text of the qote. Supports markdown or html * source - a string, the cited source. HTML supported

#Creating a quote post
client.create_quote(blogName, state="queue", quote="I am the Walrus", source="Ringo")

Creating a link post

  • title - a string, the title of post that you want. Supports HTML entities.
  • url - a string, the url that you want to create a link post for.
  • description - a string, the desciption of the link that you have
#Create a link post
client.create_link(blogName, title="I like to search things, you should too.", url="https://duckduckgo.com",
                   description="Search is pretty cool when a duck does it.")

Creating a chat post

Creating a chat post supports the same options as default and two other parameters * title - a string, the title of the chat post * conversation - a string, the text of the conversation/chat, with diablog labels (no html)

#Create a chat post
chat = """John: Testing can be fun!
Renee: Testing is tedious and so are you.
John: Aw.
"""
client.create_chat(blogName, title="Renee just doesn't understand.", conversation=chat, tags=["renee", "testing"])

Creating an audio post

Creating an audio post allows for all default options and a has 3 other parameters. The only thing to keep in mind while dealing with audio posts is to make sure that you use the external_url parameter or data. You cannot use both at the same time. * caption - a string, the caption for your post * external_url - a string, the url of the site that hosts the audio file * data - a string, the filepath of the audio file you want to upload to Tumblr

#Creating an audio file
client.create_audio(blogName, caption="Rock out.", data="/Users/johnb/Music/my/new/sweet/album.mp3")

#lets use soundcloud!
client.create_audio(blogName, caption="Mega rock out.", external_url="https://soundcloud.com/skrillex/sets/recess")

Creating a video post

Creating a video post allows for all default options and has three other options. Like the other post types, it has some restrictions. You cannot use the embed and data parameters at the same time. * caption - a string, the caption for your post * embed - a string, the HTML embed code for the video * data - a string, the path of the file you want to upload

#Creating an upload from YouTube
client.create_video(blogName, caption="Jon Snow. Mega ridiculous sword.",
                    embed="http://www.youtube.com/watch?v=40pUYLacrj4")

#Creating a video post from local file
client.create_video(blogName, caption="testing", data="/Users/johnb/testing/ok/blah.mov")

Editing a post

Updating a post requires you knowing what type a post you're updating. You'll be able to supply to the post any of the options given above for updates.

client.edit_post(blogName, id=post_id, type="text", title="Updated")
client.edit_post(blogName, id=post_id, type="photo", data="/Users/johnb/mega/awesome.jpg")

Reblogging a Post

Reblogging a post just requires knowing the post id and the reblog key, which is supplied in the JSON of any post object.

client.reblog(blogName, id=125356, reblog_key="reblog_key")

Deleting a post

Deleting just requires that you own the post and have the post id

client.delete_post(blogName, 123456) # Deletes your post :(

A note on tags: When passing tags, as params, please pass them as a list (not a comma-separated string):

client.create_text(blogName, tags=['hello', 'world'], ...)

Getting notes for a post

In order to get the notes for a post, you need to have the post id and the blog that it is on.

data = client.notes(blogName, id='123456')

The results include a timestamp you can use to make future calls.

data = client.notes(blogName, id='123456', before_timestamp=data["_links"]["next"]["query_params"]["before_timestamp"])

Tagged Methods

# get posts with a given tag
client.tagged(tag, **params)

Using the interactive console

This client comes with a nice interactive console to run you through the OAuth process, grab your tokens (and store them for future use).

You'll need pyyaml installed to run it, but then it's just:

$ python interactive-console.py

and away you go! Tokens are stored in ~/.tumblr and are also shared by other Tumblr API clients like the Ruby client.

Running tests

The tests (and coverage reports) are run with nose, like this:

python setup.py test

Author: tumblr
Source Code: https://github.com/tumblr/pytumblr
License: Apache-2.0 license

#python #api 

Divya Raj

1624879756

Employer Branding Guide: Tips to Develop the Best Employer Branding Strategy for 2021

We have penned down this article to cover virtually everything there is to know about employer branding, such as its benefits, how to measure it, how can you improve it, and some coverage on top global companies using market-leading employer branding strategies.

To know more about this refer-

https://blog.digitalogy.co/employer-branding-strategy/

#best employer branding #best employer branding strategy for 2021 #employer branding #, employer branding 2021 #employer branding and recruitment #employer branding best practices

Shubham Ankit

Shubham Ankit

1657081614

How to Automate Excel with Python | Python Excel Tutorial (OpenPyXL)

How to Automate Excel with Python

In this article, We will show how we can use python to automate Excel . A useful Python library is Openpyxl which we will learn to do Excel Automation

What is OPENPYXL

Openpyxl is a Python library that is used to read from an Excel file or write to an Excel file. Data scientists use Openpyxl for data analysis, data copying, data mining, drawing charts, styling sheets, adding formulas, and more.

Workbook: A spreadsheet is represented as a workbook in openpyxl. A workbook consists of one or more sheets.

Sheet: A sheet is a single page composed of cells for organizing data.

Cell: The intersection of a row and a column is called a cell. Usually represented by A1, B5, etc.

Row: A row is a horizontal line represented by a number (1,2, etc.).

Column: A column is a vertical line represented by a capital letter (A, B, etc.).

Openpyxl can be installed using the pip command and it is recommended to install it in a virtual environment.

pip install openpyxl

CREATE A NEW WORKBOOK

We start by creating a new spreadsheet, which is called a workbook in Openpyxl. We import the workbook module from Openpyxl and use the function Workbook() which creates a new workbook.

from openpyxl
import Workbook
#creates a new workbook
wb = Workbook()
#Gets the first active worksheet
ws = wb.active
#creating new worksheets by using the create_sheet method

ws1 = wb.create_sheet("sheet1", 0) #inserts at first position
ws2 = wb.create_sheet("sheet2") #inserts at last position
ws3 = wb.create_sheet("sheet3", -1) #inserts at penultimate position

#Renaming the sheet
ws.title = "Example"

#save the workbook
wb.save(filename = "example.xlsx")

READING DATA FROM WORKBOOK

We load the file using the function load_Workbook() which takes the filename as an argument. The file must be saved in the same working directory.

#loading a workbook
wb = openpyxl.load_workbook("example.xlsx")

 

GETTING SHEETS FROM THE LOADED WORKBOOK

 

#getting sheet names
wb.sheetnames
result = ['sheet1', 'Sheet', 'sheet3', 'sheet2']

#getting a particular sheet
sheet1 = wb["sheet2"]

#getting sheet title
sheet1.title
result = 'sheet2'

#Getting the active sheet
sheetactive = wb.active
result = 'sheet1'

 

ACCESSING CELLS AND CELL VALUES

 

#get a cell from the sheet
sheet1["A1"] <
  Cell 'Sheet1'.A1 >

  #get the cell value
ws["A1"].value 'Segment'

#accessing cell using row and column and assigning a value
d = ws.cell(row = 4, column = 2, value = 10)
d.value
10

 

ITERATING THROUGH ROWS AND COLUMNS

 

#looping through each row and column
for x in range(1, 5):
  for y in range(1, 5):
  print(x, y, ws.cell(row = x, column = y)
    .value)

#getting the highest row number
ws.max_row
701

#getting the highest column number
ws.max_column
19

There are two functions for iterating through rows and columns.

Iter_rows() => returns the rows
Iter_cols() => returns the columns {
  min_row = 4, max_row = 5, min_col = 2, max_col = 5
} => This can be used to set the boundaries
for any iteration.

Example:

#iterating rows
for row in ws.iter_rows(min_row = 2, max_col = 3, max_row = 3):
  for cell in row:
  print(cell) <
  Cell 'Sheet1'.A2 >
  <
  Cell 'Sheet1'.B2 >
  <
  Cell 'Sheet1'.C2 >
  <
  Cell 'Sheet1'.A3 >
  <
  Cell 'Sheet1'.B3 >
  <
  Cell 'Sheet1'.C3 >

  #iterating columns
for col in ws.iter_cols(min_row = 2, max_col = 3, max_row = 3):
  for cell in col:
  print(cell) <
  Cell 'Sheet1'.A2 >
  <
  Cell 'Sheet1'.A3 >
  <
  Cell 'Sheet1'.B2 >
  <
  Cell 'Sheet1'.B3 >
  <
  Cell 'Sheet1'.C2 >
  <
  Cell 'Sheet1'.C3 >

To get all the rows of the worksheet we use the method worksheet.rows and to get all the columns of the worksheet we use the method worksheet.columns. Similarly, to iterate only through the values we use the method worksheet.values.


Example:

for row in ws.values:
  for value in row:
  print(value)

 

WRITING DATA TO AN EXCEL FILE

Writing to a workbook can be done in many ways such as adding a formula, adding charts, images, updating cell values, inserting rows and columns, etc… We will discuss each of these with an example.

 

CREATING AND SAVING A NEW WORKBOOK

 

#creates a new workbook
wb = openpyxl.Workbook()

#saving the workbook
wb.save("new.xlsx")

 

ADDING AND REMOVING SHEETS

 

#creating a new sheet
ws1 = wb.create_sheet(title = "sheet 2")

#creating a new sheet at index 0
ws2 = wb.create_sheet(index = 0, title = "sheet 0")

#checking the sheet names
wb.sheetnames['sheet 0', 'Sheet', 'sheet 2']

#deleting a sheet
del wb['sheet 0']

#checking sheetnames
wb.sheetnames['Sheet', 'sheet 2']

 

ADDING CELL VALUES

 

#checking the sheet value
ws['B2'].value
null

#adding value to cell
ws['B2'] = 367

#checking value
ws['B2'].value
367

 

ADDING FORMULAS

 

We often require formulas to be included in our Excel datasheet. We can easily add formulas using the Openpyxl module just like you add values to a cell.
 

For example:

import openpyxl
from openpyxl
import Workbook

wb = openpyxl.load_workbook("new1.xlsx")
ws = wb['Sheet']

ws['A9'] = '=SUM(A2:A8)'

wb.save("new2.xlsx")

The above program will add the formula (=SUM(A2:A8)) in cell A9. The result will be as below.

image

 

MERGE/UNMERGE CELLS

Two or more cells can be merged to a rectangular area using the method merge_cells(), and similarly, they can be unmerged using the method unmerge_cells().

For example:
Merge cells

#merge cells B2 to C9
ws.merge_cells('B2:C9')
ws['B2'] = "Merged cells"

Adding the above code to the previous example will merge cells as below.

image

UNMERGE CELLS

 

#unmerge cells B2 to C9
ws.unmerge_cells('B2:C9')

The above code will unmerge cells from B2 to C9.

INSERTING AN IMAGE

To insert an image we import the image function from the module openpyxl.drawing.image. We then load our image and add it to the cell as shown in the below example.

Example:

import openpyxl
from openpyxl
import Workbook
from openpyxl.drawing.image
import Image

wb = openpyxl.load_workbook("new1.xlsx")
ws = wb['Sheet']
#loading the image(should be in same folder)
img = Image('logo.png')
ws['A1'] = "Adding image"
#adjusting size
img.height = 130
img.width = 200
#adding img to cell A3

ws.add_image(img, 'A3')

wb.save("new2.xlsx")

Result:

image

CREATING CHARTS

Charts are essential to show a visualization of data. We can create charts from Excel data using the Openpyxl module chart. Different forms of charts such as line charts, bar charts, 3D line charts, etc., can be created. We need to create a reference that contains the data to be used for the chart, which is nothing but a selection of cells (rows and columns). I am using sample data to create a 3D bar chart in the below example:

Example

import openpyxl
from openpyxl
import Workbook
from openpyxl.chart
import BarChart3D, Reference, series

wb = openpyxl.load_workbook("example.xlsx")
ws = wb.active

values = Reference(ws, min_col = 3, min_row = 2, max_col = 3, max_row = 40)
chart = BarChart3D()
chart.add_data(values)
ws.add_chart(chart, "E3")
wb.save("MyChart.xlsx")

Result
image


How to Automate Excel with Python with Video Tutorial

Welcome to another video! In this video, We will cover how we can use python to automate Excel. I'll be going over everything from creating workbooks to accessing individual cells and stylizing cells. There is a ton of things that you can do with Excel but I'll just be covering the core/base things in OpenPyXl.

⭐️ Timestamps ⭐️
00:00 | Introduction
02:14 | Installing openpyxl
03:19 | Testing Installation
04:25 | Loading an Existing Workbook
06:46 | Accessing Worksheets
07:37 | Accessing Cell Values
08:58 | Saving Workbooks
09:52 | Creating, Listing and Changing Sheets
11:50 | Creating a New Workbook
12:39 | Adding/Appending Rows
14:26 | Accessing Multiple Cells
20:46 | Merging Cells
22:27 | Inserting and Deleting Rows
23:35 | Inserting and Deleting Columns
24:48 | Copying and Moving Cells
26:06 | Practical Example, Formulas & Cell Styling

📄 Resources 📄
OpenPyXL Docs: https://openpyxl.readthedocs.io/en/stable/ 
Code Written in This Tutorial: https://github.com/techwithtim/ExcelPythonTutorial 
Subscribe: https://www.youtube.com/c/TechWithTim/featured 

#python 

Hermann  Frami

Hermann Frami

1656080760

Serverless Plugin: AWS API Gateway integration Helper

Serverless Plugin: AWS Api Gateway integration helper

Overview

The plugin provides the functionality to merge OpenApiSpecification files (formerly known as swagger) with one or multiple YML files containing the the x-amazon-apigateway extensions. There are several use-cases to keep both information separated, e.g. it is needed to deploy different api gateway integrations depending on a stage environment.

When dealing with functional tests you do not want to test the production environment, but only a mocking response.

The plugin supports YML based OpenApi3 specification files only

Features

  • deploy stage dependent x-amazon-apigateway integrations
  • separate infrastructure (aws) from openapi specification
  • use mock integrations for functional testing
  • auto-generating CORS methods, headers and api gateway mocking response
  • hook into package & deploy lifeCycle and generate combined openApi files on the fly during deployment
  • auto-inject generated openApi file into the Body property of specified API Gateway
  • generate mocking responses without specifying x-amazon-apigateway-integration objects
  • generate request-validation blocks
  • generate all required x-amazon-apigateway-integration objects automatically
  • full proxy generation support with [NEW]: feature: PROXY Manager

See the examples folder for a full working example

Installation & Setup

Run npm install in your Serverless project.

$ npm install --save-dev serverless-openapi-integration-helper

Add the plugin to your serverless.yml file

plugins:
  - serverless-openapi-integration-helper

Plugin configuration

You can configure the plugin under the key openApiIntegration. See See Configuration Reference for a list of available options

The mapping array must be used to configure where the files containing the x-amazon-apigateway-integration blocks are located.

openApiIntegration:
    package: true #New feature! Hook into the package & deploy process
    inputFile: schema.yml
    mapping:
       - stage: [dev, prod] #multiple stages
         path: integrations
       - stage: test #single stage
         path: mocks

In the above example all YML files inside the integrations directory will be processed and merged with the schema.yml file when deploying the dev stage

serverless deploy --stage=dev

To use a different x-amazon-apigateway to perform functional tests (with mocking responses e.g) the directory mock is processed and merged with the schema.yml file when deploying the test stage

serverless deploy --stage=test

Usage

You can setup a fully working API GATEWAY with any openApi 3.0 specification file First create the input file containing the OpenApiSpecification

# ./schema.yml
openapi: 3.0.0
info:
  description: User Registration
  version: 1.0.0
  title: UserRegistration
paths:
  /api/v1/user:
    post:
      summary: adds a user
      requestBody:
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/Customer'
      responses:
        '201':
          description: user created
components:
  schemas:
    Customer:
      type: object
      required:
        - email_address
        - password
      properties:
        email_address:
          type: string
          example: test@example.com
        password:
          type: string
          format: password
          example: someStrongPassword#

The plugin will generate the x-amazon-apigateway integrations objects for all methods that do not have an integration.

#generate a file containing a gateway mock integration in the directory /mocks
serverless integration create --output mocks --type mock --stage=test

#generate a file containing the production integration in the directory integrations/
serverless integration create --output integrations --type http --stage=prod

Supported types are

  • http_proxy
  • http
  • aws
  • aws_proxy
  • mock

The plugin now generates a merged file during deployment that is automatically injected in your serverless resources

#Create OpenApi File containing mocking responses (usable in functional tests) and deploy to ApiGateway
serverless deploy --stage==test
#Create OpenApi File containing the production integration and deploy to ApiGateway
serverless deploy --stage=prod

The generated output is automatically injected in the resources.Resources.YOUR_API_GATEWAY.Properties.Body property

resources:
  Resources:
    ApiGatewayRestApi:
      Type: AWS::ApiGateway::RestApi
      Properties:
        ApiKeySourceType: HEADER
        Body: ~ #autogenerated by plugin
        Description: "Some Description"
        FailOnWarnings: false
        Name: ${opt:stage, self:provider.stage}-some-name
        EndpointConfiguration:
          Types:
            - REGIONAL
    ApiGatewayDeployment:
      Type: AWS::ApiGateway::Deployment
      Properties:
        RestApiId:
          Ref: ApiGatewayRestApi
        StageName: ${opt:stage, self:provider.stage}

Commands

Manual merge

The generate command can be used independently with

serverless integration merge --stage=dev

Of course then the API Gateway Body property has to be specified manually

resources:
  Resources:
    ApiGatewayRestApi:
      Type: AWS::ApiGateway::RestApi
      Properties:
        ApiKeySourceType: HEADER
        Body: ${file(openapi-integration/api.yml)}

CORS generator

The plugin can generate full CORS support out of the box.

openApiIntegration:
  cors: true
  ...

If enabled, the plugin generates all required OPTIONS methods as well as the required header informations and adds a mocking response to API Gateway. You can customize the CORS templates by placing your own files inside a directory openapi-integration (in your project root). The following files can be overwritten:

FilenameDescription
headers.ymlAll headers required for CORS support
integration.ymlContains the x-amazon-apigateway-integration block
path.ymlOpenApi specification for the OPTIONS method
response-parameters.ymlThe response Parameters of the x-amazon-apigateway-integration responses

See the EXAMPLES directory for detailed instructions.

Auto Mock Generator

If enabled, the plugin generates mocking responses for all methods that do not have an x-amazon-apigateway-integration block defined. It takes the first 2xx response defined in the openApi specification and generates a simple mocking response on the fly

openApiIntegration:
  autoMock: true
  ...

When using the autoMock feature, you do not need to specify inputPath mappings, since all endpoints are mocked automatically

openApiIntegration:
    package: true
    inputFile: schema.yml
    mapping: ~

VALIDATION generator

The plugin supports full request validation out of the box

openApiIntegration:
  validation: true
  ...

If enabled, the plugin generates the x-amazon-apigateway-request-validators blocks and adds a basic request validation to all methods. You can customize the VALIDATION template by placing your own files inside a directory openapi-integration (in your project root). The following files can be overwritten:

FilenameDescription
request-validator.ymlThe x-amazon-apigateway-request-validators block

See the EXAMPLES directory for detailed instructions.

Proxy Manager

The proxymanager feature automates the complete generation of an HTTP proxy integration. You only have to define the target URL and all necessary AWS integration blocks are generated on-the-fly during deployment.

openApiIntegration:
   cors: true
   validation: true
   mapping:
      - stage: [dev, prod]
        proxyManager:
           type: http_proxy
           baseUrl: https://www.example.com
           pattern: "(?<=api\/v1)\/.+"
  ...

With this setting, no separate integration files need to be created

A combination of your own and auto-generated files is still possible without any problems

Proxy Manager configuration

type

at the moment only http_proxy supported

baseUrl

The base url is required to map the path variable from the openapi specification to the URI from the aws integration.

Example:

#original openapi specification
paths:
   /api/v1/user:
    post:
      ... 

will be translated to

#generated openapi specification output
paths:
   /api/v1/user:
      post:
         ...
         x-amazon-apigateway-integration:
            type: http_proxy
            passthroughBehavior: when_no_match
            httpMethod: POST
            uri: https://www.example.com/api/v1/user

pattern

The pattern can be used to adapt the mapping of the base url using regexp, to remove a prefix, or a version string

Example:

baseUrl: https://www.example.com
pattern: "(?<=api\/v1)\/.+"

will translate the route /api/v1/user to https://www.example.com/user

Configuration Reference

configure the plugin under the key openApiIntegration

openApiIntegration:
  inputFile: schema.yml #required
  package: true #optional, defaults to false 
  inputDirectory: ./ #optional, defaults to ./
  cors: true #optional, defaults to false
  autoMock: true #optional, defaults to false
  validation: true #optional, defaults to false
  mapping: #optional, can be completely blank if autoMock option is enabled
    - stage: [dev, prod] #multiple stages
      path: integrations
      proxyManager: #optional
         type: http_proxy
         baseUrl: https://example.com
         pattern: "(?<=v1)\/.+"
    - stage: test #single stage
      path: mocks/customer.yml
  outputFile: api.yml #optional, defaults to api.yml
  outputDirectory: openapi-integration #optional, defaults to ./openapi-integration

Known Issues

Stage deployment

When using serverless framework only to deploy your aws resources without having any lambda functions or triggers, the AWS Gateway deploymemt does not behave as expected. Any deployment to an existing stage will be ignored, since CloudFormation does not redeploy a stage if the DeploymentIdentifier has not changed.

The plugin serverless-random-gateway-deployment-id solves this problem by adding a random id to the deployment-name and all references to it on every deploy

See the examples folder for a full working example

Variable Resolving

Serverless variables inside the openapi integration files are not resolved correctly when using the package & deploy hooks. This problem can be solved by using the api gateway STAGE VARIABLES.

See the examples folder for a full working example

Example

service:
  name: user-registration

provider:
  name: aws
  stage: dev
  region: eu-central-1

plugins:
  - serverless-openapi-integration-helper
  
openApiIntegration:
  inputFile: schema.yml
  package: true
  mapping:
    - path: integrations
      stage: [dev, prod]
    - path: mocks/customer.yml
      stage: test

functions:

resources:
  Resources:
    ApiGatewayRestApi:
      Type: AWS::ApiGateway::RestApi
      Properties:
        ApiKeySourceType: HEADER
        Body: ~
        Description: "Some Description"
        FailOnWarnings: false
        Name: ${opt:stage, self:provider.stage}-some-name
        EndpointConfiguration:
          Types:
            - REGIONAL
    ApiGatewayDeployment:
      Type: AWS::ApiGateway::Deployment
      Properties:
        RestApiId:
          Ref: ApiGatewayRestApi
        StageName: ${opt:stage, self:provider.stage}
serverless deploy --stage=test
serverless deploy --stage=prod

Approach to a functional test of schema validation

The plugin works well in combination with the serverless-plugin-test-helper to automate tests against the deployed api gateway

Install The plugin test helper package

npm install --save-dev serverless-plugin-test-helper

add the plugin as a plugin dependency in your serverless configuration file and configure the plugin according to the Readme

#./serveless.yml
plugins:
  - serverless-plugin-test-helper
  - serverless-openapi-integration-helper

[...]

resources:
   Outputs:
      GatewayUrl: # This is the key that will be used in the generated outputs file
         Description: This is a helper for functional tests
         Value: !Join
            - ''
            - - 'https://'
              - !Ref ApiGatewayRestApi
              - '.execute-api.'
              - ${opt:region, self:provider.region}
              - '.amazonaws.com/'
              - ${opt:stage, self:provider.stage}

   Resources:
    ApiGatewayRestApi:
      Type: AWS::ApiGateway::RestApi
      Properties:
        ApiKeySourceType: HEADER
        Body: ~
        Description: User Registration (${opt:stage, self:provider.stage})
        FailOnWarnings: false
        Name: ${opt:stage, self:provider.stage}-gateway
        EndpointConfiguration:
          Types:
            - REGIONAL
    ApiGatewayDeployment:
      Type: AWS::ApiGateway::Deployment
      Properties:
        RestApiId:
          Ref: ApiGatewayRestApi
        StageName: ${opt:stage, self:provider.stage}

Testing the schema validation

Add a functional test (e.g. with jest)

//tests/registration.js
import {getOutput} from 'serverless-plugin-test-helper';
import axios from 'axios';

axios.defaults.adapter = require('axios/lib/adapters/http'); //Todo

const URL = getOutput('GatewayUrl');
test('request validation on registration', async () => {
    expect.assertions(1);
    const {status} = await axios.post(URL + '/api/v1/user',
        {
            "email_address": "test@example.com",
            "password": "someStrongPassword#"
        },
        {
            headers: {
                'Content-Type': 'application/json',
            }
        });
    expect(status).toEqual(201);
});

test('request validation on registration (invalid request)', async () => {
    expect.assertions(1);
    try {
        await axios.post(URL + '/api/v1/user',
            {
                "email": "test@example.com"
            },
            {
                headers: {
                    'Content-Type': 'application/json',
                }
            });
    } catch (e) {
        expect(e.response).toMatchObject({
            statusText: 'Bad Request',
            status: 400
        });
    }
});

Then perform the functional test

serverless deploy --stage=test
npm test
serverless remove --stage=test

The command will

  • merge the openapi specification file with the MOCK integration configured before
  • deploy to API Gateway in an isolated TEST infrastructure environment (your other environments will not be affected since we are deploying to a separated gateway)
  • perform the test and verify that schema validation is correct
  • remove all TEST resources if test succeeded

See the examples folder for a full working example

Feedback is appreciated! If you have an idea for how this plugin/library can be improved (or even just a complaint/criticism) then please open an issue.

Author: yndlingsfar
Source Code: https://github.com/yndlingsfar/serverless-openapi-integration-helper 
License: MIT license

#serverless #openapi #aws