Read data from second google sheet

user_14027403 0
Viewed 66

Im reading data from google spreadsheets using this code

  let url = "https://docs.google.com/spreadsheets/d/1a9X6Xb1IqHCWpBN6x0_BuQlYQevumfcSA6WASaisW-g/edit#gid=1626313095";
  let request = new XMLHttpRequest();
  request.open("GET", url, false);
  request.send(null);

  let csvData = new Array();
  let jsonObject = request.responseText.split(/\r?\n|\r/);
  for (var i = 0; i < jsonObject.length; i++) {
    csvData.push(jsonObject[i].split(','));
  }

these are the sheet cities im reading data from: enter image description here

the problem is it always read data from the first sheet which is baffalo , how can I make it loop through and read from another sheet which in this case would be Cape Town

google-sheets javascript
1 Answers

Change the gid parameter in url, for example

Buffalo have gid = 0 https://docs.google.com/spreadsheets/d/1a9X6Xb1IqHCWpBN6x0_BuQlYQevumfcSA6WASaisW-g/gviz/tq?tqx=out:csv&tq&gid= 0

Housing Backlogs have gid = 1626313095 https://docs.google.com/spreadsheets/d/1a9X6Xb1IqHCWpBN6x0_BuQlYQevumfcSA6WASaisW-g/gviz/tq?tqx=out:csv&tq&gid= 1626313095

You can also change the output like tqx=out:html / tqx=out:json

For dynamic tab changing You should try

let baseUrl = 'https://docs.google.com/spreadsheets/d/1a9X6Xb1IqHCWpBN6x0_BuQlYQevumfcSA6WASaisW-g/gviz/tq?tqx=out:csv&tq&gid=';

let buffalo = baseUrl + '0';

or

let housingBacklogs = baseUrl.concat('1626313095');
user_88589270
Related