DEV Community: Mahesh Prajapati

Fetch and Convert Google Sheets Data to JSON with PHP

Mahesh Prajapati — Thu, 09 Jan 2025 09:51:31 +0000

If you're working with Google Sheets and you need to make its data accessible as JSON for a web application or API, PHP provides a simple way to get, parse, and convert CSV data from Google Sheets. In this post, I'll walk you through a PHP script that gets data from public Google Sheets in CSV format and converts it into a structured JSON response.

Why use Google Sheets as JSON?

Google Sheets are widely used to organize data. Whether for prototyping, content management, or a lightweight database solution, having the ability to convert Google Sheets to JSON opens up many possibilities for dynamic web applications.

Here’s the complete PHP script:

<?php
// Array of sheet URLs with their respective IDs
$sheets = [
    'sheet1' => "https://docs.google.com/spreadsheets/d/e/2PACX-1vQawhdv3OZSq4n3DTEIwY6aID5otU3KTk_BYUUHc8nuCQNerFA0xdWRsd68z4aIpUs3JDFXohjsvJKy/pub?gid=0&single=true&output=csv",
    'sheet2' => "https://docs.google.com/spreadsheets/d/e/2PACX-1vQawhdv3OZSq4n3DTEIwY6aID5otU3KTk_BYUUHc8nuCQNerFA0xdWRsd68z4aIpUs3JDFXohjsvJKy/pub?gid=1073107567&single=true&output=csv",
];

// Set response type as JSON
header('Content-Type: application/json');

try {
    // Get the requested sheet identifier from the query parameter
    $sheet = $_GET['sheet'];

    // Validate the sheet identifier
    if (!isset($sheets[$sheet])) {
        throw new Exception("Invalid sheet identifier.");
    }

    // Fetch CSV data from Google Sheets
    $csvData = file_get_contents($sheets[$sheet]);
    if ($csvData === FALSE) {
        throw new Exception("Failed to fetch data from Google Sheets.");
    }

    // Parse CSV data into an array
    $rows = array_filter(array_map('str_getcsv', explode("\n", $csvData))); // Remove empty rows
    $headers = array_shift($rows); // First row as headers

    if (!$headers || empty($rows)) {
        throw new Exception("Invalid or empty CSV data.");
    }

    // Convert CSV rows to associative array
    $menu = array_map(function($row) use ($headers) {
        $row = array_map('trim', $row); // Trim whitespace
        if (count($row) !== count($headers)) {
            return null; // Skip rows with missing fields
        }
        return array_combine($headers, $row);
    }, $rows);

    // Filter out invalid rows
    $menu = array_filter($menu);

    // Return JSON response
    echo json_encode($menu);

} catch (Exception $e) {
    // Handle errors
    http_response_code(500);
    echo json_encode(['error' => $e->getMessage()]);
}

How It Works

1. Google Sheets Setup:
Ensure your Google Sheet is published as a CSV. Go to File > Share > Publish to Web and copy the CSV link.

2. Mapping Sheet URLs:
The $sheets array maps user-friendly sheet identifiers (e.g., sheet1, sheet2) to their corresponding Google Sheets URLs.

3. Fetching Data:
The script uses PHP’s file_get_contents() to retrieve the CSV content.

4. Parsing CSV:
The data is parsed into an array using str_getcsv() and converted into an associative array with headers as keys.

5. JSON Conversion:
The processed data is encoded as JSON and sent back as the response.

6. Error Handling:
Errors such as invalid sheet identifiers, failed fetches, or malformed data are handled gracefully, returning appropriate error messages.

Example Usage

1. Request Format:
Call the script via URL, passing the sheet identifier as a query parameter:

http://yourdomain.com/sheet-fetcher.php?sheet=sheet1

2. Expected JSON Response:
For a sheet with the following content:

Name,Age,City
Alice,30,New York
Bob,25,San Francisco

The JSON output will be:

[
    { "Name": "Alice", "Age": "30", "City": "New York" },
    { "Name": "Bob", "Age": "25", "City": "San Francisco" }
]

Error Responses

The script includes robust error handling. For example:

Invalid Sheet Identifier:

{ "error": "Invalid sheet identifier." }

Fetch Error:

{ "error": "Failed to fetch data from Google Sheets." }

Advantages of This Approach

Dynamic Data: Updates in Google Sheets are reflected in real-time.
Simple Integration: No external libraries required; works with plain PHP.
Flexible: Can handle multiple sheets using a single script.

This script is a simple yet powerful way to make Google Sheets data accessible via a JSON API. Whether you’re building a frontend app, creating dashboards, or exposing APIs, this technique will save you time and effort.

Creating a Social Media Marketing Strategy for FMCG Brands in 2025

Mahesh Prajapati — Mon, 30 Dec 2024 12:33:18 +0000

In 2025, the FMCG sector is rapidly evolving, with social media becoming an integral part of connecting brands with consumers. As shopping habits are changing and digital platforms are growing, it is important for brands to optimize their social media presence for meaningful engagement. Let’s explore how FMCG brands can create effective strategies to thrive in this constantly changing landscape.

Key Social Media Strategies for FMCG Brands in 2025

Social Commerce Integration into the Customer Journey
Social commerce has become a cornerstone for FMCG brands. With platforms like Instagram Shops and TikTok Shopping enabling seamless buying experiences directly within feeds, brands must focus on creating shoppable content that reduces barriers to impulse purchases.

Video as the Primary Form of Content
Short videos continue to dominate, but in 2025, they go beyond mere entertainment. Brands should focus on value-driven content, such as product demonstrations, behind-the-scenes sustainability practices, quick recipes, and real-time customer experiences.

Transparency and Direct Customer Engagement
Consumers demand transparency from FMCG brands. Clear communication regarding ingredients, sustainability initiatives, and price-value propositions helps foster trust. Direct engagement through customer feedback and addressing complaints in real-time is crucial.

Personalization at Scale
Leveraging AI for hyper-personalization enables brands to deliver tailored experiences. From personalized product recommendations based on behavior to culturally aligned content, personalization enhances the customer journey across social media.

Building and Strengthening Communities
Community-building is the future. Exclusive groups, product launches, live Q&A sessions, and social challenges create deeper connections with customers and amplify brand advocacy.

Sustainability Storytelling
Sustainability is no longer optional. Brands should showcase eco-friendly packaging, waste minimization techniques, and their journey towards carbon neutrality. Social media becomes a powerful platform for storytelling in sustainability efforts.

Influencer Alliances and Augmented Reality
Collaborating with micro-influencers who align with brand values and integrating Augmented Reality for virtual product trials and interactive experiences enhances engagement and builds meaningful connections.

Cross-Platform Integration
Consistency across platforms is vital. A unified brand message with platform-optimized content ensures a seamless experience for customers, regardless of where they interact.

Informed Decisions with Advanced Analytics
Using advanced analytics to track consumer sentiment, competitive positioning, and campaign impact helps brands refine their strategies for better performance.

In 2025, FMCG brands must balance new-age technologies with human-centric approaches to create meaningful relationships with consumers. Social media marketing is no longer just about promotion; it’s about fostering ecosystems where consumers explore, engage, and advocate for brands they trust. Embracing these strategies thoughtfully will lead to both short-term sales growth and long-term brand equity.

Why CSS Grid Isn’t Enough for Masonry Layouts

Mahesh Prajapati — Thu, 26 Dec 2024 09:56:10 +0000

An easy-to-use method for implementing masonry layouts has long been sought for by the web developer community. It has been difficult to create these aesthetically dynamic grids using only CSS, thanks to Pinterest and related designs. The Chrome team contends that this strategy might not be the best one, despite recent recommendations that call for masonry capabilities to be added to the CSS Grid Layout specification. Here are some reasons why we think masonry should have its own layout technique and some potential advantages for developers.

The Case Against Adding Masonry to CSS Grid

1. Performance Concerns

CSS Grid and masonry layouts handle item placement in fundamentally different ways:

CSS Grid: All items are placed before layout, allowing the browser to calculate exact track sizes and placements.
Masonry: Items are placed as they are laid out, requiring dynamic calculations that can lead to significant performance issues when mixing fixed and intrinsic track sizes.

Consider a grid with mixed track definitions like grid-template-columns: 200px auto 200px. With masonry, the browser must pre-layout every item in every possible configuration, creating exponential complexity in large grids. This is especially problematic when using advanced features like subgrids.

To avoid shipping a layout method with such inherent limitations, we propose a solution that separates masonry from CSS Grid.

2. Specification Complexity

Merging masonry into the grid specification introduces inconsistencies that conflict with the core principles of formatting contexts:

Alignment Properties: Grid supports six alignment properties, but masonry would only use a subset, like flexbox.
Placement Properties: Grid has four placement properties (e.g., grid-column-start), while masonry would need only two.
Track Sizing: Certain patterns like grid-template-columns: repeat(auto-fill, max-content) make sense in masonry but must remain invalid in grid.

Introducing these discrepancies increases the cognitive load for developers, as they would need to remember which features work in which context. This fragmentation could lead to confusion and errors.

The Proposal: Masonry as a Separate Layout Method

Instead of bundling masonry with CSS Grid, we advocate defining it as a standalone layout method using display: masonry. This approach retains all the flexibility developers love about grid while avoiding the pitfalls outlined above.

Example

Classic Masonry Layout

A simple masonry layout with equal-sized columns can be achieved with:

.masonry {
  display: masonry;
  masonry-template-tracks: repeat(auto-fill, minmax(14rem, 1fr));
  gap: 1rem;
}

Mixed Track Sizes

For layouts with alternating narrow and wide columns:

.masonry {
  display: masonry;
  masonry-template-tracks: repeat(auto-fill, minmax(8rem, 1fr) minmax(16rem, 2fr)) minmax(8rem, 1fr);
  gap: 1rem;
}

Auto-Sized Tracks

Allow tracks to auto-size based on content:

.masonry {
  display: masonry;
  masonry-template-tracks: repeat(auto-fill, auto);
  gap: 1rem;
}

Spanning and Placement

Enable items to span multiple tracks:

.masonry {
  display: masonry;
  masonry-template-tracks: repeat(auto-fill, auto);
}

.span-2 {
  masonry-track: span 2; /* spans two columns */
}

.placed {
  masonry-track: 2 / 5; /* covers tracks 2, 3, and 4 */
}

Benefits of a Separate Masonry Layout

Clarity: Developers can use masonry without worrying about the nuances of CSS Grid compatibility.

Flexibility: All grid-like features remain available without introducing new constraints.

Future-Proofing: A dedicated masonry specification ensures consistent behavior across browsers and avoids unnecessary complexity.

Understand SessionStorage and LocalStorage for Controlling Popups

Mahesh Prajapati — Tue, 24 Dec 2024 12:05:59 +0000

When considering sessionStorage and localStorage for managing website popups, the main difference is in the duration of data storage and the way the popup is displayed.

1. sessionStorage

Data Life: Data persists only for the duration of the browser session. Once the tab or browser is closed, the data is cleared.

Use Case:

Use sessionStorageif the popup should reappear each time the user opens the site in a new browser session.
Example: You want to show a welcome popup only during a user's current session and not if they refresh the page or open the site in a different tab.

if (!sessionStorage.getItem('popupDisplayed')) {
    // Display popup
    alert('Welcome to the website!');
    sessionStorage.setItem('popupDisplayed', 'true');
}

2. localStorage

Data Life: Data persists even after the browser is closed, until explicitly cleared by the user or via script.

Use Case:
Use localStorage if the popup should remain hidden across multiple sessions once a user has seen it.
Example: You want to display a promo popup only once a week or never again after the user dismisses it.

if (!localStorage.getItem('popupDisplayed')) {
    // Display popup
    alert('Check out our special offer!');
    localStorage.setItem('popupDisplayed', 'true');
}

Key Differences for Popup Management:

Feature	sessionStorage	localStorage
Data Persistence	Only for the current session.	Persists indefinitely or until cleared.
Scope	Tab-specific.	Shared across all tabs/windows of the same origin.
When to Use	Temporary popups (e.g., session-only welcome message).	Persistent control (e.g., don't show again for a returning user).

Decision Criteria:

Short-term Popup Logic: Use sessionStorage if you want the popup to reappear in a new session.
Persistent Popup Logic: Use localStorage if the popup logic needs to persist even after the browser or tab is closed.

For more complicated situations, you may even use custom logic to mix both storages (e.g., session-based for a week).

Creating a Festive Christmas Web Page with Snowfall Animation

Mahesh Prajapati — Mon, 23 Dec 2024 12:56:13 +0000

In this post, I'll demonstrate how to use HTML, CSS, and JavaScript to construct a straightforward yet captivating Christmas-themed webpage with a snowfall animation.

Features:

Snowfall Effect: Falling snowflakes animated using JavaScript and CSS.
Christmas Greeting: A festive greeting message styled with CSS.
Interactive: Snowflakes move randomly and reset after reaching the bottom of the screen.

How It Works:

Canvas Animation: The canvas element is used for the snowfall effect. JavaScript manages the properties and animations of the snowflakes.
Responsive Design: The canvas adapts to window size, ensuring the snowfall covers the entire screen.
Festive Styling: CSS enhances the visual experience with vibrant colors and glowing text effects.

How to Install Magento 2 on Shared Hosting ?

Mahesh Prajapati — Fri, 20 Dec 2024 09:26:15 +0000

Because of its resource requirements and server limitations, Magento 2 might be difficult to install on shared hosting. If the hosting environment satisfies Magento's minimal criteria, it is feasible.

Pre-Installation Checklist

1. Verify Hosting Requirements:

PHP version: 7.4+ (or the latest version supported by Magento).
MySQL version: 5.7+ or MariaDB 10.4+.
Elasticsearch: May not be available on shared hosting (required for Magento 2.4+).
PHP Extensions: intl, soap, bcmath, gd, mbstring, xsl, cURL, zip, etc.
Composer Support: Some shared hosting providers include Composer, or you can use it locally.

2. Secure FTP and Database Access:

FTP credentials for uploading files.
A database (MySQL/MariaDB) created through cPanel.

3. Magento Package:

Download the Magento 2 ZIP file from the Magento Downloads page.

4. Domain or Subdomain:

Ensure your domain or subdomain points to the shared hosting account.

Step-by-Step Installation

1. Upload Magento Files

Download the Magento 2 ZIP package.
Extract it locally and upload the contents to your server using FTP or cPanel's File Manager.

2. Set File Permissions

After uploading, set the correct permissions: Folders: 755 Files: 644
Some shared hosting environments might require additional permission adjustments for pub, var, and generated directories.

3. Create a Database

Log in to cPanel.
Go to MySQL Databases:
Create a new database.
Create a new user and assign it to the database with ALL PRIVILEGES.

4. Configure Magento Installation

Navigate to your domain or subdomain in a browser (e.g., https://yourdomain.com).

The Magento Setup Wizard will start:

Readiness Check: Ensure your hosting meets the requirements.
Add Database Details: Enter the database name, username, and password.

Web Configuration:

Specify the Base URL (e.g., https://yourdomain.com).
Enable/disable HTTPS based on your SSL setup.
Admin Account: Create an admin username, password, and email.
Installation Path: Magento will configure itself.

5. Post-Installation Tasks

Set File Permissions Again:

chmod -R 775 pub var generated
chmod u+x bin/magento

You may need to do this via SSH or ask your hosting provider for assistance.

Run Indexers (Optional): If SSH access is available:

php bin/magento indexer:reindex
php bin/magento cache:flush

Without SSH, Magento automatically handles this after installation.

6. Fix Common Shared Hosting Issues

Cron Jobs: Configure cron jobs via cPanel for Magento's background processes. Example:

*/5 * * * * php /home/username/public_html/bin/magento cron:run

Elasticsearch Workaround: Magento 2.4+ requires Elasticsearch. If not available, use an earlier Magento version (like 2.3.x) or request Elasticsearch from your hosting provider.

Troubleshooting Tips

Slow Performance: Shared hosting is limited in resources; consider upgrading to VPS or cloud hosting.
Readiness Check Fails: Ensure all required PHP extensions are installed/enabled. Contact your hosting provider for assistance.
Composer Errors: Use Composer locally to install dependencies, then upload the files to the server.

Magento 2 may work on shared hosting for small-scale stores or testing environments. For a production site, VPS or dedicated hosting is highly recommended for better performance, security, and scalability.

Magento 2 commands for maintenance and deployment

Mahesh Prajapati — Thu, 19 Dec 2024 06:57:07 +0000

These commands are essential for maintaining Magento 2 and ensuring smooth operations in both development and production environments. They handle tasks like database migrations, optimizing code execution, preparing frontend assets, and clearing cache.

1. php bin/magento setup:upgrade

Purpose: Applies any new database schema updates, runs data patches, and sets up newly installed or upgraded modules.

When to use:

After installing/upgrading modules or themes.
After modifying database-related configurations.

2. php bin/magento setup:di:compile

Purpose: Compiles dependency injection (DI) configurations into generated code for faster execution and performance.

When to use:

After making changes to module code.
After enabling/disabling a module.
In production mode, before deploying.

3. php bin/magento setup:static-content:deploy –f

Purpose: Deploys static view files (CSS, JavaScript, images) into the pub/static directory.

When to use:

After making changes to frontend files (e.g., LESS, CSS, JS).
Before deploying your store in production mode.

4. php -dmemory_limit=6G bin/magento setup:static-content:deploy -f

Purpose: Same as above, but this version overrides PHP’s default memory limit to 6 GB during the deployment process.

When to use:

Use this when you encounter memory-related errors during static content deployment.

5. php bin/magento indexer:reindex

Purpose: Rebuilds Magento’s indexes to optimize database queries and improve store performance.

When to use:

After making changes to product, category, or other data in the database that affects storefront performance.

6. php bin/magento c:f

Purpose: This is shorthand for php bin/magento cache:flush, which clears all cached data from Magento.

When to use:

After making any configuration changes.
When cached data might cause unexpected behavior or prevent new changes from appearing.

[Boost]

Mahesh Prajapati — Thu, 19 Dec 2024 04:48:08 +0000

Here's an easy way to create a Magento 2 eCommerce custom theme

Mahesh Prajapati ・ Dec 18

#magneto #themes #frontend #coding

Here's an easy way to create a Magento 2 eCommerce custom theme

Mahesh Prajapati — Wed, 18 Dec 2024 12:32:28 +0000

1. Understand Magento's Theme Structure

Magento themes are located in the directory:
app/design/frontend/<Vendor>/<ThemeName>

<Vendor>: Your company or brand name (e.g., MyCompany).
<ThemeName>: The name of your theme (e.g., MyTheme).

2. Create the Directory Structure

Navigate to the app/design/frontend/ directory.

Create your custom folders:

app/design/frontend/MyCompany/MyTheme

Inside the MyTheme folder, create these subdirectories:

etc
web
templates
layouts

etc: Configuration files.
web: Static assets (CSS, JavaScript, images).
templates: HTML and PHTML files.
layouts: XML layout files.

3. Create the theme.xml File

This file declares your theme. Place it inside the etc folder:

app/design/frontend/MyCompany/MyTheme/etc/theme.xml

Example content:

<?xml version="1.0"?>
<theme xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Config/etc/theme.xsd">
    <title>MyCompany MyTheme</title>
    <parent>Magento/blank</parent>
    <media>
        <preview_image>media/preview.jpg</preview_image>
    </media>
</theme>

4. Register Your Theme

Create a registration.php file in the root of your theme directory:

app/design/frontend/MyCompany/MyTheme/registration.php

Example content:

<?php
use Magento\Framework\Component\ComponentRegistrar;

ComponentRegistrar::register(
    ComponentRegistrar::THEME,
    'frontend/MyCompany/MyTheme',
    __DIR__
);

5. Add Static Assets

CSS: Create a custom stylesheet in web/css/styles.css.
JavaScript: Place your JS files in web/js/.
Images: Add images to web/images/.

6. Configure Layouts

Define layout changes using XML files in the layout folder.
Example: default.xml

app/design/frontend/MyCompany/MyTheme/layout/default.xml

Example content:

<?xml version="1.0"?>
<page xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:View/Layout/etc/page_configuration.xsd">
    <body>
        <referenceContainer name="header">
            <block class="Magento\Framework\View\Element\Template" name="custom.block" template="Magento_Theme::html/header.phtml"/>
        </referenceContainer>
    </body>
</page>

7. Configure Your Theme in Admin Panel

Go to the Magento Admin Panel.
Navigate to Content > Design > Themes.
Your theme should appear here. Assign it as the default theme.

8. Test Your Theme

Clear caches: php bin/magento cache:clean.
Test your changes by visiting your store.

9. Customize as Needed

Modify PHTML templates for frontend changes.
Add widgets and blocks for dynamic content.

This step-by-step process should help you get started with a Magento custom theme. Let me know if you need detailed guidance on specific steps!

Building Modern Websites and Managing Online Presence with Expertise in Magento, WordPress, & Shopify

Mahesh Prajapati — Wed, 18 Dec 2024 06:48:28 +0000

👋 Hi there! I'm Mahesh Prajapati, a passionate frontend developer with experience in creating beautiful, responsive, and user-friendly websites and web applications.
About Me

I specialize in crafting modern, intuitive, and accessible web experiences using a variety of technologies:

Frontend Technologies: HTML5, CSS3, JavaScript, React, Next.js, Tailwind CSS
E-Commerce Platforms: Magento, WordPress, Shopify
SEO, Performance Optimization, and UI/UX Design
Social Media Management for growing online presence

With 10+ of experience, I focus on delivering clean, optimized, and engaging web solutions tailored to meet client needs. Whether it's building a custom website or managing a brand's online presence, I strive to exceed expectations in every project.
Freelance Work

I'm excited to take on freelance projects where I can leverage my skills in front-end development and digital marketing. Whether it's a complete website overhaul or managing social media accounts for brand growth, I provide comprehensive solutions tailored to your business needs.
What I Offer

Responsive Web Design & Development
Custom Front-end Solutions
Tailwind CSS for Modern UI Design
Next.js & Static Site Generation
Magento, WordPress, Shopify E-commerce Development
Social Media Management & Marketing
Performance Optimization & SEO Best Practices Contact Me

Feel free to reach out to discuss your project or just to connect!

Email: thepixxel@gmail.com

Looking forward to collaborating and building something amazing together!

Dynamic Box Shadow on Mouse Move

Mahesh Prajapati — Wed, 18 Dec 2024 06:14:10 +0000

Interactive UI effects can significantly enhance the user experience of your website. One such effect is the Dynamic Box Shadow, where the shadow of an element moves in response to the mouse position, creating a subtle yet engaging interaction.

This effect is achieved by combining basic HTML, CSS, and JavaScript to track mouse movements and dynamically adjust the box shadow properties of an element. It's a great way to add depth and interactivity to your design.

Why Use Dynamic Box Shadows?

Enhanced Visual Appeal: Moving shadows add a touch of sophistication and interactivity to otherwise static designs.

User Engagement: Such effects create a more interactive experience, encouraging users to explore your site.

Lightweight Implementation: This effect is simple to implement without relying on heavy libraries or plugins.

Customization Ideas

Intensity Adjustment: You can control the shadow's movement intensity to suit your design style.

Color Variations: Experiment with different shadow colors to complement your theme.

Interactive Elements: Apply the effect to buttons, cards, or other focus elements for better user engagement.

Bring your designs to life with this simple yet powerful effect. Check out the implementation and customize it for your projects!

Happy coding!

Interactive Snowfall Cursor Effect with CSS and JavaScript

Mahesh Prajapati — Tue, 17 Dec 2024 13:08:24 +0000

Introduction

Creating visually engaging web effects can significantly enhance the user experience on websites. One such captivating effect is the Interactive Snowfall Cursor Effect, where snowflakes are generated as the user moves the mouse. In this post, we’ll walk through how to create this stunning effect using a combination of CSS and JavaScript.

Check out the full implementation on CodePen:

Interactive Snowfall Cursor Effect

What You'll Learn

How to create a snowfall effect using CSS and JavaScript.
Implementing a dynamic cursor interaction for generating snowflakes.
Adding random sizes and speeds to enhance visual variety.

Conclusion

The combination of CSS for styling and JavaScript for interactivity creates a stunning snowfall effect that enhances user engagement. This approach allows for endless customization, from different shapes and colors to varied animation speeds, making your website more dynamic and visually appealing.

If you have any questions or suggestions, feel free to leave a comment below. Happy coding! ❄️