How to open multiple sheets inside a google sheet using openByUrl() method in Apps Script

Viewed 18

Currently, I'm using the code (written in Apps Script) given below to sync data from multiple google sheets to the database with the help of URLs. When the "sheetLinks" is given URL of sheets within a single google sheet file, it doesn't read the gid of the URL which is the only thing that differs between multiple sheets within a google sheet file thus making the for loop to run twice(as it is considering only the base URL which is same in both).

const sheetLinks = [
 "https://docs.google.com/spreadsheets/d/same/edit#gid=0",
"https://docs.google.com/spreadsheets/d/same/edit#gid=654836738"
];
 let baseUrl = "https://something/api";

function syncData() {
  for (filenum = 0; filenum < sheetLinks.length; filenum++) {
let spreadsheet = SpreadsheetApp.openByUrl(sheetLinks[filenum]);

let values = spreadsheet.getDataRange().getValues();

 for (let y = 1; y < values.length; y++) 
{
  let data = {};

  data.name = values[y][11].toString();
  data.platform = values[y][5].toString();
  data.platform_link = values[y][6].toString();

  let dbId = values[y][0];
  let responseData = {};
  if (dbId) {
    responseData = updateSession(dbId, data);
    console.log("Session updated! session ID: " + responseData.id);
  } else {
    responseData = createSession(data);
    // write the id to the dbId cell
    let actualRowNumber = y + 1; // y + 1 because we start the loop from y 1
    spreadsheet.getRange("A" + actualRowNumber).setValue(responseData.id);
    console.log("Session created! New session ID: " + responseData.id);
  }
  y++;
}
   }

   function apiRequest(url, method, data) {
let options = {
  method,
  contentType: "application/json",
  payload: JSON.stringify(data),
  muteHttpExceptions: true,
};
return UrlFetchApp.fetch(url, options);
    }

     function createSession(sessionData) {
let url = baseUrl + "/session";

let response = apiRequest(url, "post", sessionData);
console.log(response.getContentText());
let responseData = JSON.parse(response.getContentText());
return responseData;
  }

   function updateSession(sessionId, sessionData) {
let url = baseUrl + "/session/" + sessionId;

let response = apiRequest(url, "patch", sessionData);
let responseData = JSON.parse(response.getContentText());
return responseData;
 }
 }
0 Answers
Related