Created
August 13, 2021 10:48
-
-
Save rapartipoorna/8c78678c9cd2232f65a8016f3cbae907 to your computer and use it in GitHub Desktop.
[ Apps script ] This will write different color background rows data into separate sheets from spreadsheet. script should be bounded to spreadsheet.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| function copyGreenRows() { | |
| var date = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy") | |
| var filename = 'your file name' | |
| var header = []; | |
| var files = DriveApp.searchFiles("fullText contains "+filename+"and mimeType contains 'spreadsheet'") | |
| while(files.hasNext()){ | |
| var nxt = files.next() | |
| var id = nxt.getId() | |
| var ss = SpreadsheetApp.openById(id) | |
| var sheets = ss.getSheets() | |
| var greenData = [], orangeData = [] , blueData = [] | |
| sheets.forEach(function(sheet){ | |
| header = [] | |
| var colors = sheet.getDataRange().getBackgrounds() | |
| var data = sheet.getDataRange().getValues() | |
| var values = sheet.getRange(1,1,1,data[0].length).getValues()[0] | |
| values.forEach(function(item){ | |
| header.push(item) | |
| }) | |
| for(var c in colors){ | |
| var rowcolors = colors[c].toString(); | |
| if(rowcolors.indexOf('#00ff00')!=-1){ | |
| greenData.push(data[c]) | |
| } | |
| if(rowcolors.indexOf('#ff9900')!=-1){ | |
| orangeData.push(data[c]) | |
| } | |
| if(rowcolors.indexOf('#0000ff')!=-1){ | |
| blueData.push(data[c]) | |
| } | |
| } | |
| }) | |
| if(greenData.length!=0){ | |
| ss.insertSheet('Green').setTabColor('#00ff00') | |
| var sheet = ss.getSheetByName('Green').appendRow(header) | |
| var range = sheet.getRange(1,1,1,header.length) | |
| range.setBackground('yellow') | |
| range.setFontWeight('bold') | |
| var lastRow = ss.getSheetByName('Green').getLastRow()+1 | |
| ss.getSheetByName('Green').getRange(lastRow,1,greenData.length,greenData[0].length).setValues(greenData) | |
| } | |
| if(orangeData.length!=0){ | |
| ss.insertSheet('Orange').setTabColor('#ff9900') | |
| var sheet = ss.getSheetByName('Orange').appendRow(header) | |
| var range = sheet.getRange(1,1,1,header.length) | |
| range.setBackground('yellow') | |
| range.setFontWeight('bold') | |
| var lastRow = ss.getSheetByName('Orange').getLastRow()+1 | |
| ss.getSheetByName('Orange').getRange(lastRow,1,orangeData.length,orangeData[0].length).setValues(orangeData) | |
| } | |
| if(blueData.length!=0){ | |
| var sheet = ss.insertSheet('Blue') | |
| sheet.setTabColor('#0000ff') | |
| sheet.appendRow(header) | |
| var range = sheet.getRange(1,1,1,header.length) | |
| range.setBackground('yellow') | |
| range.setFontWeight('bold') | |
| var lastRow = ss.getSheetByName('Blue').getLastRow()+1 | |
| ss.getSheetByName('Blue').getRange(lastRow,1,blueData.length,blueData[0].length).setValues(blueData) | |
| } | |
| } } | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment