CODEWITHSUNDEEP
javascriptreactjsgoogle-sheets-api
sbaden
sbaden

Reputation: 565

How do I get list of sheets in a spreadsheet dynamically with google sheets api in React?

Is there a way to get a list of all sheets in the below google sheet? See ranges=${sheet}. The code below gets data from one sheet but I'd like to get a list of sheets so I can call them dynamically.

const API = `https://sheets.googleapis.com/v4/spreadsheets/${sheetID}/values:batchGet?ranges=${sheet}&majorDimension=ROWS&key=${key}`;

    useEffect(()=>{
        fetch(API).then(response => response.json()).then(data => {
            console.log(data)

            let rows = data.valueRanges[0].values;

          });
    }, []);

Upvotes: 2

Views: 1510

Answers (1)

Tanaike
Tanaike

Reputation: 201603

  • You want to retrieve the sheet names in the Spreadsheet using the API key.
    • You have already known the Spreadsheet ID.
  • The Spreadsheet is publicly shared.
  • You want to achieve this using fetch of Javascript.

If my understanding is correct, how about this answer?

Modification point:

  • In order to retrieve the sheet names in the Spreadsheet, the method of spreadsheets.get in Sheets API is used.

Script 1:

The sample script for retrieving the sheet names in the Spreadsheet is as follows.

const endpoint = `https://sheets.googleapis.com/v4/spreadsheets/${sheetID}?key=${key}`;
fetch(endpoint).then(response => response.json()).then(data => {
  const sheetNames = data.sheets.map(sheet => sheet.properties.title);
  console.log(sheetNames)
});

Script 2:

When your script is modified, how about the following modification? In this modified script, the values are retrieved from all sheets in the Spreadsheet.

const endpoint = `https://sheets.googleapis.com/v4/spreadsheets/${sheetID}?key=${key}`;
fetch(endpoint).then(response => response.json()).then(data => {
  data.sheets.forEach(s => {
    const sheetName = s.properties.title;
    const API = `https://sheets.googleapis.com/v4/spreadsheets/${sheetID}/values:batchGet?ranges=${sheetName}&majorDimension=ROWS&key=${key}`;
    fetch(API).then(response => response.json()).then(data => {
      console.log(data)

      let rows = data.valueRanges[0].values;
    });
  });
});

Script 3:

In this script, the values are retrieved from only the 1st sheet in the Spreadsheet.

fetch(endpoint).then(response => response.json()).then(data => {
  const sheetName = data.sheets[0].properties.title;
  const API = `https://sheets.googleapis.com/v4/spreadsheets/${sheetID}/values:batchGet?ranges=${sheetName}&majorDimension=ROWS&key=${key}`;
  fetch(API).then(response => response.json()).then(data => {
    console.log(data)

    let rows = data.valueRanges[0].values;
  });
});

Note:

  • If you want to retrieve the values from the specific sheet in the Spreadsheet, please modify the above script.

Reference:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 6

Related Questions