1

I have an Excel file that I upload and automatically convert to a google sheet on a weekly basis. This file is always the same layout, just different dates, and I would like to have it automatically format the same way every time I upload it. I can easily create a macro, however that macro only works in the current sheet. I would like the macro to work on all the files I upload to this folder. Is this possible? I've tried publishing the script to a web app and an add-on but they do not work.

Thanks. Below is the script:

/** @OnlyCurrentDoc */

function ScheduleFormat() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
  spreadsheet.getActiveRangeList().setFontSize(11);
  spreadsheet.getActiveSheet().setFrozenColumns(0);
  spreadsheet.getRange('1:1').activate();
  spreadsheet.getActiveRangeList().setFontSize(12);
  sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
  spreadsheet.getActiveRangeList().setFontColor(null);
  spreadsheet.getRange('B:C').activate();
  spreadsheet.setCurrentCell(spreadsheet.getRange('C1'));
  spreadsheet.getActiveSheet().deleteColumns(spreadsheet.getActiveRange().getColumn(), spreadsheet.getActiveRange().getNumColumns());
  spreadsheet.getRange('C2').activate();
  spreadsheet.getActiveSheet().setColumnWidth(2, 89);
  spreadsheet.getActiveSheet().setColumnWidth(2, 103);
  spreadsheet.getRange('C2:I22').activate();
  spreadsheet.getActiveRangeList().setBorder(true, true, true, true, true, true, '#000000', SpreadsheetApp.BorderStyle.SOLID);
  spreadsheet.getRange('23:24').activate();
  spreadsheet.setCurrentCell(spreadsheet.getRange('A24'));
  spreadsheet.getActiveSheet().deleteRows(spreadsheet.getActiveRange().getRow(), spreadsheet.getActiveRange().getNumRows());
  spreadsheet.getRange('17:17').activate();
  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);
  spreadsheet.getActiveRange().offset(0, 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
  spreadsheet.getRange('10:10').activate();
  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);
  spreadsheet.getActiveRange().offset(0, 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
  spreadsheet.getRangeList(['10:10', '18:18']).activate()
  .clearFormat();
  spreadsheet.getActiveSheet().setRowHeight(10, 13);
  spreadsheet.getActiveSheet().setRowHeight(18, 13);
  spreadsheet.getRange('G6').activate();
};
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
AMEt
  • 11
  • 1
  • Welcome to SO! Did you try to keep the macro in your PERSONAL file at Excel? – David García Bodego Nov 30 '19 at 03:42
  • Thoughts.. 1) "every time I upload it": could be ticklish but either a time-driven trigger (once a day/week/etc) or a manual executed script could do the trick; 2) convert the xlsx file to Google sheets format: @Tanaike wrote a great script that addresses this - [Convert excel files to Google Spreadsheet and replace existing spreadsheet files automatically](https://stackoverflow.com/q/55453479/1330560) - much food for thought; 3) adapt your script to work via `openById(id)` [doc](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openbyidid). – Tedinoz Nov 30 '19 at 06:39
  • You can use DriveApp https://developers.google.com/apps-script/reference/drive/drive-app to iterate through all files in a given folder and select the files that you want to convert by date (e.g. set up a weekly firing time trigger and retrieve the files updated within the last week with getLastUpdated() https://developers.google.com/apps-script/reference/drive/file ). – ziganotschka Dec 02 '19 at 08:33

0 Answers0