[ Umbrella issue ] Crowdsourcing Communities in Asia

[afro-coder]

Aim

To start building the database we need to start figuring out a way to get stuff into it.

This issue is to track things like

1. Organizers and their events in the following format

Community name
Description
Website
Region
Theme

2. Maintain a way to automate data filling, still looking into it, few initial thoughts is Google Forms -> Google Sheets -> Writing a script there in google sheets to push the data to github, last part could be done via Github GraphQL.

[divya-mohan0209]

@afro-coder Thanks for this issue!

As part of this umbrella issue, I will branch out to create another issue that captures the data ONLY.

This main issue can be used to track the logistics around the whole task. As and when the time comes, we will open an issue for the second point i.e. the creation of the website.

[Roushan-kr]

@afro-coder as you suggested, we are now capturing community data using a Google Sheet, and we’ve added the sheet link in the project description for easy edits.

We then fetch the latest data from this Google Sheet using the Google Sheets API, and programmatically update our README.md file with that data in a markdown table format.

Here’s a simple script that does the job:

const fs = require("fs");
const { google } = require("googleapis");
const path = require("path");
require("dotenv").config();

async function getSheetData() {
  const auth = new google.auth.GoogleAuth({
    keyFile: "credentials.json",
    scopes: ["https://www.googleapis.com/auth/spreadsheets.readonly"],
  });

  const sheets = google.sheets({ version: "v4", auth: await auth.getClient() });
  const range = "Sheet1!A2:E"; // Assuming headers are in row 1

  const response = await sheets.spreadsheets.values.get({
    spreadsheetId: process.env.SHEET_ID,
    range,
  });

  return response.data.values;
}

function formatAsMarkdownTable(rows) {
  const header = "| Name | Type | Website | Contact |\n|------|------|---------|---------|";
  const lines = rows.map(row => {
    const [name, type, website, contact] = row;
    return `| ${name || ""} | ${type || ""} | ${website || ""} | ${contact || ""} |`;
  });
  return [header, ...lines].join("\n");
}

function updateReadme(newTable) {
  const readmePath = path.join(__dirname, "README.md");
  let content = fs.readFileSync(readmePath, "utf-8");

  const startTag = "<!--START_SECTION:communities-->";
  const endTag = "<!--END_SECTION:communities-->";
  const regex = new RegExp(`${startTag}[\\s\\S]*?${endTag}`, "gm");

  const newSection = `${startTag}\n${newTable}\n${endTag}`;
  const updated = content.replace(regex, newSection);

  fs.writeFileSync(readmePath, updated, "utf-8");
}

(async () => {
  const data = await getSheetData();
  const markdownTable = formatAsMarkdownTable(data);
  updateReadme(markdownTable);
  console.log("✅ README updated with data from Google Sheets.");
})();

---

✅ What This Script Does

• Authenticates using Google Service Account credentials (credentials.json).

• Fetches all rows from your Google Sheet.

• Formats the data into a markdown table like:

  | Name         | Type        | Website               | Contact          |
  |--------------|-------------|------------------------|------------------|
  | PyCon India  | Conference  | https://in.pycon.org   | (leave empty if N/A) |

• Replaces the content between the markers in README.md:

  <!--START_SECTION:communities-->
  (auto-updated table here)
  <!--END_SECTION:communities-->

• Overwrites the file locally with the new content.

---

🔧 Setup Required

1. Add the following to your .env:

   SHEET_ID=your_google_sheet_id
   GIT_AUTHOR_NAME=Your Name
   GIT_AUTHOR_EMAIL=your@email.com

2. Place your downloaded Google service account key as credentials.json in the root.

3. one issue is that it need mannual removal and update if sheet is not public edit to resoleve this we may create a seprate db insted of sheet if you allow

---

This keeps the community list updated automatically and makes collaboration smooth and non-technical. Anyone can add a new row in the Google Sheet, and it reflects in the repo after running the script!

we also fallow github action to perform incremental update

[divya-mohan0209]

Hi @Roushan-kr , thanks for this. I assume you’ve reached out after seeing the listing on C4GT. Please note that the tracking issue has already been assigned to someone else. If you’d like to work on something else, we will be opening more issues up in the Open Community. We request you to keep an eye out for the same.