Set Google Sheets Column MetaData - Sheet ID is not being Set - Watch a spreadsheet column for being moved

1
votes

I'm using the Advanced Drive Service to set the Sheets column metadata. The column ID is being set, and I can retrieve it, but the sheetId property is not getting set.

function setShColumnMetadata() {
  var request,response,ss,sh;
  
  ss = SpreadsheetApp.getActiveSpreadsheet();
  sh = ss.getSheetByName('Employees');
    
  request = [ 
    {
      createDeveloperMetadata:{

        developerMetadata:{

          metadataKey:"employeeSh_DeptIdCol",
          metadataValue:JSON.stringify({
            writtenBy:Session.getEffectiveUser().getEmail(),
            createdAt:new Date().getTime()
          }),
          location:{
        //See https://developers.google.com/sheets/api/reference/rest/v4/DimensionRange
            dimensionRange: {
              "sheetId":sh.getSheetId(),
              dimension:"COLUMNS",
              startIndex:9,//column number + 1 is the real column being watched
              endIndex:10
//Even though the end index is one more than the start index its only one column
            }
          },
          visibility:"DOCUMENT"      
        }
      }
    }
  ];
  
  response = Sheets.Spreadsheets.batchUpdate({requests:request}, ss.getId());
  Logger.log('response: ' + response)
 
}

Verification Response:

{
"replies":[
  {"createDeveloperMetadata":
    {"developerMetadata":
      {"metadataKey":"employeeSh_DeptIdCol",
       "visibility":"DOCUMENT",
       "metadataValue":"{
         \"writtenBy\":\"[email protected]\",
         \"createdAt\":1530996621771
        }",
      
      "metadataId":1988973086,
      "location":{
        "dimensionRange":{
          "startIndex":9,
          "endIndex":10,
          "dimension":"COLUMNS"
        },
        "locationType":"COLUMN"
      }
    }
  }
}],

"spreadsheetId":"ID Removed"

}

The problem is in the dimensionRange

This is how it's designated:

dimensionRange: {
  //See https://developers.google.com/sheets/api/reference/rest/v4/DimensionRange
  "sheetId":sh.getSheetId(),
  dimension:"COLUMNS",
  startIndex:9,  //column number
  endIndex:10
  //Even though the end index is one more than the
  //start index its only one column
}

This is what comes back:

"dimensionRange":{
  "startIndex":9,
  "endIndex":10,
  "dimension":"COLUMNS"
},

There is no sheetId

If I use the following code to get the saved metadata:

function getTheSettingsBack() {
  var metaDataClass,ssID,stateFound;
    
  ssID = SpreadsheetApp.getActiveSpreadsheet().getId();
  
  metaDataClass = Sheets.Spreadsheets.DeveloperMetadata;
    
  stateFound = metaDataClass.search({
    dataFilters:[{
      developerMetadataLookup: {
        metadataKey: 'employeeSh_DeptIdCol'
      }}]
  }, ssID);
  
  Logger.log('stateFound: ' + stateFound)
  
}

It returns:

{"matchedDeveloperMetadata":
[{"dataFilters":[{"developerMetadataLookup":
{"metadataKey":"employeeSh_DeptIdCol"}}],"developerMetadata":
{"metadataKey":"employeeSh_DeptIdCol","visibility":"DOCUMENT",
"metadataValue":"{\"writtenBy\":\"account name here\",\"createdAt\":1530997558830}",
"metadataId":459573349,
"location":{
  "dimensionRange":{"startIndex":9,"endIndex":10,"dimension":"COLUMNS"},
"locationType":"COLUMN"}}}]}

Still no sheetId

I don't know if I'm doing something wrong, or if there is something wrong with the Advanced Sheets Service.

The Sheets Advanced API is ON, and turned on in my cloud console, and the version is version 4.

Sheets API is set to Version 4

1
google-apps-scriptgoogle-sheetsmetadatagoogle-sheets-api
Although I'm not sure whether this is useful information for you, I would like to report this. In my environment, I confirmed that when the request body is sent using your setShColumnMetadata(), sheetId is included in the response. And also I confirmed that after setShColumnMetadata() was run, the values retrieved using getTheSettingsBack() have sheetId. So how about trying this for a new Spreadsheet? I thought that when a new Spreadsheet is created, the issue might be removed. If this was not useful for your situation, I'm sorry. – Tanaike
Thank you for testing the code. I did try a new spreadsheet, and I'm still getting the same result, with the sheetId missing from the object information. But, I just found out, that I don't really need that information. It is immaterial to me at this point. I set the MetaData for a column to watch, moved the column, and the startIndex and endIndex settings changed in the MetaData. That's what I really need. So, even though I'd like to know why this is happening in my paid account, I might not really care if I can keep track of the columns being moved. – Alan Wells
Thank you for your additional information. Can I propose an another test? As an another proposal, how about using "fields" to Sheets.Spreadsheets.batchUpdate()`` like response = Sheets.Spreadsheets.batchUpdate({requests:request}, ss.getId(), {fields:"replies/createDeveloperMetadata/developerMetadata/location/dimensionRange/sheetId"});? In my environment, only sheetId can be retrieved when setShColumnMetadata() including it is run. If this was still the same results, I'm sorry. – Tanaike
I tried the new code and it ran without any error, but I'm still getting the same result. – Alan Wells
If your issue was not still resolved, how about this? When I had created my application, I noticed to the reason of issue by chance. When your script is used to the sheet with gid=0, sheetId is not included in dimensionRange. When your script is used to the sheet with gid=######### which is except for the number of 0, sheetId is included in dimensionRange. I thought that this might be an internal issue of GAS side, because when the value is 0, the property is not added. Is this useful for resolving your issue? If your issue had already been resolved, I'm sorry. – Tanaike

1 Answers

1
votes

I can confirm that when sheetId === 0, it is not included in dimension range,
but when working on any other sheet (i.e. where it is not 0), the sheetId is included in the dimension range. Perhaps a falsey bug in GAS side?

I'm using the PHP client library to interact with Google Sheets API and I have added the following to work around it for now.

if($sheetId === null)
  $sheetId = 0 ;

Be good for Google to fix this so as not to trip up other users.