0

UPDATE: See below

I think the best way to pose my question is to describe the current procedure and the procedure I would like to implement. The current procedure:

1) My program creates NNN (some number) of CSV files (e.g. file1.csv, file2.csv, file3.csv, etc).  
2) I move those CSVs to my Google Drive.  
3) I select the first one and [right click] "Open With Google Sheets"
4) Once the data from the CSV file is imported into the new Google Spreadsheet file, I:
    A) Delete the first column (A)
    B) Highlight all the column headings (first row)
    C) Apply Bold, and Bottom Border
    D) Highlight all the columns (A-F)
    E) Double-click on a column-head separator to resize all columns to an optimal width
    F) View, Freeze, 1 Row
    G) Close the spreadsheet
5) Repeat, until all CSVs are imported and reformatted (separate single-sheet/tab spreadsheets, one for each CSV file)

The procedure I would like:

1) My program creates NNN (some number) of CSV files.  
2) I move those CSV files to my Google Drive.  
3) I select the first one and [right click] "Open With Google Sheets"
4) Once the data from the CSV file is imported into the new Google Spreadsheet file, I:
    A) Press [Alt]-r* (which performs the formatting steps A-G, detailed above)
5) Repeat, until all CSV files are imported and reformatted (separate single-sheet/tab spreadsheet files, one for each CSV file)

The trick (and the basis for my question) is, how do I run the macro designated [Alt]-r in each of the spreadsheets I've just created, when the code for that macro is stored in some other spreadsheet file (e.g. MyReformattingMacro)?

*The keystroke that fires the macro is unimportant. I used "[Alt]-r" as an example.

UPDATE: Based on @ale13's suggestion I think an alternative to my "Procedure I would like" could be:

1) My program creates NNN (some number) of CSV files.  
2) I move those CSV files to my Google Drive.  
3) I select the first one and [right click] "Open With Google Sheets"
4) Once the data from the CSV file is imported into the new Google Spreadsheet file, I:
5) Repeat, until all CSV files are imported and reformatted (separate single-sheet/tab spreadsheet files, one for each CSV file)
6) I open a spreadsheet that contains the names of all the spreadsheet I just created (I could create the names list at the same time I create the original CSV files), and copy and paste the list into "MyReformattingMacro" (mentioned above).  And then press [Alt]-r* (which performs the reformatting operations A-G, detailed above)
JohnCroc
  • 79
  • 1
  • 9
  • Unfortunately, I cannot understand about `a macro that is available to any sheet I open`. I apologize for this. Can I ask you about the detail of your goal? – Tanaike Sep 14 '20 at 23:36
  • 2
    I want to write a macro, which I want to use in any sheet I open. In other words, one option would be to write the macro I want one time, and then copy and paste it into the macro area for a particular sheet, then run it on that sheet. But that copy and paste action would take as much time as doing the formatting manually (as I'm doing now). Is it possible to open a sheet, then run a macro which is stored in different sheet, to operate on the current sheet? – JohnCroc Sep 14 '20 at 23:42
  • 1
    Thank you for replying. From your replying, I cannot understand about `any sheet` of `I want to use in any sheet I open`. I apologize for this. In this case, there are several sheets in one Google Spreadsheet? Or there are several Google Spreadsheet including a sheet? – Tanaike Sep 14 '20 at 23:45
  • Maybe this explanation will make more sense: I have 100 CSVs. I need to open each one with into a Google Sheet. After I open CSV #1, there are a number of formatting changes I need to do before giving the sheet to the user who needs it (delete a column, format column headings, resize columns, and freeze a row). It would be SO much more efficient, if I could write a macro to do that work, but only if I could write the macro once and store it in a separate sheet (call it "my macro"). Then, ... (1/2) – JohnCroc Sep 14 '20 at 23:51
  • (2/2)...I would like to run the macro stored in MyMacro on the sheet I created, from CSV #1 (Sheet1), so that the formatting operations performed by the macro would modify Sheet1. – JohnCroc Sep 14 '20 at 23:51
  • Multiple Google Sheets (one each, created from my CSVs), each with a single tab. – JohnCroc Sep 14 '20 at 23:53
  • Thank you for replying. At first, I would like to confirm my understanding. 1. You have 100 CSV data (is that 100 files?). 2. You want to put 100 CSV data to "Sheet1" in 100 new Google Spreadsheets, respectively. In this case, 100 Google Spreadsheet are required to be created as new one? 3. You want to set the cell format for each "Sheet1" of 100 Google Spreadsheet. Is my understanding correct? If my understanding is correct, I think that in this case, your goal can be achieved one script. How about this? – Tanaike Sep 14 '20 at 23:57
  • I think so (I'm not sure I understand some of your terms. I think you've helped me determine how best to pose my question above, so I'm going to revise it. Give me a minute and please re-read my original question. – JohnCroc Sep 15 '20 at 00:07
  • Thank you for replying. I would like to wait for your update. When your goal could be cleared more, it might be able to propose more concrete direction. – Tanaike Sep 15 '20 at 00:10
  • I hope my revised question, above, is more clear...and Thank you for helping me see what was unclear in my original question! – JohnCroc Sep 15 '20 at 00:26
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/221487/discussion-between-johncroc-and-tanaike). – JohnCroc Sep 15 '20 at 00:31
  • 2
    You can create a script at https://script.google.com . Once the csv reaches drive> you can programmatically import as spreadsheet>modify each spreadsheet as necessary from the same macro. Your reply to ale13 is correct( though ale13's answer itself is not correct, which means "tab" and not files). Do note that "sheet" means tab in a "spreadsheet" (a workbook or a file) – TheMaster Sep 17 '20 at 05:41
  • Have you thought about writing a regular script (not a macro) and publish [an add-on](https://developers.google.com/gsuite/add-ons/editors/sheets), so that you can execute these actions for every new spreadsheet via clicking a button from a custom menu? Do you think that could be appropriate for you situation? – Iamblichus Sep 17 '20 at 09:36
  • @TheMaster Could you please expand on `Once the csv reaches drive> you can programmatically import as spreadsheet>modify each spreadsheet as necessary from the same macro`? – Iamblichus Sep 17 '20 at 09:36
  • @Iamblichus Could you clarify which part of the said statement is confusing to you? If you ask a specific question, I'd be happy to explain what I meant. I'll add a answer just in case – TheMaster Sep 17 '20 at 10:42
  • 2
    @TheMaster No need for that, you clarified that in your answer. Thank you. – Iamblichus Sep 18 '20 at 09:47

1 Answers1

2

The flow would be:

  1. You move local CSV files to my Google Drive to a specific Google folder (say "pendingCSVs")

  2. Create a new script by clicking here

  3. In this script, get all files in a specific folder.

  4. Convert all csvs in pendingCSVs folder to Google sheets. This can be done with either Utilities.parseCsv or Drive api See sample here and here and put the converted spreadsheets to another folder say, "pendingSpreadsheets".

  5. Then the script loops through each file in pendingSpreadsheets, get it's id (file.getId()) and opens the file using (SpreadsheetApp.open(id)).

  6. Once the file is opened as Spreadsheet, do all the formatting options needed like (setting bold) and repeat(Step5) in a loop for all other files

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • This appears to be a good solution! Having a script that operates *on* spreadsheet files in a directory is the part of this I couldn't find any reference to. I'm going to start writing code and assuming I don't run into some unforeseen roadblock, I'll be back to mark yours as the "solution." Thank you! – JohnCroc Sep 17 '20 at 20:58