Google表格:获取ID的有效代码，但结果应与文件名行对齐 [英] Google sheets: Working code for get IDs but result should be align on the filename row

问题描述

ID应该像左侧的示例一样对齐.但是正如您在右侧看到的那样，在第11行之前一切正常.有人可以帮我解决这个问题吗?

The IDs should be aligned like the sample on the left side. But as you can see on the right side, everything is working fine until row 11. Can someone help me with this one?

这是我正在使用的代码.我是从此处获得的.

Here's the code that I'm using. I got it from here.

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  
  var SSID =  ss.getId();
  var spreadsheetFile =  DriveApp.getFileById(SSID); //get file by id
  //var folderId = spreadsheetFile.getParents().next().getId();
  var folderId = "1vn5n00iCpKUWe_JsTFAesQsSmBsXu36i";
  
  const sh = ss.getSheetByName('Sheet4'); // change that to the name of your sheet
  const filenames = sh.getRange('B3:B').getValues().flat().filter(r=>r!='');
  const Folder = DriveApp.getFolderById(folderId);

  // 1. Retrieve the file list of all files in `folderId`.
  const getFileList = (f, folders = [], fileList = {}) => {
  const fs = f.getFiles();
  while (fs.hasNext()) {
    const file = fs.next()
    fileList[file.getName()] = file.getId();
  }
  const fols = f.getFolders();
  const folderObjs = [];
  while (fols.hasNext()) folderObjs.push(fols.next());
  if (folderObjs.length > 0) {
    folders.push(folderObjs);
    folderObjs.forEach(fol => getFileList(fol, folders, fileList));
  }
  return fileList;
};
const fileList = getFileList(Folder);

// 2. Create an array for putting values to Spreadsheet.
const IDs = filenames.map(fn => [fileList[fn] || ""]);
 
  sh.getRange(3,3,IDs.length,1).setValues(IDs);
}

推荐答案

我认为您当前问题的原因可能是由于 const filenames = sh.getRange('B3:B').getValues().flat().filter(r => r！=''); .那么如何如下修改呢?

I think that the reason of your current issue might be due to by const filenames = sh.getRange('B3:B').getValues().flat().filter(r=>r!='');. So how about modifying this as follows?

const filenames = sh.getRange('B3:B').getValues().flat().filter(r=>r!='');

收件人:

const filenames = sh.getRange('B3:B' + sh.getLastRow()).getValues();

这篇关于Google表格:获取ID的有效代码，但结果应与文件名行对齐的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！