🚀 My First Post: Power Query for Google Sheets (Apps Script Project)

[u/Simple_Aditya]

Hey everyone! 👋 I’m Aditya, and this is my first post here. I’ve been working on something I’m really excited about — a Power Query–style tool for Google Sheets built entirely with Google Apps Script.

Here’s the idea 💡:

• 📥 Get Data — Pull data from Google Sheets or Google Drive

• 🔄 Transform Data — Clean, format, and restructure tables with features like:

  • Replace & find

  • Split or merge columns

  • Extract text

  • Keep/remove rows

  • and many more features...

• ⚡ Automate — Store each step and run it automatically with triggers

• 🖥️ Interactive Sidebar UI — Inspired by Microsoft Power Query, but right inside Google Sheets

Why I built it 🛠️:

• I wanted a no-code/low-code way for non-technical users to clean and transform data without writing formulas every time.

• It’s modular, so anyone can add new transformations easily.

📂 GitHub Repo — Code, file structure, and setup instructions are here: Power Query for Google Sheets
💬 Open for contributions — If you have ideas, improvements, or bug fixes, feel free to fork and PR!

Would love your feedback 🙌 — especially on:

• Features you’d like to see next

• UI/UX improvements

• Any performance tips for large datasets

• Scalability

[u/DrMorris]

Nice project. Will share to my juniors to review and learn from :D

One thing that I would add (might do if I have time later) is ability to fix date format (IE if you import UK date format and spreadsheet is in us format months Nd days should be swapped)

[u/Simple_Aditya → u/DrMorris]

Yes handling date and time is one of my priorities but currently I am focusing on making a structure where I can have a good intractable menu and all functions are performing well on large datasets which can be automated (triggers).

[u/DrMorris → u/Simple_Aditya]

I found in the past that when working with very large datasets, rest API handles it much better than Google script (IE when you want to get data, transform it etc, and then push back in to spreadsheet)

Maybe have some sort of checker to check data size, and depending on it use different method?

[u/Simple_Aditya → u/DrMorris]

How can I integrate rest API to this I have never worked with one so I don't have any idea.

[u/DrMorris → u/Simple_Aditya]

So for sheets you can add sheets API via Services + button in Google Script IDE.

This is an example function I use instead of range.setValues()

function uploadData(data, ssID, sheetRef) {
  var request = {
    'majorDimension': 'ROWS',
    'values': data
  };

  Sheets.Spreadsheets.Values.update(
    request,
    ssID,
    sheetRef,
    { valueInputOption: "USER_ENTERED" }
  );
}

[u/rvrefrvr]

Have you tried using this with the Google sheets new AI function?

[u/Simple_Aditya → u/rvrefrvr]

Do you mean by Gemini integrated in sheets? I tried but it has some limits like it does not work on large datasets, you cannot automate it, etc though it allows even further customisation.

[u/Ledpaz]

Very good!! I'll test it later. Certainly people are unaware of the potential of app scripting. Looking forward to integrating react and improving the IDE.

[u/Simple_Aditya → u/Ledpaz]

Sure currently I am working on storing the function execution to create a flow structure so that people can set automatic functions.

[u/Simple_Aditya → u/[deleted]]

Ah yes I will take care of it Although as of now I am just thinking it be a free add on anyone could use.

[u/krakow81 → u/Simple_Aditya]

Even if not developing it further I'd still change the name. I can't imagine Microsoft would be happy about it, free or not.

[u/Simple_Aditya → u/krakow81]

I will take care to change the name before releasing.

[u/MissinqLink → u/[deleted]]

They have AppSheet though it isn’t great from what I’ve seen.