Advertisement
volond

Create Sheet as XLSX From TAB color red

Dec 3rd, 2020 (edited)
192
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. let FOLDER_URL = "";
  2.  
  3. function driveIt(url, i) {
  4.     const blobushka = getBlob(url);
  5.  
  6.     const file = DriveApp.createFile(blobushka).setName(
  7.         `${i}. файл от ${Utilities.formatDate(
  8.         new Date(),
  9.         Session.getScriptTimeZone(),
  10.         "YY-MM-dd"
  11.       )}`
  12.     );
  13.  
  14.     FOLDER_URL
  15.         ?
  16.         file.moveTo(DriveApp.getFolderById(getIdFromUrl(FOLDER_URL))) :
  17.         "";
  18.     return file.getUrl();
  19. }
  20.  
  21. function driveIts() {
  22.     const ss = SpreadsheetApp.getActive();
  23.     const sheets = ss.getSheets();
  24.     const arr = sheets
  25.         .filter((sh) => sh.getTabColor() == "#ff0000")
  26.         .map(
  27.             (sh) =>
  28.             "https://docs.google.com/spreadsheets/export?id=" +
  29.             ss.getId() +
  30.             "&gid=" +
  31.             sh.getSheetId() +
  32.             "&exportFormat=xlsx"
  33.         );
  34.     console.log(arr);
  35.     const msg = arr.map((url, i) => driveIt(url, i));
  36.  
  37.     Browser.msgBox(`А вот и ваш файл:\n ${msg.join("\n")}`);
  38. }
  39. function driveItsCSV(){
  40. const ss = SpreadsheetApp.getActive();
  41. const sheets=ss.getSheets()
  42. const isTabColorMeColor=(color,sh)=>(sh)=>sh.getTabColor()==color
  43. const isTabColorRed=isTabColorMeColor('#ff0000')
  44.  
  45. const getExportLinkExt=(ss,exten,sh)=>(exten,sh)=>(sh)=>"https://docs.google.com/spreadsheets/export?id=" + ss.getId() + "&gid=" + sh.getSheetId() + "&exportFormat="+exten
  46. const getExportLinkSheetSS=getExportLinkExt(ss)
  47. const getExportLinkSheetSSToXLSX=getExportLinkSheetSS('csv')
  48.  
  49. const redSheet=sheets
  50. .filter(isTabColorRed)
  51.  
  52. const msg=redSheet
  53. .map(getExportLinkSheetSSToXLSX)
  54. .map(driveIt)
  55. redSheet.forEach(sh=>sh.setTabColor('#000000'))
  56. if (msg.length ){
  57. Browser.msgBox(`А вот и ваш файл:\n ${msg.join('\n')}`);
  58. }
  59. }
  60. function getBlob(url) {
  61.     var token = ScriptApp.getOAuthToken();
  62.     var response = UrlFetchApp.fetch(url, {
  63.         headers: {
  64.             Authorization: "Bearer " + token,
  65.         },
  66.     });
  67.     return response.getBlob();
  68. }
  69.  
  70. function getIdFromUrl(url) {
  71.     return url.match(/[-\w]{25,}/);
  72. }
  73.  
  74. function onOpen(e) {
  75.     SpreadsheetApp.getUi()
  76.         .createMenu("С к р и п т ы")
  77.         .addItem("превратить Листы Книги в XLSX", "driveIts")
  78.         .addItem("превратить Листы Книги в CSV", "driveItsCSV")
  79.         .addToUi();
  80. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement