google sheet url is not following the expected strcture

Question

Yesterday I noticed that google api is formatting the sheet url different than before.

According to the Google Sheets API docs, the format of a google sheet url should be:

• https://docs.google.com/spreadsheets/d/aBC-123_xYz/edit#gid=sheetId

But I noticed yesterday that it has changed to:

• https://docs.google.com/spreadsheets/d/aBC-123_xYz/edit?gid=sheetId#gid=sheetId

This is a breaking change for our application that needs to extract the sheetId from the url. I could not find any docs related to this change or any releases updates.

-

Answer 1

The proper way to send feedback regarding the documentation is to click the “Send feedback” button from the upper right corner of this Google Developers article. I also sent feedback this way to have more traction on this.

As a workaround for the break in your code, you can use this JavaScript code to convert the new format to the old one:

function convertToOldFormat(rawURL) {
  
  // Checking if the URL contains "?gid" or "&gid", as this is the new format of the URL. If the URL does not contain either, it's already an old format URL so we can return it directly
  if (rawURL.lastIndexOf('?gid') != -1) {

    //If it is a new format and the URL does not have any other parameters this will return an old format by eliminating the "#gid=id" part and replacing the "?gid-id" part with "#gid=id"
    return rawURL.substring(0, rawURL.lastIndexOf('#gid')).replace("?","#"); 
  
  } else if (rawURL.lastIndexOf('&gid') != -1) {

    // If it is a new format and the URL does have any other parameters this will return an old format by eliminating the "#gid=id" part and replacing the "&gid-id" part with "#gid=id"
    return rawURL.substring(0, rawURL.lastIndexOf('#gid')).replace("&","#"); 

  } else {

    return rawURL; 

  }

}

Should you want to do the opposite (convert it to the new format), use this instead:

function convertToNewFormat(rawURL) {
 
 // As previously checking if the URL is in the new format, if it is we just return it as it is
  
if (rawURL.lastIndexOf('?gid') != -1 || rawURL.lastIndexOf('&gid') != -1) {
    return rawURL;
 
 // If this is the old format
  
} else {
    
    // Get the “#gid=id” and save it to add it to the URL
    let newGID=rawURL.substring(rawURL.lastIndexOf("#gid"));
    
    // If “#gid” is right next to edit (“edit#gid”) then the URL does not have parameters 
    if(rawURL.lastIndexOf("#gid")-4 == rawURL.lastIndexOf("edit")){
      
      // Replacing edit#gid with edit?gid and adding the saved #gid from before
      return rawURL.replace("#","?") + newGID 
    }

    // If “gid” is not next to edit then the URL has parameters
    else{
      
      // Instead of ? add &
      return rawURL.replace("#","&") + newGID 
    }
  }
}

You can use this function to easily test all of the possible conversions:

function test(){

  // Possible versions of URLS:
  // New:
  // https://docs.google.com/spreadsheets/d/ID/edit?resourcekey=Key&gid=id#gid=id -> with parameters
  // https://docs.google.com/spreadsheets/d/ID/edit?gid=id#gid=id -> without parameters
  // Old:
  // https://docs.google.com/spreadsheets/d/ID/edit?resourcekey=Key#gid=id -> with parameters
  // https://docs.google.com/spreadsheets/d/ID/edit#gid=id -> without parameters

console.log(convertToNewFormat("URL HERE"));

}

I tested the code with the examples that are commented there. This code can be used to change the input in your code to be the same as the old input.