Read GoogleSheet in VanillaTS project

[pierrenavarra (LessRevitMoreCode)]

hi
Sorry in advance if this post has nothing to do here but I confess I’m a bit lost (I’ve created a new post on the codesandBox community as well).
I’m using a Vanilla Typescript template to develop an application using speckle’s APIViewer. So I have an index.html file, a main index.ts file
I’ve set everything in my google settings to use the GoogleSheet API.
I’ve created a sheet and now I’m trying to read the data in my sheet…
I’m looking for examples…I confess I’m lost…could someone please give me some hints?

[pierrenavarra (LessRevitMoreCode)]

I finaly use : const response = await fetch(url);

[jonathon]

Hey @pierrenavarra , kind of out of the normal scope for us, so I hope you don’t mind I’ve moved your question to the general discussion.

I have done a fair deal of reading Google Sheets from scripts in my time (a killer feature of Sheets tbh)

I’ll also assume that

@pierrenavarra

I finaly use : const response = await fetch(url);

means you sorted your own problem?

If not, I can see what I can do to help.

[pierrenavarra (LessRevitMoreCode)]

Hi @jonathon

Thank you for taking the time to reply.
Since my message, I’ve been able to read data in a GoogleSheet, but when I write, I get a 401 error and I can’t explain it…
Here’s my js code and screenshots of my OAuth setup at Google Cloud

// index.js

// Configuration de l'API MapTiler (optionnel si besoin dans d'autres parties)
maptilerClient.config.apiKey = "0IOxISLhuYyV9WWBRWYR";

// Fonction pour récupérer les données de Google Sheets
window.fetchData = async function() {
  const apiKey = "myKey";
  const spreadsheetId = "1CpczW9Y4ggB1HnUWBn88vG3Gnt-tFiPnLXEZl5s1UXU";
  const range = "COORDINATES!A2:E6";
  const url = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${range}?key=${apiKey}`;

  try {
    const response = await fetch(url);
    if (!response.ok) {
      throw new Error(`HTTP error! Status: ${response.status}`);
    }

    const jsonData = await response.json();
    const googleSheetData = [];

    jsonData.values.forEach((row) => {
      const elementId = parseInt(row[0]);
      const latitude = parseFloat(row[1]);
      const longitude = parseFloat(row[2]);
      const name = row[3];
      const mapUrl = row[4];

      googleSheetData.push({ elementId, latitude, longitude, name, mapUrl });
    });

    // Récupérer l'elementId saisi par l'utilisateur
    const elementIdInput = parseInt(document.getElementById('elementIdInput').value);

    // Filtrer les données par elementId
    const filteredData = googleSheetData.filter(item => item.elementId === elementIdInput);

    // Afficher les données filtrées dans le DOM
    displayFetchedData(filteredData);

    // Remplir les champs latitude et longitude si les données sont trouvées
    if (filteredData.length > 0) {
      document.getElementById('latitudeInput').value = filteredData[0].latitude;
      document.getElementById('longitudeInput').value = filteredData[0].longitude;
    }

  } catch (error) {
    console.error("Error fetching data:", error);
    document.getElementById('fetchedData').innerHTML = "<p>Error fetching data from Google Sheets. Please try again later.</p>";
  }
}

// Fonction pour afficher les données récupérées dans le DOM
function displayFetchedData(data) {
  const fetchedDataElement = document.getElementById('fetchedData');
  fetchedDataElement.innerHTML = '';

  if (data.length === 0) {
    fetchedDataElement.innerHTML = "<p>No data found for the given Element ID.</p>";
    return;
  }

  data.forEach((item) => {
    const dataItem = `
      <div class="data-item">
        <p>Element ID: ${item.elementId}</p>
        <p>Name: ${item.name}</p>
        <p>Latitude: ${item.latitude}</p>
        <p>Longitude: ${item.longitude}</p>
        <p>Map URL: <a href="${item.mapUrl}" target="_blank">${item.mapUrl}</a></p>
      </div>
    `;
    fetchedDataElement.innerHTML += dataItem;
  });
}

// Fonction pour initialiser l'authentification OAuth
async function authenticate() {
  const CLIENT_ID = myClientID';
  const REDIRECT_URI = 'http://127.0.0.1:5501'; // Utilisez l'URI de redirection exact
  const SCOPES = 'https://www.googleapis.com/auth/spreadsheets';

  const tokenClient = google.accounts.oauth2.initTokenClient({
    client_id: CLIENT_ID,
    scope: SCOPES,
    redirect_uri: REDIRECT_URI,
    callback: (response) => {
      if (response.error !== undefined) {
        throw response;
      }
      console.log('Access token acquired:', response.access_token);
      localStorage.setItem('oauth_token', response.access_token);
    },
  });

  tokenClient.requestAccessToken({ prompt: 'consent' });
}

// Fonction pour mettre à jour les coordonnées dans Google Sheets
window.updateCoordinates = async function() {
  
  /*
  const CLIENT_ID = 'MyClientID';
  const CLIENT_SECRET = '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';
  const REDIRECT_URI = 'http://localhost:5501/index.html'; // Remplacez par votre URI de redirection autorisé
  const SCOPES = 'https://www.googleapis.com/auth/spreadsheets';
  const REFRESH_TOKEN = 'votre-refresh-token'; // Remplacez par votre refresh token
  */

  const accessToken = localStorage.getItem('oauth_token');
  if (!accessToken) {
    await authenticate();
    return;
  }

  const apiKey = "AIzaSyDf8su5VGkJyPrblaoNpXkYGIgPkmB5eQw";
  const spreadsheetId = "1CpczW9Y4ggB1HnUWBn88vG3Gnt-tFiPnLXEZl5s1UXU";
  const elementIdInput = parseInt(document.getElementById('elementIdInput').value);
  const latitudeInput = parseFloat(document.getElementById('latitudeInput').value);
  const longitudeInput = parseFloat(document.getElementById('longitudeInput').value);

  if (isNaN(elementIdInput) || isNaN(latitudeInput) || isNaN(longitudeInput)) {
    document.getElementById('fetchedData').innerHTML = "<p>Invalid input. Please provide valid Element ID, latitude, and longitude.</p>";
    return;
  }

  const range = "COORDINATES"; // Nom de la feuille où les données doivent être mises à jour

  try {
    // Récupérer les données actuelles de la feuille
    const fetchUrl = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${range}?key=${apiKey}`;
    const fetchResponse = await fetch(fetchUrl);
    if (!fetchResponse.ok) {
      throw new Error(`HTTP error! Status: ${fetchResponse.status}`);
    }
    const sheetData = await fetchResponse.json();

    // Trouver la ligne correspondant à l'elementId
    const rowIndex = sheetData.values.findIndex(row => parseInt(row[0]) === elementIdInput);
    if (rowIndex === -1) {
      document.getElementById('fetchedData').innerHTML = "<p>No matching Element ID found in the Google Sheet.</p>";
      return;
    }

    // Mettre à jour les valeurs dans la ligne trouvée
    sheetData.values[rowIndex][1] = latitudeInput.toString();
    sheetData.values[rowIndex][2] = longitudeInput.toString();

    // Envoyer les données mises à jour vers Google Sheets
    const updateUrl = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${range}!A${rowIndex + 2}:E${rowIndex + 2}?valueInputOption=USER_ENTERED&key=${apiKey}`;
    const updateBody = {
      range: `${range}!A${rowIndex + 2}:E${rowIndex + 2}`,
      majorDimension: "ROWS",
      values: [sheetData.values[rowIndex]]
    };

    const updateResponse = await fetch(updateUrl, {
      method: 'PUT',
      headers: {
        'Content-Type': 'application/json'
      },
      body: JSON.stringify(updateBody)
    });

    if (!updateResponse.ok) {
      throw new Error(`HTTP error! Status: ${updateResponse.status}`);
    }

    document.getElementById('fetchedData').innerHTML = "<p>Coordinates updated successfully in Google Sheets.</p>";

  } catch (error) {
    console.error("Error updating coordinates:", error);
    document.getElementById('fetchedData').innerHTML = "<p>Error updating coordinates in Google Sheets. Please try again later.</p>";
  }
}

[jonathon]

From memory, you must pass the authentication as a header on the PUT event, even with an authenticated client. I have found that using the publish client libraries (there is one for JS if not TS) under the gapi namespace is quite beneficial here.

const updateResponse = await fetch(updateUrl, {
      method: 'PUT',
      headers: {
        'Content-Type': 'application/json',
        'Authorization': `Bearer ${accessToken}` // Include the access token here
      },
      body: JSON.stringify(updateBody)
    });

Added Authorization: 'Bearer ${accessToken}' in the headers for the PUT request. You can retrieve this from localStorage

Check if the access token is properly obtained and stored.

[pierrenavarra (LessRevitMoreCode)]

Hi @jonathon
thank you for your reply
I’ll keep this and keep you posted