Update Google sheet Every 1hr-CodePudding

How can I make this script run and update the google sheet every 1hr. I want new data to append at the bottom not to overwrite the existing data and is there any possibility it will delete the duplicate data automatically on the basis of Column C, deleting old values and leaves new.

function youTubeSearchResults() {
  // 1. Retrieve values from column "A".
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const values = sheet.getRange("A2:A"   sheet.getLastRow()).getDisplayValues().filter(([a]) => a);

  // 2. Retrieve your current values.
  const modifyResults = values.flatMap(([keywords]) => {
    const searchResults = JSON.parse(UrlFetchApp.fetch(`https://yt.lemnoslife.com/noKey/search?part=snippet&q=${keywords}&maxResults=10&type=video&order=viewCount&videoDuration=short&publishedAfter=2022-09-27T00:00:00Z`).getContentText());
    
    const fSearchResults = searchResults.items.filter(function (sr) { return sr.id.kind === "youtube#video" });
    return fSearchResults.map(function (sr) { return [keywords, sr.id.videoId, `https://www.youtube.com/watch?v=${sr.id.videoId}`, sr.snippet.title, sr.snippet.publishedAt, sr.snippet.channelTitle, sr.snippet.channelId, `https://www.youtube.com/channel/${sr.snippet.channelId}`, sr.snippet.thumbnails.high.url] });
  });

  // 3. Retrieve viewCounts and subscriberCounts.
  const { videoIds, channelIds } = modifyResults.reduce((o, r) => {
    o.videoIds.push(r[1]);
    o.channelIds.push(r[6]);
    return o;
  }, { videoIds: [], channelIds: [] });
  const limit = 50;
  const { viewCounts, subscriberCounts } = [...Array(Math.ceil(videoIds.length / limit))].reduce((obj, _) => {
    const vIds = videoIds.splice(0, limit);
    const cIds = channelIds.splice(0, limit);
    const res1 = YouTube.Videos.list(["statistics"], { id: vIds, maxResults: limit }).items.map(({ statistics: { viewCount } }) => viewCount);
    const obj2 = YouTube.Channels.list(["statistics"], { id: cIds, maxResults: limit }).items.reduce((o, { id, statistics: { subscriberCount } }) => (o[id] = subscriberCount, o), {});
    const res2 = cIds.map(e => obj2[e] || null);
    obj.viewCounts = [...obj.viewCounts, ...res1];
    obj.subscriberCounts = [...obj.subscriberCounts, ...res2];
    return obj;
  }, { viewCounts: [], subscriberCounts: [] });
  const ar = [viewCounts, subscriberCounts];
  const rr = ar[0].map((_, c) => ar.map(r => r[c]));

  // 4. Merge data.
  const res = modifyResults.map((r, i) => [...r, ...rr[i]]);

  // 5. Put values on Spreadsheet.
  sheet.getRange(2, 2, res.length, res[0].length).setValues(res);
    
}

CodePudding user response：

Hourly Trigger youTubeSearchResults

function hourlytrigger() {
  if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == "youTubeSearchResults").length==0) {
    ScriptApp.newTrigger("youTubeSearchResults").timeBased().everyHours(1).create();
  }
}

To append data:

sheet.getRange(sheet.getLastRow()   1, 2, res.length, res[0].length).setValues(res);