Google Sheets Script for Fetching Data from Supabase

description.md

Google Sheets Script for Fetching Data from Supabase

This Google Sheets script fetches data from a Supabase database and writes the selected headers and data to the active sheet. The script first clears the sheet, writes the headers, then fetches the data from the Supabase API, and finally writes the data to the sheet. It is optimized and has anonymized variables for public use.

How to Use

1. Create a new Google Sheet or open an existing one.
2. Click on "Extensions" in the menu, then select "Apps Script." (If you don't see "Extensions," click on "Tools" and then "Script editor.")
3. In the Apps Script editor, replace the default Code.gs content with the content of supabase_to_google_sheets.gs.
4. Replace the SUPABASE_URL and SUPABASE_ANON_KEY variables with your actual Supabase URL and anon key.
5. Adjust the SELECTED_HEADERS arrays according to your needs.
6. Save the script by clicking on the floppy disk icon or pressing Ctrl+S (Cmd+S on Mac).
7. To run the script, go to the "Select function" dropdown in the toolbar, choose mainFunction, and click the "Run" button (the triangle icon).
8. Grant the necessary permissions when prompted.
9. The script will clear the active sheet and fetch data from the Supabase API, then write the selected headers and data to the sheet.

Remember to save your changes before running the script. You can customize the SELECTED_HEADERS arrays as needed, and you can modify the script's behavior if necessary.

supabaseToGoogleSheets.gs

const SUPABASE_URL = "https://your_supabase_url_here";
const SUPABASE_ANON_KEY = "your_supabase_anon_key_here";
const TABLE = "your_table_name_here"

const SELECTED_HEADERS = [
  // Add all the headers you want to select from the table here
];

const HEADER_ROW = 1;
const DATA_START_ROW = HEADER_ROW + 1;

function mainFunction() {
  clearEntireSpreadsheet();
  writeHeadersOnRow(HEADER_ROW);
  fetchDataAndWriteToSheet();
}

function fetchDataAndWriteToSheet() {
  // If you want to select all columns, you can remove ?select=${SELECTED_HEADERS.join(',')}
  const url = `${SUPABASE_URL}/rest/v1/${TABLE}?select=${SELECTED_HEADERS.join(',')}`;
  const options = {
    headers: {
      Apikey: SUPABASE_ANON_KEY,
      Authorization: `Bearer ${SUPABASE_ANON_KEY}`,
      'Content-Type': 'application/json'
    },
    method: 'get',
  }
  const response = UrlFetchApp.fetch(url, options);
  const jsonData = JSON.parse(response.getContentText());
  writeToSheet(jsonData);
}

function writeToSheet(data) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  const rows = data.map((row) => {
    return SELECTED_HEADERS.map((column) =>
      row.hasOwnProperty(column) ? row[column] : ''
    );
  });

  const dataRange = sheet.getRange(DATA_START_ROW, 1, rows.length, rows[0].length);
  dataRange.setValues(rows);
}

function clearEntireSpreadsheet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clear();
}

function writeHeadersOnRow(rowNumber) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(rowNumber, 1, 1, SELECTED_HEADERS.length).setValues([SELECTED_HEADERS]);
}