设置Google表格列元数据-未设置表格ID-观看电子表格列是否被移动 [英] Set Google Sheets Column MetaData - Sheet ID is not being Set - Watch a spreadsheet column for being moved

问题描述

我正在使用高级驱动器服务"来设置表格"列的元数据.正在设置列ID，我可以检索它，但是 sheetId 属性未设置.

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)
 
}

验证响应:

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

"spreadsheetId":"ID Removed"

}

问题出在 dimensionRange

这是它的指定方式:

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
}

这就是回来了:

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

没有 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)
  
}

它返回:

{"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"}}}]}

仍然没有 sheetId

我不知道我做错了什么，还是Advanced Sheets Service是否有问题.

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

Sheets Advanced API已打开，并且已在我的云控制台中打开，并且版本为版本4.

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

推荐答案

我可以确认，当 sheetId === 0 时，它不包含在尺寸范围内，
但是在工作时在任何其他图纸上(即其中不是0 的图纸)，sheetId都包含在尺寸范围内.也许是GAS方面的虚假错误?

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?

我正在使用PHP客户端库与Google Sheets API进行交互，并且现在添加了以下内容来解决该问题.

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 ;

请Google很好地解决此问题，以免绊倒其他用户.

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

这篇关于设置Google表格列元数据-未设置表格ID-观看电子表格列是否被移动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！