Replace Google Sheet using Apps Script while keeping the same File ID

0
votes

I am exporting two csv files from an SQL server to a Google Drive folder using File Stream, the data of which I then need to pull into a Google Sheet (without setting share settings to public).

The process I have in mind is:

  1. Export CSVs to file stream (F1.csv & F2.csv) - WORKING
  2. Convert the CSV files to Google Sheets using App Script (F1.gheet & F2.gsheet) - WORKING
  3. Use =IMPORTRANGE in my master Google Sheet (Tool.gheet) to import F1.gsheet & F2.gheet into their own Sheets in that workbook

The issue I am running into, is that the two csv files will be exported and uploaded daily. I can convert them fine, but they will get new file IDs, which breaks step 3.

What I want to be able to do, is replace F1.gsheet & F2.gsheet with the contents of the new F1.csv & F2.csv, so that their file IDs stay intact and Tool.gsheet pulls the new data.

I have looked at How to automatically import data from uploaded CSV or XLS file into Google Sheets but it is extremely slow, I just need to be able to use something similar to the Google file revision feature, but with Google Apps Script.

Any help would be greatly appreaciated!

1
google-apps-scriptgoogle-sheets

1 Answers

0
votes

You cannot assign to a new sheet the ID of an old sheet, but you can work instead with indices

Sample:

function moveSheet() {
  var ss =  SpreadsheetApp.getActive();
  var newSheet = SpreadsheetApp.getActive().insertSheet();
  var desiredIndex = 1
  ss.setActiveSheet(newSheet);
  ss.moveActiveSheet(desiredIndex);
  ss.getSheets()[desiredIndex].getName();
  Logger.log(newSheet.getName()+ "is the same as "+  ss.getSheets()[desiredIndex].getName());
}