google sheets scritp : create a loop to open each sheet, select all and copy/paste only value withou-CodePudding

I have a big google sheet file with - 420 sheets So I have to give my colleague without formulas and if it is possible not to do it manually, it will be great for me.

I would like to have a script to open the sheet, select all ( ctrl a) , copy and paste without formulas ( ctrl shift v) close the sheet and next.

So I need to make a script with a loop to solve this problem and all the operations.

I have already found a script to have all the sheets in a list.

  function sheetnames() { 
  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i=0 ; i<sheets.length ; i  ) out.push( [ sheets[i].getName() ] )
  return out  
}

that is ok.

and the screenshot is the list of sheets in my file. It is in the sheet "for_script" in the file "Entrainements 2021 2022"

list of sheets

thank you so musch for you help. Jérôme challe

CodePudding user response：

Copy DisplayValues

function getJSON() {
  const ss = SpreadsheetApp.getActive();
  const output = {pA:[]};//you can loop through this to load data later;Every element of this array is a property in the form of a sheet name that contains a 2d array of displayValues
  if(ss.getName() == "Entrainements 2021 2022") {
    const lsh = ss.getSheetByName("for_script");
    const list = lsh.getRange(1,1,lsh.getLastRow()).getDisplayValues().flat();
    ss.getSheets().filter(s => ~list.indexOf(s.getName())).forEach(sh => {
      if(!output.hasOwnProperty(sh.getName())) {
        output[sh.getName()]=sh.getDataRange().getDisplayValues();
        output.pA.push(sh.getName());
      } 
    })
  }
  return JSON.stringify(output);//return as JSON
}

CodePudding user response：

Another way is to create csv files in one folder and share this folder

function makeCSVFiles() {
  const folderID = 'YOUR FOLDER ID'
  var folder = DriveApp.getFolderById(folderID); 
  SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(sh => {
    var csv = "";
    sh.getDataRange().getValues().forEach(d => {
      csv  = d.join(",")   "\n";
    })
    var fileId = DriveApp.createFile(sh.getName()   ".csv", csv).getId()
    DriveApp.getFileById(fileId).moveTo(folder)
  })
}