Script to update google sheets using a CSV file

davidcantor
3StarLounger
Posts: 248
Joined: 05 Nov 2012, 19:40

Script to update google sheets using a CSV file

Post by davidcantor »

HI
I am trying to create a script that will update a google sheets document using a csv file.

the update should only insert the rows that have a later date than the date in the last row of the sheets document.

I do not know where to start with this so any help will be appreciated.

David

User avatar
HansV
Administrator
Posts: 79526
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Script to update google sheets using a CSV file

Post by HansV »

I'm afraid I don't know anything about Google Apps Scripts.
Best wishes,
Hans

User avatar
SpeakEasy
5StarLounger
Posts: 631
Joined: 27 Jun 2021, 10:46

Re: Script to update google sheets using a CSV file

Post by SpeakEasy »

Starting with some assumptions

1. You know your fileIDs
2 Date is in first column
3 CSV file does not have a header row

Then something like the following Google Apps script:

Code: Select all

function updateGoogleSheetWithCSV() {
  // Set your CSV file ID and Google Sheets ID
  const csvFileId = '1gvpPXvM8mnum5GN4SEU3Jg99EGfrRCxy'; // Replace with your CSV file ID
  const sheetId = '1L2HR4GWqO9Txy1omrAyDeDkzK9H45HIW8KUGmWZ4XYk'; // Replace with your Google Sheets ID

  // Open the CSV file and read its content
  const csvFile = DriveApp.getFileById(csvFileId);
  const csvData = Utilities.parseCsv(csvFile.getBlob().getDataAsString());

 // Logger.log(csvData)

  // Open the Google Sheets document
  const sheet = SpreadsheetApp.openById(sheetId).getActiveSheet();

  // Get the last row in the existing sheet
  const lastRow = sheet.getLastRow();
  const lastDate = lastRow > 0 ? new Date(sheet.getRange(lastRow, 1).getValue()) : null; // Assuming dates are in the first column

  // Iterate through the CSV data and check dates
  const rowsToInsert = [];
  for (let i = 0; i < csvData.length; i++) { // Skip header row
    const row = csvData[i];
    const rowDate = new Date(row[0]); // Assuming the date is in the first column of the CSV

    // Check if the row date is later than the last date in the sheet
    if (lastDate === null || rowDate > lastDate) {
      rowsToInsert.push(row);
    }
  }

  // Insert the new rows into the Google Sheet
  if (rowsToInsert.length > 0) {
    sheet.getRange(lastRow + 1, 1, rowsToInsert.length, rowsToInsert[0].length).setValues(rowsToInsert);
  } else {
    Logger.log("No new rows to insert.");
  }
}
Note I am not a google apps script expert by any means, and credit for the core of this lies with ChatGPT